Announcement

Collapse
No announcement yet.

Concatenating Many Columns To One

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    Re: Concatenating Many Columns To One

    Attachment missing.

    Comment


    • #3
      Re: Concatenating Many Columns To One

      sorry here a sample of it
      Attached Files

      Comment


      • #4
        Re: Concatenating Many Columns To One

        Hi,

        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
        Kris

        ExcelFox

        Comment


        • #5
          Re: Concatenating Many Columns To One

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

          Comment


          • #6
            Re: Concatenating Many Columns To One

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

            ExcelFox

            Comment

            Working...
            X