+ Reply to Thread
Results 1 to 8 of 8

concatinating similar columns

  1. #1
    Registered User
    Join Date
    02-25-2004
    Posts
    4

    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

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    48,642

    Re: concatinating similar columns

    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    2010 & 2016
    Posts
    5,360

    Re: concatinating similar columns

    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

  4. #4
    Forum Moderator Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    9,683

    Re: concatinating similar columns

    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).
    You really don't have to give me rep points for moderating. It's part of the job. Keep rep points for those who find solutions

  5. #5
    Registered User
    Join Date
    02-25-2004
    Posts
    4
    Quote Originally Posted by Pepe Le Mokko View Post
    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.

  6. #6
    Registered User
    Join Date
    02-25-2004
    Posts
    4
    Quote Originally Posted by watto84 View Post
    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

  7. #7
    Forum Moderator Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    9,683

    Re: concatinating similar columns

    Please add links to ALL your cross posts - Thx

  8. #8
    Registered User
    Join Date
    02-25-2004
    Posts
    4

    Re: concatinating similar columns

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1