# Consolidate Duplicate Rows into One Cell

1. ## 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

2. ## 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. ## 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. ## Re: Consolidate Duplicate Rows into One Cell

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

5. ## Re: Consolidate Duplicate Rows into One Cell

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

6. ## 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?

7. ## 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. ## 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.

9. ## 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. ## 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. ## 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.

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

#### 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