Consolidating columns of text based

[Roonie847]

I have a data set that looks like this:

[TABLE="width: 128"]
<colgroup><col width="64" style="width:48pt" span="2"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Col1[/TD]
[TD="width: 64, bgcolor: transparent"]Col2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]a[/TD]
[TD="bgcolor: transparent"]sam[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]b[/TD]
[TD="bgcolor: transparent"]sam[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]c[/TD]
[TD="bgcolor: transparent"]sam[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]d[/TD]
[TD="bgcolor: transparent"]sam[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]e[/TD]
[TD="bgcolor: transparent"]sam[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]f[/TD]
[TD="bgcolor: transparent"]sam[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]g[/TD]
[TD="bgcolor: transparent"]sam[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]a[/TD]
[TD="bgcolor: transparent"]bill[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]b[/TD]
[TD="bgcolor: transparent"]bill[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]d[/TD]
[TD="bgcolor: transparent"]bill[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]g[/TD]
[TD="bgcolor: transparent"]bill[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]h[/TD]
[TD="bgcolor: transparent"]bill[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]d[/TD]
[TD="bgcolor: transparent"]dave[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]e[/TD]
[TD="bgcolor: transparent"]dave[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]f[/TD]
[TD="bgcolor: transparent"]dave[/TD]
[/TR]
</tbody>[/TABLE]

and I need to convert it to look like this:
[TABLE="width: 128"]
<colgroup><col width="64" style="width:48pt" span="2"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]NewCol1[/TD]
[TD="width: 64, bgcolor: transparent"] NewCol2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]a[/TD]
[TD="bgcolor: transparent"] sam|bill[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]b[/TD]
[TD="bgcolor: transparent"] sam|bill[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]c[/TD]
[TD="bgcolor: transparent"] sam[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]d[/TD]
[TD="bgcolor: transparent"] sam|bill|dave[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]e[/TD]
[TD="bgcolor: transparent"] sam|dave[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]f [/TD]
[TD="bgcolor: transparent"] sam|dave[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]h[/TD]
[TD="bgcolor: transparent"] bill[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Fluff]

@Toadstool
Your results for f, g & h look wrong, if you are using the data supplied in the op.

 

[Toadstool]

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.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
Col1	Col2	NewCol1	NewCol2	Toad New Col2	Wrk1	Wrk2	Wrk3	Wrk4	Wrk5	Wrk6
a	sam	a	sam|bill	sam|bill	sam	|bill
b	sam	b	sam|bill	sam|bill	sam	|bill
c	sam	c	sam	sam	sam
d	sam	d	sam|bill|dave	sam|bill|dave	sam	|bill	|dave
e	sam	e	sam|dave	sam|dave	sam	|dave
f	sam	f	sam|dave	sam|dave	sam	|dave
g	sam	h	bill	sam|bill	sam	|bill
a	bill	bill	bill
b	bill

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: center"]Toad New Col1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: center"]a[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: center"]b[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: center"]c[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: center"]d[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: center"]e[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: center"]f[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: center"]g[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]h[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

</tbody>

Roonie847​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=I2&J2&K2&L2&M2&N2[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=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))),"")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]=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))),"")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K2[/TH]
[TD="align: left"]=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))),"")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L2[/TH]
[TD="align: left"]=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))),"")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M2[/TH]
[TD="align: left"]=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))),"")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N2[/TH]
[TD="align: left"]=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))),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$2:$A$16, MATCH(0, COUNTIF(G$1:$G1, $A$2:$A$16), 0)), "")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]