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:

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>
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
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>
 
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
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>
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
Copy Selected Names To A Different Sheet
Replies
2
Views
128
DanteAmor
DanteAmor
C
  • Solved
Filter multiple sheets based on one value
Replies
3
Views
407
mumps
mumps
franklin_m
  • Question
VBA Search row for instances of pattern in two adjacent cells
Replies
3
Views
116
franklin_m
franklin_m
P
  • Question
Import an Excel file while maintaining alignment with the user comment column
Replies
3
Views
317
Peter_SSs
Peter_SSs
P
  • Solved
How to find large fuel consumption in Excel
Replies
3
Views
172
SunnyAlv
SunnyAlv

Forum statistics

Threads
1,214,819
Messages
6,121,729
Members
449,049
Latest member
MiguekHeka

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