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>
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,307
Messages
5,413,695
Members
403,496
Latest member
chamshop

This Week's Hot Topics

Top