Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

Original

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

After

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
add comment

1 Answer 1

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

    Next
    'going 1 row backward
    i = n - 1

    Next
    '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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.