+ Reply to Thread
Results 1 to 7 of 7

concatenating some data from multiple rows into multiple columns based on a unique no.

  1. #1
    Forum Contributor
    Join Date
    02-09-2004
    Posts
    154

    concatenating some data from multiple rows into multiple columns based on a unique no.

    I have a very large file (94000 rows) with 3 columns. It is very difficult to explain so I have attached a sample with a small section of those 3 columns on the left, and what I am trying to achieve on the right.

    Basically the end result needs to show the ID number only once per row, with the party names concatenated together under the various interest types. I have 11400 ID's but currently they are being duplicated each time there is a different party name and/or interest type, so I am trying to get the file down to 11400 lines but retain the party name and interest type information by concatenating them.

    For example in the original list there are 2 freeholders and 3 leaseholders for ID LP00004. I need to concatenate the 2 freeholders into a freehold column, and the leaseholders into a leasehold column so that the end result is one row per ID number, but still containing all of the information in the original list.

    This has been done before with this data but I am no longer in touch with the person that did it and have no idea how it was done. I suspect they might have used several pivot tables to manipulate the data and then cut and paste them but I haven't been able to work out what they did. I do know that sometimes the number of party names was so long it would not fit into a single cell and got cut off (which again leads me to believe it might have been done via some pivot tables rather than a macro which might fall over if the text won't fit in the cell?).

    Anyone know how to do this? I don't mind whether it is via a macro or pivot tables, I am using excel 2013.

    Thanks
    Attached Files Attached Files
    Last edited by neowok; 05-30-2016 at 10:17 AM. Reason: added more data to sample file

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,849

    Re: concatenating some data from multiple rows into multiple columns based on a unique no.

    I could give you a formula solution (i.e. neither macro nor pivot table), but I'm working in the garden today so it will be (much) later on.

    Hope this helps.

    Pete

  3. #3
    Forum Contributor
    Join Date
    02-09-2004
    Posts
    154

    Re: concatenating some data from multiple rows into multiple columns based on a unique no.

    Sure formula would do, didn't think it was possible with a formula but maybe it is :P

    thanks

  4. #4
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: concatenating some data from multiple rows into multiple columns based on a unique no.

    Try this in your Data sheet, for results starting "F1".
    Please Login or Register  to view this content.
    Regards Mick
    Last edited by MickG; 05-30-2016 at 11:08 AM.

  5. #5
    Forum Contributor
    Join Date
    02-09-2004
    Posts
    154

    Re: concatenating some data from multiple rows into multiple columns based on a unique no.

    Thanks that seems to work perfectly . I MIGHT need to add some extra interest types as there are actually 14 (such as Tenant and Beneficiary which aren't in the sample sheet), but I don't think I need them all and can hopefully figure out how to modify it to add an extra couple later on.

  6. #6
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: concatenating some data from multiple rows into multiple columns based on a unique no.

    You're welcome

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,380

    Re: concatenating some data from multiple rows into multiple columns based on a unique no.

    Quote Originally Posted by neowok View Post
    I MIGHT need to add some extra interest types as there are actually 14 (such as Tenant and Beneficiary which aren't in the sample sheet)
    No need to do it....
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 11-14-2015, 03:48 PM
  2. [SOLVED] Data validation- Remove duplicate records by concatenating multiple columns to get unique
    By thara.p24 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 08-09-2014, 12:57 AM
  3. Replies: 2
    Last Post: 02-06-2013, 04:44 AM
  4. Replies: 4
    Last Post: 01-16-2013, 10:06 PM
  5. Replies: 1
    Last Post: 03-02-2011, 04:14 PM
  6. Replies: 1
    Last Post: 05-18-2009, 11:21 AM
  7. Remove data based on criteria from multiple columns and rows
    By jvegastn in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-09-2009, 11:42 AM

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