concatinating similair columns

W

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:

[TABLE="width: 567"]
<colgroup><col><col span="6"></colgroup><tbody>[TR]
[TD]Concatenate Rows B-F		Name1Name2Name3Name4Name5Score
Michael,Sam,Jim,Frank,BillMichaelSamJimFrankBill
Michael,Sam,Jim,Frank,BillMichaelSamJimFrankBill
Michael,Sam,Jim,Frank,BillMichaelSamJimFrankBill
Michael,Sam,Jim,Frank,BillMichaelSamJimFrankBill
Michael,Sam,Jim,Frank,BillMichaelSamJimFrankBill
Michael,Sam,Jim,Bill,FrankMichaelSamJimBillFrank
Michael,Sam,Jim,Bill,FrankMichaelSamJimBillFrank

<colgroup><col style="width:48pt" width="64" span="5"> </colgroup><tbody>
[TD="class: xl65, width: 64"][TABLE="width: 503"]
<colgroup><col><col span="5"></colgroup><tbody>[TR]

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

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

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

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

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

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

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

</tbody>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]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


[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Sorry the formatting of that table was horrible.....here it is....

[TABLE="width: 567"]
<colgroup><col><col span="6"></colgroup><tbody>[TR]
[TD]Concatenate Rows B-F[/TD]
[TD]Name1[/TD]
[TD]Name2[/TD]
[TD]Name3[/TD]
[TD]Name4[/TD]
[TD]Name5[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]Michael,Sam,Jim,Frank,Bill[/TD]
[TD]Michael[/TD]
[TD]Sam[/TD]
[TD]Jim[/TD]
[TD]Frank[/TD]
[TD]Bill[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Michael,Sam,Jim,Frank,Bill[/TD]
[TD]Michael[/TD]
[TD]Sam[/TD]
[TD]Jim[/TD]
[TD]Frank[/TD]
[TD]Bill[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Michael,Sam,Jim,Frank,Bill[/TD]
[TD]Michael[/TD]
[TD]Sam[/TD]
[TD]Jim[/TD]
[TD]Frank[/TD]
[TD]Bill[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Michael,Sam,Jim,Frank,Bill[/TD]
[TD]Michael[/TD]
[TD]Sam[/TD]
[TD]Jim[/TD]
[TD]Frank[/TD]
[TD]Bill[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Michael,Sam,Jim,Frank,Bill[/TD]
[TD]Michael[/TD]
[TD]Sam[/TD]
[TD]Jim[/TD]
[TD]Frank[/TD]
[TD]Bill[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Michael,Sam,Jim,Bill,Frank[/TD]
[TD]Michael[/TD]
[TD]Sam[/TD]
[TD]Jim[/TD]
[TD]Bill[/TD]
[TD]Frank[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Michael,Sam,Jim,Bill,Frank[/TD]
[TD]Michael[/TD]
[TD]Sam[/TD]
[TD]Jim[/TD]
[TD]Bill[/TD]
[TD]Frank[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You could use a helper column to create the sort then concatenate the results


Book1
ABCDEFGHIJKLMNOPQ
1Concatenate Rows B-FName1Name2Name3Name4Name5Score4321
2Sam,Jim,Frank,BillMichaelSamJimFrankBill14321SamJimFrankBill
3Sam,Jim,Frank,BillMichaelSamJimFrankBill04321SamJimFrankBill
4Sam,Jim,Frank,BillMichaelSamJimFrankBill14321SamJimFrankBill
5Sam,Jim,Frank,BillMichaelSamJimFrankBill14321SamJimFrankBill
6Sam,Jim,Frank,BillMichaelSamJimFrankBill04321SamJimFrankBill
7Sam,Jim,Frank,BillMichaelSamJimBillFrank14312SamJimFrankBill
8Sam,Jim,Frank,BillMichaelSamJimBillFrank14312SamJimFrankBill
Sheet2
Cell Formulas
RangeFormula
I2=COUNTIF($C2:$F2, "<="&C2)
J2=COUNTIF($C2:$F2, "<="&D2)
K2=COUNTIF($C2:$F2, "<="&E2)
L2=COUNTIF($C2:$F2, "<="&F2)
N2=INDEX($C2:$F2, MATCH(N$1, $I2:$L2, 0))
O2=INDEX($C2:$F2, MATCH(O$1, $I2:$L2, 0))
P2=INDEX($C2:$F2, MATCH(P$1, $I2:$L2, 0))
Q2=INDEX($C2:$F2, MATCH(Q$1, $I2:$L2, 0))
A2=N2&","&O2&","&P2&","&Q2
 
Upvote 0
Apologies, I missed column B


Book1
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
Sheet2
Cell Formulas
RangeFormula
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
 
Upvote 0
mrhstn said:
Apologies, I missed column B

ABCDEFGHIJKLMNOPQRS
Concatenate Rows B-FName1Name2Name3Name4Name5Score
Sam,Michael,Jim,Frank,BillMichaelSamJimFrankBillSamMichaelJimFrankBill
Sam,Michael,Jim,Frank,BillMichaelSamJimFrankBillSamMichaelJimFrankBill
Sam,Michael,Jim,Frank,BillMichaelSamJimFrankBillSamMichaelJimFrankBill
Sam,Michael,Jim,Frank,BillMichaelSamJimFrankBillSamMichaelJimFrankBill
Sam,Michael,Jim,Frank,BillMichaelSamJimFrankBillSamMichaelJimFrankBill
Sam,Michael,Jim,Frank,BillMichaelSamJimBillFrankSamMichaelJimFrankBill
Sam,Michael,Jim,Frank,BillMichaelSamJimBillFrankSamMichaelJimFrankBill

<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>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

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

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=COUNTIF($B2:$F2, "<="&B2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]=COUNTIF($B2:$F2, "<="&C2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K2[/TH]
[TD="align: left"]=COUNTIF($B2:$F2, "<="&D2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L2[/TH]
[TD="align: left"]=COUNTIF($B2:$F2, "<="&E2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M2[/TH]
[TD="align: left"]=COUNTIF($B2:$F2, "<="&F2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O2[/TH]
[TD="align: left"]=INDEX($B2:$F2, MATCH(O$1, $I2:$M2, 0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P2[/TH]
[TD="align: left"]=INDEX($B2:$F2, MATCH(P$1, $I2:$M2, 0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Q2[/TH]
[TD="align: left"]=INDEX($B2:$F2, MATCH(Q$1, $I2:$M2, 0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]R2[/TH]
[TD="align: left"]=INDEX($B2:$F2, MATCH(R$1, $I2:$M2, 0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]S2[/TH]
[TD="align: left"]=INDEX($B2:$F2, MATCH(S$1, $I2:$M2, 0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=O2&","&P2&","&Q2&","&R2&","&S2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Copy Selected Names To A Different Sheet
Replies
2
Views
292
DanteAmor
DanteAmor
C
  • Solved
Filter multiple sheets based on one value
Replies
3
Views
523
mumps
mumps
E
  • Solved
All Unique Combinations from 5 Columns of Data + Customizations - Excel 365
Replies
6
Views
241
Excel Jason
E
N
  • Solved
Dynamic formula to pull in column headers based on column values within a row
Replies
2
Views
247
n64kps
N
M
  • Question Question
VBA Subtotal 47,000+ Rows
Replies
1
Views
259
igold
igold

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

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