concatenating multiple rows

[avandever]

i have two columns with values in column A that could repeat and all different values in column 2. what i want to do is concatenate all column 2 values for every like value in column a. thank you.

first table that i want to pull from

<table border="0" cellpadding="0" cellspacing="0" width="154"><col style="width: 58pt;" span="2" width="77"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 58pt;" height="20" width="77">a</td> <td style="width: 58pt;" align="right" width="77">1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">b</td> <td align="right">2</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">c</td> <td align="right">3</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">c</td> <td align="right">4</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">c</td> <td align="right">5</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">d</td> <td align="right">6</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">d</td> <td align="right">7</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">e</td> <td align="right">8</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">f</td> <td align="right">9</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">f</td> <td align="right">0
</td></tr></tbody></table>

what i want in 2nd table

<table border="0" cellpadding="0" cellspacing="0" width="154"><col style="width: 58pt;" span="2" width="77"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 58pt;" height="20" width="77">a</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td class="xl65" style="border-left: medium none; width: 58pt;" align="right" width="77">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">b</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">c</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">3, 4, 5</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">d</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">6, 7</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">e</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">8</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">f</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">9, 0</td> </tr> </tbody></table>

 

[Desu Nota from Columbus]

Take a look at this post ---> http://www.mrexcel.com/forum/showthread.php?t=550681

Domenic's macro is what you want (all you need to do is add column headings and change the names of Student ID and Parent ID in the macro to the newly created column headings.

 

[Desu Nota from Columbus]

You will actually need to edit the macro to reverse Column A and B's action.

Not as cut and dry as i thought, sorry.

I sent Domenic a message to see if he can edit his macro to help you out.

 

[Desu Nota from Columbus]

Take a look at this post ---> http://www.mrexcel.com/forum/showthread.php?t=550681

Domenic's macro is what you want (all you need to do is add column headings and change the names of Student ID and Parent ID in the macro to the newly created column headings.

If you still need this to be done, you could reverse your columns. Make the column with a, b, c, d, etc column B (and insert a Cell at B1, named Parent ID) and insert a cell in A1 named Student ID----run the macro from the link i gave you and then change Student ID and Parent ID to whatever you want.

Just a thought

 

[Domenic]

Assuming that Column A and Column B contain the data, and that Row 1 contains the column headers, the following macro will create your second table, starting at D2...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] Dict [color=darkblue]As[/color] [color=darkblue]Object[/color]
    [color=darkblue]Dim[/color] MyArray() [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] Cnt [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    
    [color=darkblue]Set[/color] Dict = CreateObject("Scripting.Dictionary")

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    [color=darkblue]For[/color] i = 2 [color=darkblue]To[/color] LastRow
        [color=darkblue]If[/color] [color=darkblue]Not[/color] Dict.Exists(Cells(i, "A").Value) [color=darkblue]Then[/color]
            Cnt = Cnt + 1
            [color=darkblue]ReDim[/color] [color=darkblue]Preserve[/color] MyArray(1 [color=darkblue]To[/color] 2, 1 To Cnt)
            MyArray(1, Cnt) = Cells(i, "A").Value
            MyArray(2, Cnt) = Cells(i, "B").Value
            Dict.Add Cells(i, "A").Value, Cnt
        [color=darkblue]Else[/color]
            MyArray(2, Dict.Item(Cells(i, "A").Value)) = MyArray(2, Dict.Item(Cells(i, "A").Value)) & ", " & Cells(i, "B").Value
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] i
    
    Range("D2").Resize(UBound(MyArray, 2), 2) = WorksheetFunction.Transpose(MyArray)
    
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
    MsgBox "Completed...", vbInformation
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[avandever]

im trying to learn vba as much as i can from reading threads on here, but its often really hard considering i dont know what each function really does. what book/website would you recommend to really learn the essentials? thanks.