Need help with complicated merge

hokiebigtackle

New Member
Joined
Jan 7, 2014
Messages
3
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?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

jamtay317

Well-known Member
Joined
Mar 6, 2013
Messages
769
can you please provide a small sample (10 rows) of data, both what you have and what you would like it to be?
 

hokiebigtackle

New Member
Joined
Jan 7, 2014
Messages
3
This is a sample of what I have:

Jillsitup
Jillsquat
Jilljump
Bobrun
Bobhit
Bobfall
Bobjump
Dougsquat
Dougrun
Dougsitup
Dougjog
Dougfall
Shellysquat
Shellyjump
Shellyhit
Shellyfall

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


This is what I want:

Jillsitup,squat,jump
Bobrun,hit,fall,jump
Dougsquat,run,situp,jog,fall
Shellysquat,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

New Member
Joined
Jan 7, 2014
Messages
3
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

Board Regular
Joined
Sep 4, 2013
Messages
146

ADVERTISEMENT

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
ABCD
1Jillsitupsitup
2Jillsquatsitup,squat
3Jilljumpsitup,squat,jumpLast One
4Bobrunrun
5Bobhitrun,hit
6Bobfallrun,hit,fall
7Bobjumprun,hit,fall,jumpLast One
8Dougsquatsquat
9Dougrunsquat,run
10Dougsitupsquat,run,situp
11Dougjogsquat,run,situp,jog
12Dougfallsquat,run,situp,jog,fallLast One
13Shellysquatsquat
14Shellyjumpsquat,jump
15Shellyhitsquat,jump,hit
16Shellyfallsquat,jump,hit,fallLast One

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

Worksheet Formulas
CellFormula
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

Well-known Member
Joined
Mar 6, 2013
Messages
769
Excel 2012
ABCDEF
1JillsitupJillsitup, squat, jump,
2JillsquatBobsitup, squat, jump, run, hitfall, jump,
3JilljumpDougsitup, squat, jump, run, hitfall, jump, squat, run, situp, jog, fall,
4BobrunShellysitup, squat, jump, run, hitfall, jump, squat, run, situp, jog, fall, squat, jump, hit, fall,
5Bobhit
6Bobfall
7Bobjump
8Dougsquat
9Dougrun
10Dougsitup
11Dougjog
12Dougfall
13Shellysquat
14Shellyjump
15Shellyhit
16Shellyfall

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


Code:
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

Well-known Member
Joined
Mar 6, 2013
Messages
769
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,814
Messages
5,638,493
Members
417,029
Latest member
lingx86

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
Top