# Thread: Consolidating columns of text based Thanks: 0 Likes: 0

1. ## Re: Consolidating columns of text based

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

2. ## Re: Consolidating columns of text based

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
1 Col1 Col2 NewCol1 NewCol2 Toad New Col1 Toad New Col2 Wrk1 Wrk2 Wrk3 Wrk4 Wrk5 Wrk6
2 a sam a sam|bill a sam|bill sam |bill
3 b sam b sam|bill b sam|bill sam |bill
4 c sam c sam c sam sam
5 d sam d sam|bill|dave d sam|bill|dave sam |bill |dave
6 e sam e sam|dave e sam|dave sam |dave
7 f sam f sam|dave f sam|dave sam |dave
8 g sam h bill g sam|bill sam |bill
9 a bill h bill bill
10 b bill
Roonie847

Worksheet Formulas
Cell Formula
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))),"")

Array Formulas
Cell Formula
G2 {=IFERROR(INDEX(\$A\$2:\$A\$16, MATCH(0, COUNTIF(G\$1:\$G1, \$A\$2:\$A\$16), 0)), "")}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself