concatinating similair columns

watto84

New Member
Joined
Dec 9, 2017
Messages
3
Hi,

I am using Excel 2003 on Windows 7.

I have data like the below table, where there is a name in each of Columns B,C,D,E,F and I have concatenated columns B,C,D,E,F into column A. I included this data into a pivot table and what I am getting is 2 rows of data in the pivot table. 1 for Michael,Sam,Jim,Frank,Bill and 1 row for Michael, Sam, Jim, Bill, Frank.

What I actually wanted is just 1 row returned in the pivot table as both rows actually all contain the same names, just in a slightly different order.

Is there a way I can concatenate/group these 5 Columns together (B,C,D,E,F) in a way where it doesn't matter what order the names are in but as long as the concatenated cell has the same 5 names then it should be treated as the same value?


Data:

Concatenate Rows B-FName1Name2Name3Name4Name5Score
Michael,Sam,Jim,Frank,BillMichaelSamJimFrankBill1
Michael,Sam,Jim,Frank,BillMichaelSamJimFrankBill0
Michael,Sam,Jim,Frank,BillMichaelSamJimFrankBill1
Michael,Sam,Jim,Frank,BillMichaelSamJimFrankBill1
Michael,Sam,Jim,Frank,BillMichaelSamJimFrankBill0
Michael,Sam,Jim,Bill,FrankMichaelSamJimBillFrank1
Michael,Sam,Jim,Bill,FrankMichaelSamJimBillFrank1

<colgroup><col><col span="6"></colgroup><tbody>
</tbody>
The output I want to see in a pivot table in the above example is therefore 1 row of data (as each concatenated cell actually has the same 5 names) that sums up all of the scores (column G), so it should return a score of 5 for the 1 row of concatenated names.

Hope that makes sense.

thanks,

Michael



<colgroup><col><col span="5"></colgroup><tbody>
</tbody>

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

watto84

New Member
Joined
Dec 9, 2017
Messages
3
Sorry the formatting of that table was horrible.....here it is....

Concatenate Rows B-FName1Name2Name3Name4Name5Score
Michael,Sam,Jim,Frank,BillMichaelSamJimFrankBill1
Michael,Sam,Jim,Frank,BillMichaelSamJimFrankBill0
Michael,Sam,Jim,Frank,BillMichaelSamJimFrankBill1
Michael,Sam,Jim,Frank,BillMichaelSamJimFrankBill1
Michael,Sam,Jim,Frank,BillMichaelSamJimFrankBill0
Michael,Sam,Jim,Bill,FrankMichaelSamJimBillFrank1
Michael,Sam,Jim,Bill,FrankMichaelSamJimBillFrank1

<colgroup><col><col span="6"></colgroup><tbody>
</tbody>
 

mrhstn

Active Member
Joined
Jul 25, 2017
Messages
316
You could use a helper column to create the sort then concatenate the results

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Concatenate Rows B-F</td><td style=";">Name1</td><td style=";">Name2</td><td style=";">Name3</td><td style=";">Name4</td><td style=";">Name5</td><td style=";">Score</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Sam,Jim,Frank,Bill</td><td style=";">Michael</td><td style=";">Sam</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">Sam</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Sam,Jim,Frank,Bill</td><td style=";">Michael</td><td style=";">Sam</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">Sam</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Sam,Jim,Frank,Bill</td><td style=";">Michael</td><td style=";">Sam</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">Sam</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Sam,Jim,Frank,Bill</td><td style=";">Michael</td><td style=";">Sam</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">Sam</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Sam,Jim,Frank,Bill</td><td style=";">Michael</td><td style=";">Sam</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">Sam</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Sam,Jim,Frank,Bill</td><td style=";">Michael</td><td style=";">Sam</td><td style=";">Jim</td><td style=";">Bill</td><td style=";">Frank</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style=";">Sam</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Sam,Jim,Frank,Bill</td><td style=";">Michael</td><td style=";">Sam</td><td style=";">Jim</td><td style=";">Bill</td><td style=";">Frank</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style=";">Sam</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I2</th><td style="text-align:left">=COUNTIF(<font color="Blue">$C2:$F2, "<="&C2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J2</th><td style="text-align:left">=COUNTIF(<font color="Blue">$C2:$F2, "<="&D2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K2</th><td style="text-align:left">=COUNTIF(<font color="Blue">$C2:$F2, "<="&E2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L2</th><td style="text-align:left">=COUNTIF(<font color="Blue">$C2:$F2, "<="&F2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">N2</th><td style="text-align:left">=INDEX(<font color="Blue">$C2:$F2, MATCH(<font color="Red">N$1, $I2:$L2, 0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O2</th><td style="text-align:left">=INDEX(<font color="Blue">$C2:$F2, MATCH(<font color="Red">O$1, $I2:$L2, 0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P2</th><td style="text-align:left">=INDEX(<font color="Blue">$C2:$F2, MATCH(<font color="Red">P$1, $I2:$L2, 0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Q2</th><td style="text-align:left">=INDEX(<font color="Blue">$C2:$F2, MATCH(<font color="Red">Q$1, $I2:$L2, 0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A2</th><td style="text-align:left">=N2&","&O2&","&P2&","&Q2</td></tr></tbody></table></td></tr></table><br />
 

mrhstn

Active Member
Joined
Jul 25, 2017
Messages
316
Apologies, I missed column B

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Concatenate Rows B-F</td><td style=";">Name1</td><td style=";">Name2</td><td style=";">Name3</td><td style=";">Name4</td><td style=";">Name5</td><td style=";">Score</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Sam,Michael,Jim,Frank,Bill</td><td style=";">Michael</td><td style=";">Sam</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">Sam</td><td style=";">Michael</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Sam,Michael,Jim,Frank,Bill</td><td style=";">Michael</td><td style=";">Sam</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">Sam</td><td style=";">Michael</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Sam,Michael,Jim,Frank,Bill</td><td style=";">Michael</td><td style=";">Sam</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">Sam</td><td style=";">Michael</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Sam,Michael,Jim,Frank,Bill</td><td style=";">Michael</td><td style=";">Sam</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">Sam</td><td style=";">Michael</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Sam,Michael,Jim,Frank,Bill</td><td style=";">Michael</td><td style=";">Sam</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">Sam</td><td style=";">Michael</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Sam,Michael,Jim,Frank,Bill</td><td style=";">Michael</td><td style=";">Sam</td><td style=";">Jim</td><td style=";">Bill</td><td style=";">Frank</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style=";">Sam</td><td style=";">Michael</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Sam,Michael,Jim,Frank,Bill</td><td style=";">Michael</td><td style=";">Sam</td><td style=";">Jim</td><td style=";">Bill</td><td style=";">Frank</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style=";">Sam</td><td style=";">Michael</td><td style=";">Jim</td><td style=";">Frank</td><td style=";">Bill</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I2</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B2:$F2, "<="&B2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J2</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B2:$F2, "<="&C2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K2</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B2:$F2, "<="&D2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L2</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B2:$F2, "<="&E2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">M2</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B2:$F2, "<="&F2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O2</th><td style="text-align:left">=INDEX(<font color="Blue">$B2:$F2, MATCH(<font color="Red">O$1, $I2:$M2, 0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P2</th><td style="text-align:left">=INDEX(<font color="Blue">$B2:$F2, MATCH(<font color="Red">P$1, $I2:$M2, 0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Q2</th><td style="text-align:left">=INDEX(<font color="Blue">$B2:$F2, MATCH(<font color="Red">Q$1, $I2:$M2, 0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">R2</th><td style="text-align:left">=INDEX(<font color="Blue">$B2:$F2, MATCH(<font color="Red">R$1, $I2:$M2, 0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">S2</th><td style="text-align:left">=INDEX(<font color="Blue">$B2:$F2, MATCH(<font color="Red">S$1, $I2:$M2, 0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A2</th><td style="text-align:left">=O2&","&P2&","&Q2&","&R2&","&S2</td></tr></tbody></table></td></tr></table><br />
 

watto84

New Member
Joined
Dec 9, 2017
Messages
3
Apologies, I missed column B

ABCDEFGHIJKLMNOPQRS
1Concatenate Rows B-FName1Name2Name3Name4Name5Score54321
2Sam,Michael,Jim,Frank,BillMichaelSamJimFrankBill145321SamMichaelJimFrankBill
3Sam,Michael,Jim,Frank,BillMichaelSamJimFrankBill045321SamMichaelJimFrankBill
4Sam,Michael,Jim,Frank,BillMichaelSamJimFrankBill145321SamMichaelJimFrankBill
5Sam,Michael,Jim,Frank,BillMichaelSamJimFrankBill145321SamMichaelJimFrankBill
6Sam,Michael,Jim,Frank,BillMichaelSamJimFrankBill045321SamMichaelJimFrankBill
7Sam,Michael,Jim,Frank,BillMichaelSamJimBillFrank145312SamMichaelJimFrankBill
8Sam,Michael,Jim,Frank,BillMichaelSamJimBillFrank145312SamMichaelJimFrankBill

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

Worksheet Formulas
CellFormula
I2=COUNTIF($B2:$F2, "<="&B2)
J2=COUNTIF($B2:$F2, "<="&C2)
K2=COUNTIF($B2:$F2, "<="&D2)
L2=COUNTIF($B2:$F2, "<="&E2)
M2=COUNTIF($B2:$F2, "<="&F2)
O2=INDEX($B2:$F2, MATCH(O$1, $I2:$M2, 0))
P2=INDEX($B2:$F2, MATCH(P$1, $I2:$M2, 0))
Q2=INDEX($B2:$F2, MATCH(Q$1, $I2:$M2, 0))
R2=INDEX($B2:$F2, MATCH(R$1, $I2:$M2, 0))
S2=INDEX($B2:$F2, MATCH(S$1, $I2:$M2, 0))
A2=O2&","&P2&","&Q2&","&R2&","&S2

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
That worked a treat! Thank you for putting the time in to find a solution.

And i apologize for posting on another forum, i will link this solution to that forum as well. This is the first time i have ever needed to post something in a forum for help, so wasn't across the rules.
 

Forum statistics

Threads
1,086,035
Messages
5,387,406
Members
402,061
Latest member
RAZE

Some videos you may like

This Week's Hot Topics

Top