concatinating similar columns

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?


Concatenate Rows B-F Name1 Name2 Name3 Name4 Name5 Score
Michael,Sam,Jim,Frank,Bill Michael Sam Jim Frank Bill 1
Michael,Sam,Jim,Frank,Bill Michael Sam Jim Frank Bill 0
Michael,Sam,Jim,Frank,Bill Michael Sam Jim Frank Bill 1
Michael,Sam,Jim,Frank,Bill Michael Sam Jim Frank Bill 1
Michael,Sam,Jim,Frank,Bill Michael Sam Jim Frank Bill 0
Michael,Sam,Jim,Bill,Frank Michael Sam Jim Bill Frank 1
Michael,Sam,Jim,Bill,Frank Michael Sam Jim Bill Frank 1


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

It's an interesting issue to solve. Unless you want to go down the VBA route, I think you may want to alphabetise the names in helper columns and then concatenate those.

Here's a link to another ExcelForum post with a similar issue that takes that approach. Perhaps it would work for you...
https://www.excelforum.com/excel-gen...cal-order.html

BSB

Originally Posted by Pepe Le Mokko

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

I apologise I was not aware of this rule. The solution I believe has is to use a helper coloumn.

The other possible solution I am trying is an If statement with hkookup on each name.

Originally Posted by watto84

I apologise I was not aware of this rule. The solution I believe has is to use a helper coloumn.

The other possible solution I am trying is an If statement with hkookup on each name.

I will post results, if I get it to work

Here is the solution to my problem:

https://www.mrexcel.com/forum/excel-...ml#post4966264

Apologies for cross posting, this was my first time posting for help on Excel. I know for next time.