+ Reply to Thread
Results 1 to 6 of 6

Need to combinemultiple row info into one cell

  1. #1
    Registered User
    Join Date
    02-28-2014
    Location
    Delaware
    MS-Off Ver
    Excel 2003
    Posts
    3

    Need to combinemultiple row info into one cell

    Hey!!

    I have been struggling with this for a few days. I have used some basic if statements but can't get what I need. My spread sheet is 160k rows by 15 columns. I can’t show the spread sheet here due to confidentially. But here is a small example. Now I want to see If I search using column A , can I add the line below in the same cell if it matches, and count it as one so its not Gary, Gary Green, Green. But if there is something new it only adds it once.

    So basically I want to search by my only constant Column A and if it matches the one above it, it adds info the cell above it, if it doesn’t it leaves and keep searching, but if there is new info it adds it to the cell.
    4 Gary Green
    4 Gary Green
    10 Agrawal Rohit R.
    48 Steve Nabil
    48 Steve Nabil
    50 Billy Sames
    50 Billy Sames
    125 Ben Heart
    125 Ben Wash
    125 Ben Heart
    125 Ben Heart
    125 Ben Heart
    125 Ben Heart
    125 Ben Heart
    125 Ben Heart
    125 Ben Heart
    125 Ben Heart
    125 Ben Heart
    125 Ben Heart
    125 Ben Heart
    125 Ben Gray
    So this is what it would kind of look like when its done:
    4 Gary Green
    10 Agrawal Rohit R.
    48 Steve Nabil
    50 Billy Sames
    125 Ben Heart
    125 Ben Heart,Wash, Gray
    Let me know if this makes sense.. Please help would love to be done!

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Need to combinemultiple row info into one cell

    This does not make sense:

    125 Ben Heart
    125 Ben Heart,Wash, Gray

    Why not
    125 Ben Heart, Wash
    125 Ben Heart, Gray

    Or just
    125 Ben Heart, Wash, Gray
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    02-28-2014
    Location
    Delaware
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Need to combinemultiple row info into one cell

    Yeah you're right kind of doesnt make sense.
    Basically I have 160k rows, 15 columns.

    In the first column i have a record number:

    4 Gary Green
    4 Gary Green
    10 Steve Barlow
    48 Bill Hugehs
    48 Bill Hugehs
    50 Tom Santora
    50 Tom Santora
    125 Bill Heart
    125 Bill Heart
    125 Bill Heart
    125 Bill Heart
    125 Bill Heart
    125 Bill Heart
    125 Bill Heart
    125 Bill Heart
    125 Bill Gray
    125 Bill Gray
    125 Bill Gray
    125 Bill Gray
    125 Bill Gray
    125 Bill Gray


    I want to have a formula or VBA that states if the record number matches with the one below it combine with each other and only add new info and erase the rest.
    So then end result would look like this:
    4 Gary Green
    10 Steve Barlow
    48 Bill Hugehs
    50 Tom Santora
    125 Bill Heart,Gray

    The reason i need it liek this, is that this spreadsheet was pulled from a program we use, it pulls the name of the customer and if there is something new in the row it make a new one instead of combining them,
    So some i have 30 Bill hearts ( gray could be a middle name) with the same record name but only one thing is different about them in one of the columns, so I need to see if i can combine them based on the record number so we can work the files, instead of trying to dig threw 160k worth of rows.
    Last edited by davidkibler33; 03-04-2014 at 05:34 PM.

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Need to combinemultiple row info into one cell

    The first step is to sort ascending based on Column A first, then B, then C (It is unclear if you are describing two or three columns, so maybe just A and B) Then use the remove duplicates command, basing it on columns A and B (and C, maybe) . Finally, insert 2 new columns at C (or D) and in C2 use the formula:

    =IF(A2=A1,C1&", "&B2,B2)
    and in D2
    =IF(COUNTIF($A$1:A2,A2)=COUNTIF(A:A,A2),"Keep","")

    IF is is three columns, in D2, use the formula
    =IF(A2=A1,D1&", "&C2,C2)
    and in E2, use
    =IF(COUNTIF($A$1:A2,A2)=COUNTIF(A:A,A2),"Keep","")

    Then copy down, convert them to values, sort based on the "Keep" values, delete the rows where that column is blank, delete the "Keep" column, and then delete the column B or C.

  5. #5
    Registered User
    Join Date
    02-28-2014
    Location
    Delaware
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Need to combinemultiple row info into one cell

    I think we are almost there. I need the row below to combine with the row above it if there is new info. If there not then not add ( i dont need the persons name in there 100x Basically use col A as the matching component.

    So if i have this:
    Col. A Col. B Col. C Coul. D Col. E Col. F Col. G
    125 Gary Green 666 5/1/72, 9/16/73
    125 Gary Green 666 5/1/72, 9/16/73
    125 Gary Green 666 5/1/72, 9/16/73
    125 Gary Green 666 5/1/72, 9/16/73
    125 Gary Green 666 5/1/72, 9/16/73
    125 Gary Green 666 5/1/72, 9/16/73
    125 Gary Green 666 5/1/72, 9/16/73
    125 Gary Green 129 5/1/72, 9/16/73
    125 Gary Green 129 5/1/72, 9/16/73
    125 Gary Gray 129 5/1/72, 9/16/73
    125 Gary Gray 129 5/1/72, 9/16/73
    125 Gary Gray 129 5/1/72, 9/16/73
    125 Gary Gray 129 5/1/72, 9/16/73
    125 Gary Gray 129 5/1/72, 9/16/73

    I need it to look liek this:
    Col. A Col. B Col. C Col. D Col. E Col. F Col. G
    125 Gary Green,Gray 666,129 5/1/72, 9/16/73

    Then we could have the keep function on the one with the full info and we can delete the others.

    So it is seing that 125 matches so it needs to add new info to the cell above or below it ( that part doesnt matter) and then if the formula see the number change in col A it starts over

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Need to combinemultiple row info into one cell

    Could you post a file, with a "Before" sheet, and an "After" sheet, showing what you have and what you want?

+ 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: 5
    Last Post: 06-01-2012, 05:04 PM
  2. Enter Cell Info, Retrieve Info from Website, Record Results
    By DrSues02 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-17-2009, 09:03 AM
  3. lookup cell info and returning line info
    By Wisconsinscw in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-08-2008, 07:05 PM
  4. Replies: 1
    Last Post: 02-21-2006, 10:30 PM
  5. Replies: 3
    Last Post: 02-01-2005, 01:06 PM

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