Compare Cells in Column A, and add all like rows together

[TimBZKK]

Hello! I'm looking for some help with a macro. I have a list of SKUs, and I'd like to be able to run a macro that finds all like SKUs, adds the values in columns C, D, E, F, and G in the first row of the like SKU, and delete the remaining rows. Here is an example.

Before Macro:

BlstSGMXAplIph5Blk 0 28 22 0 11
BlstSGMXAplIph5Blk 6 39 7 0 2
BlstSGMXAplIph5Blu 0 5 0 0 0
BlstSGMXAplIph5Blu 0 13 0 0 0

After Macro:

BlstSGMXAplIph5Blk 6 67 29 0 13

BlstSGMXAplIph5Blu 0 18 0 0 0

Can anyone help me?

[xladept]

Are they sorted or can they be sorted?

[TimBZKK]

They can, as long as the SKUs stay with their numbers. The SKU is an inventory item (on the left), and the numbers on the right are quantity posted, quantity closed, quantity sold, quantity left in inventory, and hits per day. So those values have to stay with the SKUs, but they can be sorted up and down.

Normally, I would just use a pivot table, but this is going to be a list of 400+ items, update bi-weekly.

[AB33]

It is difficult to see your data in text format. Please attach a sample book.

[TimBZKK]

Done and done!

SampleBook.xlsx

[AB33]

Try the attached

[TimBZKK]

This works perfectly! Thank you very much!

[AB33]

Tim,
You are welcome!

[TimBZKK]

Quick question, AB33. If I want to drop that last column from my sheet (the Hits Per Day column), how would I go about changing the macro scripting?


**Edit**

Nevermind, I didn't see that it was creating a new sheet every time, I thought it was the same sheet. My bad!

[TimBZKK]

Ok, I am getting an issue with this code that I did not see until now. It's removing all duplicate rows, but it's only adding two together. Some of the SKUs have 3 or 4 copies. Any idea how I would go about fixing this? the code is below:

Please Login or Register  to view this content.

[AB33]

The code was written for the attached sample. It was meant to add values in columns C-G if SKUs are identical. That is column A.
I do not think it only adds two rows and left the rest of the rows if all these rows are identical. I want to see your sample and show me what is not working.

[TimBZKK]

This should have a larger range of values to work with.

Book2.xlsx

[AB33]

I did not see any issue with my first code, still works as it did the first time. I have lighted the duplicates using conditional formatting. If you are looking for any match, not just identical ones, this is a wrong code. It only deals with exact match words as Pivot Table does.