Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I wish to do the following but not sure how to do it...Can help on this?


Column1   Column2
TitleA       123
TitleA       345
TitleB       888
TitleC       567
TitleC       789


Column1   Column2
TitleA       123   345
TitleB       888
TitleC       567   789

Appreciate if anyone can help to advise :)

share|improve this question
you could probably build a vba macro to do so. We can help you correcting the code provided you made a first try – JMax Apr 3 '12 at 13:37
Are you trying to combine the numbers into 1 column or into multiple columns? ie, Are 345 & 789 in Column2 or Column3? – mischab1 Apr 3 '12 at 18:35
i see what i can do and research on the vba macro...thx alot...345 & 789 are for column2 and column3 ...more columns needed if duplcate values for column1 – Sandiago Apr 4 '12 at 8:08

You can start with this old macro I have and try to customize it to fit your needs, any guidance you need, just ask.

Sub ConcatenateAcrossColumns()

Dim data, numrows As Long, result, i As Long, n As Long

'turn off screen update
Application.ScreenUpdating = 0

'check if the data on the sheet start where the code expects it
If Range("a1") = "" Then Exit Sub

'define data range
With Range("a1", Cells(Rows.Count, "a").End(xlUp)).Resize(, 2)

    'sort data range by A1
    .Sort key1:=Range("a1"), Header:=xlNo
    'take data to array
    data = .Value
    'setting variable value equal to number of rows in array
    numrows = UBound(data)
    'creating result array
    ReDim result(1 To numrows, 1 To 1)

    'start loop from 1 row to the last row of array
    For i = 1 To numrows
    'taking first animal name to a variable
    temp = data(i, 1)
    'put number of the animal to result array
    result(i, 1) = result(i, 1) & data(i, 2)
    'loop until value of temp not equals current animal - ex.: cat <> dog
    For n = i + 1 To numrows
        'if cat = cat write it's corresponding value from the second column to result array
        If data(n, 1) = temp Then result(i, 1) = result(i, 1) & "," & data(n, 2) Else Exit For

    'going 1 row backward
    i = n - 1

    'output result array to the sheet
    .Offset(, 2).Resize(, 1) = result

End With

End Sub
share|improve this answer
Hi, thank you so much! will try and let you know :) – Sandiago Apr 10 '12 at 2:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .