Need help with complicated merge

[hokiebigtackle]

Ok, here's the scenario:
I need a formula that will merge cells from column B and separate with a comma at each change of value in column A. This would be fairly easy except they are 1000's of rows and each and the number of rows for each different value in column A varies anywhere from 6 rows to 12 rows. If each value in column A had the same number of rows, I would create a formula like this =(B1&","&B2&","&B3&","&B4...) and just copy and paste the formula on the first line of each different value, but because the # of rows varies, each paste has to be adjusted by adding additional cells or deleting cells from the formula. I use Excel daily and feel like I'm pretty knowledgeable usually but this is way above my pay grade. :wink: Can anyone help me?

 

[jamtay317]

can you please provide a small sample (10 rows) of data, both what you have and what you would like it to be?

 

[hokiebigtackle]

This is a sample of what I have:

Jill	situp
Jill	squat
Jill	jump
Bob	run
Bob	hit
Bob	fall
Bob	jump
Doug	squat
Doug	run
Doug	situp
Doug	jog
Doug	fall
Shelly	squat
Shelly	jump
Shelly	hit
Shelly	fall

<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>
</tbody>

This is what I want:

Jill	situp,squat,jump
Bob	run,hit,fall,jump
Doug	squat,run,situp,jog,fall
Shelly	squat,jump,hit,fall

<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:4892;width:103pt" width="138"> </colgroup><tbody>
</tbody>

 

[hokiebigtackle]

I basically just did this by inserting a row in the middle and using the formula =(C1&","&C2&","&C3), then changed the formulas to values and deleted column C. But what I'm really dealing with involves 1000s of different values in column A and 10s of 1000s of rows with varying amounts of rows for each different value in column A.

 

[ExcelTactics]

I assume you want a hack-ish solution that doesn't involve writing VBA or other complications...

If you can sort the data by the column that has the matching values, this set of steps should work:
Excel 2010

	A	B	C	D
1	Jill	situp	situp
2	Jill	squat	situp,squat
3	Jill	jump	situp,squat,jump	Last One
4	Bob	run	run
5	Bob	hit	run,hit
6	Bob	fall	run,hit,fall
7	Bob	jump	run,hit,fall,jump	Last One
8	Doug	squat	squat
9	Doug	run	squat,run
10	Doug	situp	squat,run,situp
11	Doug	jog	squat,run,situp,jog
12	Doug	fall	squat,run,situp,jog,fall	Last One
13	Shelly	squat	squat
14	Shelly	jump	squat,jump
15	Shelly	hit	squat,jump,hit
16	Shelly	fall	squat,jump,hit,fall	Last One

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula C1 =B1 C2 =IF(A2=A1,C1&","&B2,B2) D1 =IF(A2<>A1,"Last One","") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

The formulas in C2 and D1 drag all the way down your data set. The result is the full list of concatenated values in the last row of each value in Column A.

You should be able to re-sort your data by Column D and take all the fully-concatenated values from the top. Let me know if you have any questions!

 

[jamtay317]

Excel 2012

	A	B	C	D	E	F
1	Jill	situp			Jill	situp, squat, jump,
2	Jill	squat			Bob	situp, squat, jump, run, hitfall, jump,
3	Jill	jump			Doug	situp, squat, jump, run, hitfall, jump, squat, run, situp, jog, fall,
4	Bob	run			Shelly	situp, squat, jump, run, hitfall, jump, squat, run, situp, jog, fall, squat, jump, hit, fall,
5	Bob	hit
6	Bob	fall
7	Bob	jump
8	Doug	squat
9	Doug	run
10	Doug	situp
11	Doug	jog
12	Doug	fall
13	Shelly	squat
14	Shelly	jump
15	Shelly	hit
16	Shelly	fall

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Sub con_by_name()
Dim as1 As Worksheet: Set as1 = ActiveSheet
    lr = as1.Cells(Rows.Count, 1).End(xlUp).Row
    For Each cell In Selection
        For x = 1 To lr
        num = Application.WorksheetFunction.CountIf(as1.Range("a1:a" & lr), cell.Value)
            If cell.Value = as1.Cells(x, 1) Then
                thing = thing & as1.Cells(x, 2) & IIf(counter = num, "", ", ")
                cell.Offset(0, 1).Value = thing
                counter = counter + 1
            End If
        Next x
    Next cell
End Sub

you will have to use VBA for this

 

[jamtay317]

Sub con_by_name()
Dim as1 As Worksheet: Set as1 = ActiveSheet
lr = as1.Cells(Rows.Count, 1).End(xlUp).Row
For Each cell In Selection
For x = 1 To lr
num = Application.WorksheetFunction.CountIf(as1.Range("a1:a" & lr), cell.Value)
If cell.Value = as1.Cells(x, 1) Then
thing = thing & as1.Cells(x, 2) & IIf(counter = num, "", ", ")
cell.Offset(0, 1).Value = thing
counter = counter + 1
End If
Next x
thing = Empty
Next cell
End Sub