concatenating multiple rows

A

avandever

Board Regular
Joined
Dec 7, 2010
Messages
83
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>
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
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.
 
Last edited:
Upvote 0
Desu Nota from Columbus said:
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.
Click to expand...


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
 
Upvote 0
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...

Code: 
[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]
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
I
  • Question Question
Sum column by priority (Y-axis) and date (Y-axis)
Replies
3
Views
241
kvsrinivasamurthy
K
C
  • Question Question
Picture won't paste in cell overlay
Replies
0
Views
250
Cerka
C
E
  • Solved
If Formula
Replies
1
Views
98
DanteAmor
DanteAmor
A
  • Solved
Trying to Separate multiple products within the same line automatically
Replies
7
Views
105
alleneure
A

Forum statistics

Threads
1,224,599
Messages
6,179,831
Members
452,947
Latest member
Gerry_F

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top