Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Concatenating Many Columns To One

  1. #1
    Join Date
    5th August 2008
    Posts
    10

    Concatenating Many Columns To One

    Hi All

    my problem is that i have a huge number of rows and columns, what i want is to concatenate the data in all the columns to the data in the first one, but separately, this is really complicated, so let us assume that i have the following table

    -----A-----B-----C-----D-----E

    1 ---98---13----17----19----44
    2 ---94---18----20----23
    3 ---19---22----245---45----62

    so after concatenating the value in A to all the values in the other columns and put the result in a new column we get the following output

    -----A
    1---- 9813
    2---- 9817
    3---- 9819
    4---- 9844
    5---- 9418
    6---- 9420
    7---- 9423
    8---- 1922
    9---- 19245
    10--- 1945
    11--- 1962

    also we need to take in consideration the empty cells as they must be skipped during the process, also please find attached my Excel sheet that needs the modification

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    23rd April 2008
    Location
    Work in North Wales
    Posts
    262

    Re: Concatenating Many Columns To One

    Attachment missing.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    5th August 2008
    Posts
    10

    Re: Concatenating Many Columns To One

    sorry here a sample of it
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Concatenating Many Columns To One

    Hi,

    VB:
    Sub kTest() 
        Dim a, w(), i As Long, c As Long, n As Long 
        a = Range("a1").CurrentRegion 
        Redim w(1 To Rows.Count, 1 To 1) 
         
        For i = 1 To UBound(a, 1) 
            For c = 2 To UBound(a, 2) 
                If Not IsEmpty(a(i, c)) Then 
                    n = n + 1: w(n, 1) = a(i, 1) & a(i, c) 
                End If 
            Next 
        Next 
        With Range("a1") 
            .CurrentRegion.ClearContents 
            .Resize(n).Value = w 
        End With 
    End Sub 
    
    

  5. #5
    Join Date
    5th August 2008
    Posts
    10

    Re: Concatenating Many Columns To One

    Krishnakumar what can i say...u r the man many thanks mate i really appreciate

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Concatenating Many Columns To One

    Quote Originally Posted by raed_237
    Krishnakumar what can i say...u r the man many thanks mate i really appreciate
    You are welcome

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Concatenating 2 Cells Into 1
    By NickW in forum Excel General
    Replies: 3
    Last Post: April 27th, 2007, 17:02
  2. Concatenating Rows
    By Wattage in forum Excel General
    Replies: 1
    Last Post: January 12th, 2007, 04:56
  3. concatenating
    By leep20 in forum Excel General
    Replies: 3
    Last Post: July 15th, 2006, 04:30
  4. Concatenating a Variable name ?
    By Russgs in forum Excel General
    Replies: 13
    Last Post: September 11th, 2004, 19:47
  5. Concatenating fields
    By Cronina in forum Excel and/or Access Help
    Replies: 1
    Last Post: June 8th, 2004, 04:59

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno