+ Reply to Thread
Results 1 to 11 of 11

Consolidate Duplicate Rows into One Cell

  1. #1
    Registered User
    Join Date
    04-05-2011
    Location
    Dale City, VA
    MS-Off Ver
    Excel 2003
    Posts
    9

    Consolidate Duplicate Rows into One Cell

    Can anyone figure out how to make this two part formula? (preferably not VB) I believe all that needs to be used is INDEX, IF and/ or COUNTIF. Just can't figure it out.

    A = Customer Number, B = Price 1, C = Price 2, D = Price 3, E1 = Price 4

    PART 1
    If A1 is equal to any cell within A2:A10, copy the adjacent cell (column B) into C1
    (e.g.: if A3 is equal to A1, copy B3 into C1).

    PART 2
    If C1 is occupied, copy B3 into D1, if C2 is occupied, copy B3 into E1, etc.

    OR

    If you can fill C1, D1, E1 consecutively based on descending rows.
    eg.: B3 copied to C1, B7 copied to D1, B10 copied to E1.

    ~Aeoneye
    Last edited by Aeoneye; 04-06-2011 at 03:14 PM.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Consolidate Duplicate Rows into One Cell

    Hello,

    For part 1, put this formula in C1

    =IF(ISNA(MATCH(A1,A2:A10,0)),"",INDEX(B2:B10,MATCH(A1,A2:A10,0)))

    I don't understand the rest of your question. C1 has the formula that looks up the value. What do you mean by "is occupied"?

    Please post a workbook with a data sample and explain in context.

    cheers,

  3. #3
    Registered User
    Join Date
    04-05-2011
    Location
    Dale City, VA
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Consolidate Duplicate Rows into One Cell

    Thanks for responding so quickly!

    Sorry I wasn't completely clear. "Occupied" as in if it already has information in it. Your formula for part 1 worked perfectly actually! . I might need more help down the road with this same spreadsheet. I will just post again though. Thanks!

  4. #4
    Registered User
    Join Date
    04-05-2011
    Location
    Dale City, VA
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Consolidate Duplicate Rows into One Cell

    trying to figure out how to put [solved] in the front.

  5. #5
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Consolidate Duplicate Rows into One Cell

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

  6. #6
    Registered User
    Join Date
    04-05-2011
    Location
    Dale City, VA
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Consolidate Duplicate Rows into One Cell

    I have another question based off of this post. What's the formula to copy adjacent cells to unique cells instead of duplicate cell?
    Last edited by Aeoneye; 04-06-2011 at 04:10 PM.

  7. #7
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Consolidate Duplicate Rows into One Cell

    Come again?

    Can you upload a data sample as an Excel workbook and explain what you want to do in context? It's so much easier to discuss real cells than talking about the concept of cells.

  8. #8
    Registered User
    Join Date
    04-05-2011
    Location
    Dale City, VA
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Consolidate Duplicate Rows into One Cell

    I'll send you the excel file for what I am trying to do. Disregard what I recently asked for. Perhaps you can find a solution to what I am trying to do by seeing the excel file.

    The tabs at the bottom is the process I am using so far. Under the tab named "Original", I used the formula you first gave me in the "price 2, 3 and 4" columns . Tab "FINAL" is what my result needs to be. There are no formulas under FINAL because I can't figure out how to make it work. Thank you again.

    note: As it shows on the "FINAL" tab, i know how to copy two columns and paste them into one cell and I know how to add $0.00 format to a number through formula. No need to explain that.
    Attached Files Attached Files
    Last edited by Aeoneye; 04-06-2011 at 05:39 PM. Reason: added note

  9. #9
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Consolidate Duplicate Rows into One Cell

    You cannot do that with formulas.

    The FINAL layout only has one row per number code, and the duplicate rows are removed. No formula can reference a row that has been removed.

    You'll need VBA to create such a layout or use a pivot table to create just the totals per number code.

    I suggest you start a new question.

  10. #10
    Registered User
    Join Date
    04-05-2011
    Location
    Dale City, VA
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Consolidate Duplicate Rows into One Cell

    I actually think I can do it. I have one more request. Can you make this formula to pull from unique cells and not duplicate cells?
    =IF(ISNA(MATCH(A1,A2:A10,0)),"",INDEX(B2:B10,MATCH(A1,A2:A10,0)))

    If I am correct, I will post it here. If not, I will make a new post requesting a VBA code.

  11. #11
    Registered User
    Join Date
    04-05-2011
    Location
    Dale City, VA
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Consolidate Duplicate Rows into One Cell

    I did it

    I guess I didn't need the unique formula.

    The main reason I made this is so I can mail merge properly. Our software doesn't export prices properly. Instead of exporting name, price 1, price 2 etc into one row, it exports the prices as separate rows. As you probably know, this is terrible for mail merge because each row in excel resembles a separate document in mail merge.

    This is the process:

    1. Export a report from our software
    2. Copy all cells from that report's excel file, paste into "original" tab
    3. "extraction" will automatically formulate Price 1, Price 2 etc onto the proper rows.
    4. FINAL converts all numbers to text. This reason being, If a price is 100.20, it will show up in mail merge as 100.2 even if I format the number to $100.20 in excel. =TEXT(extraction!M2, "$0.00")

    This file is fully functional. I just have to test this one a 1500 record report. Look at the formulas and you'll see what I mean. "copy to original 1" and "copy to original 2" are meant to test different lists. Copy/ paste "copy to original 2" into the "original" tab. and "extraction"/ "FINAL" will do all the magic.

    Let me know if you find any holes. Thanks.
    Attached Files Attached Files
    Last edited by Aeoneye; 04-07-2011 at 01:17 PM. Reason: clarification

+ 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