MACRO: Consolidate 'like' entries in table as one entry

[cjhiggins]

Hello again Excel Experts...

I need some serious help regarding this macro...

1)It must be a macro, not pivot table, please don't suggest a pivot table.

2) Here is the concept of the macro:

It takes IDENTICAL (first and last name) entries that are entered in column 'B' and consolidates the data in columns 'C' and 'D' respectively.

3) Columns 'B', 'C', and 'D' are the only columns being targeted in this macro.

So...instead of having multiple entries under the same name with different data, it adds it together under a single entry being of the same name.

I'm really stuck. Please see attached my example spreadsheet, with 'before the macro' is applied and 'after' it is applied.

Please consider that while on my example sheet there are only 6 entries, the list could be considerably longer and would need to affect the entire sheet within the perimeters set above.

This is a challenge. Please help. Thank you.

[cjhiggins]

Ife anyone requires additional information or finds my post confusing please let me know and I'd be happy to clarify

[AB33]

Since you want to consolidate columns B and C only, the first match row would be appear in column E.
The output is in new sheet.

[cjhiggins]

Sorry, but what does that mean?

Update: I've taken a look at your output sheet and I don't see how this solves my issue.

[AB33]

You should like at the output and see if the code works for you.

[cjhiggins]

I see what you've done, however I need the macro to work within the single spreadsheet, not create a new one. I'm sorry but this doesn't work for me but its close!

[cjhiggins]

Can anyone help me please?

[AB33]

The code works as long as you can cope with merged cells. Merged cells are nightmare in excel, so if you have merged cells, you will get a funny output.

[cjhiggins]

Thank you AB33 for sticking with me, this code is a lot better however can you write a code that doesn't effect column 'A' (which is the ranking numbers), and that consolidates the entries on the 'real table' and not the 'after macro example table' Because in reality there is no 'after macro table' its there as an example of how the real table should look when the macro is applied. Thank you!

This isn't a movement from one table to another, its altering the one existing table.

[AB33]

Unless you get rid of the merged cells, the code will not work.

[cjhiggins]

Which merged cells? None of the affected data contains merged cell. The title for the workbook is a merged cell, as well as the title of that particular table.

[cjhiggins]

UPDATE ON EXAMPLE SPREADSHEET BEFORE & AFTER.

See attached.

[jindon]

This should do

Please Login or Register  to view this content.

[cjhiggins]

Hey Jindon thanks a lot! However this code alters column 'E', I need that column to remain unaltered by the code.

[jindon]

You mean?

Please Login or Register  to view this content.

[cjhiggins]

No. I mean so that when the data is consolidated it will still calculate the average/transaction which is what column 'E' does. Your code writes over that column.

What is being consolidated is strictly column 'B' 'C' and 'D'. Not 'A or 'E'

[jindon]

Ahhh, OK

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[cjhiggins]

THANK YOU THANK YOU THANK YOU !!! A week I've been contending with this code and you've done it in a half hour span! Thank you so much!

[jindon]

No problem.

Please don't forget to mark this thread as "Solved".

[jindon]

Re your message.

You don't want to get col.E to be changed?

Please Login or Register  to view this content.

[cjhiggins]

Thank you so much Jindon I've confirmed this code on my master sheet and it works beautifully. Sorry about the confusion. I won't forget this. Thank you!

[cjhiggins]

Jindon, just one more thing I promise I feel so bad for taking so much of your time.

When you clear the contents of columns 'B thru D' it leaves column 'E' with this:

#Div/0!

But it should still say '0' because this code: =IF(D10=0,"0",C10/D10) [ignore that specific range] should remain unchanged even after the contents of B thru D are cleared.

I bow to your skills!

[cjhiggins]

Attached is the example spreadsheet with your newest code.

Consolidate the entries first. Your code is amazing.

But then clear the contents for the entries B:D and see what it does to col.E

[jindon]

OOps,

Can you replace back to the original?

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[cjhiggins]

Thank you its complete. If your ever in Mississauga, Ontario, Canada send me a line. I owe you a drink!