Consolidating Duplicate Values

[djc123]

Hi there.

I have a table, columns A through J, that has a lot of duplicated data. I would like a formula that consoldates any values that are matched in collumn D (PID), for collumns E, F, G, H & I. Collumns A, B & C do not consoldate, they just keep one value.

Thank you so much!

Danny

[AliGW]

You keep labelling your workbooks Mr Excel - are you aware that this is not the Mr Excel forum?

I will have a look at your workbook presently.

[AliGW]

Select columns A to J - Data ribbon - remove duplicates.

You have a lot of open threads where you have received help, but given your helper no feedback whatsoever. We do expect members to at least thank those who have offered help - it's common courtesy. Thanks.

[djc123]

Hi Ali,

My apologies for my lack of gratitude! I am extremely grateful for the help I receive, I will be sure to be more vocal in the future.

Yes - the file name is indeed incorrect.

In regards to this post, I need to consolodate the values in columns E, F, G, H & I, as I will be using this file as a template for more data.

Are you (or another) able to assist me with a formula for the above?

Kindest regards,

Danny

[AliGW]

I have explained how to do it in post #3.

[djc123]

Hi Ali,

Unfortunately that doesn't consoldate the values in columns E, F, G, H & I - it leaves the non-removed value only.

Or am I doing something wrong?

Thanks again.

Danny

[AliGW]

Well, what you are NOT doing is giving us any examples of what you want, so it's all guesswork!

For instance, how do you expect rows 9 to 25 (orange juice) to look in your consolidated table?

[djc123]

Ok - for the example of orange juice (rows 9-25), it would look like this:

COLUMNS:
A - PRODUCT NAME - remove duplicates only (not consolidated)
B - VAT RATE - remove duplicates only (not consolidated)
C - VAT AMOUNT - remove duplicates only (not consolidated)
D - PID - remove duplicates only (not consolidated) (THIS IS THE COLUMN THAT DRIVES THE FORMULAS)
E - AMOUNT - consolidate
F - PRICE - consolidate
G - TOTAL - consolidate
H - COST VAT - consolidate
I - PROFIT - consolidate
J - CATEGORY TYPE - remove duplicates only (not consolidated)

Orange Juice:
A - PRODUCT NAME - Fresh Orange Juice
B - VAT RATE - 7
C - VAT AMOUNT - 8.83
D - PID - DKS18 (THIS IS THE COLUMN THAT DRIVES THE FORMULAS)
E - AMOUNT - 54
F - PRICE - 255
G - TOTAL - 810
H - COST VAT - 0
I - PROFIT - 757.08
J - CATEGORY TYPE - Unknown (product deleted)

Thank you so much!

Danny

[AliGW]

Please provide a workbook showing what you expect those rows to look like AFTER consolidation. You are not telling us HOW you expect each column to be consolidated.

Still no clearer - sorry.

[djc123]

Hi AliGW,

I have attatched a preview of what I want the final file to look like.

Rows highlighted in yellow are those that had duplicate values.

The calls with red text are those I want consolidating.

I hope that makes sense!

Danny

[AliGW]

OK - this is done with PQ groupings.

M Code:

Please Login or Register  to view this content.

[djc123]

Hi AliGW,

That's wonderful - thank you so much!!!!

Danny

[AliGW]

No worries. There are links to PQ info in my signature.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.