Consolidating columns of text based

Roonie847

New Member
Joined
Sep 6, 2019
Messages
4
I have a data set that looks like this:

Col1Col2
asam
bsam
csam
dsam
esam
fsam
gsam
abill
bbill
dbill
gbill
hbill
ddave
edave
fdave

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

and I need to convert it to look like this:
NewCol1 NewCol2
a sam|bill
b sam|bill
c sam
d sam|bill|dave
e sam|dave
f sam|dave
h bill

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

Basically, I need to consolidate all of the values from Col2 into a single data element where the values in col1 are repeated. Been looking at a creative Vlookup combined with a concat but cannot figure it out. My dataset is about 6000 rows.
 
@Toadstool
Your results for f, g & h look wrong, if you are using the data supplied in the op.
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
It's unclear why your NewCol2 doesn't have an entry for g?

If I can use worker columns this does the job, but you'll need to copy N right if you've more than 6 Col2s for a Col1 and add ampersands to H.

ABCDEFGHIJKLMN
1Col1Col2NewCol1NewCol2Toad New Col1Toad New Col2Wrk1Wrk2Wrk3Wrk4Wrk5Wrk6
2asamasam|billasam|billsam|bill
3bsambsam|billbsam|billsam|bill
4csamcsamcsamsam
5dsamdsam|bill|davedsam|bill|davesam|bill|dave
6esamesam|daveesam|davesam|dave
7fsamfsam|davefsam|davesam|dave
8gsamhbillgsam|billsam|bill
9abillhbillbill
10bbill

<tbody>
</tbody>
Roonie847

Worksheet Formulas
CellFormula
H2=I2&J2&K2&L2&M2&N2
I2=IFERROR(INDEX($B$2:$B$16,AGGREGATE(15,6,ROW($A$2:$A$16)-1/($A$2:$A$16=$G2),COLUMNS($I$1:I$1))),"")
J2=IFERROR("|"&INDEX($B$2:$B$16,AGGREGATE(15,6,ROW($A$2:$A$16)-1/($A$2:$A$16=$G2),COLUMNS($I$1:J$1))),"")
K2=IFERROR("|"&INDEX($B$2:$B$16,AGGREGATE(15,6,ROW($A$2:$A$16)-1/($A$2:$A$16=$G2),COLUMNS($I$1:K$1))),"")
L2=IFERROR("|"&INDEX($B$2:$B$16,AGGREGATE(15,6,ROW($A$2:$A$16)-1/($A$2:$A$16=$G2),COLUMNS($I$1:L$1))),"")
M2=IFERROR("|"&INDEX($B$2:$B$16,AGGREGATE(15,6,ROW($A$2:$A$16)-1/($A$2:$A$16=$G2),COLUMNS($I$1:M$1))),"")
N2=IFERROR("|"&INDEX($B$2:$B$16,AGGREGATE(15,6,ROW($A$2:$A$16)-1/($A$2:$A$16=$G2),COLUMNS($I$1:N$1))),"")

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
G2{=IFERROR(INDEX($A$2:$A$16, MATCH(0, COUNTIF(G$1:$G1, $A$2:$A$16), 0)), "")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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