Finding duplicates and adding their adjacent cells

[trier]

Hi, I'm very new to vba and haven't found a thread that matches exactly what I need, although I apologise if this has been discussed before.

I have thousands of rows to compare each time. I need to find duplicate entries in column A and then add their values which are in column B to put in column C. There is only one worksheet each time.

Col A can be numbers only or a mixture of numbers and letters. It starts looking like:

Col A Col B
THH1234 1
TKK254 1
124892 2
6935 1
6935 5
C09654 2

I want it to end like this below where 6935 are recognised as duplicates and their values of 1 & 5 added to be 6. It doesn't matter if the 6 is in the row for the first or second duplicate.

Col A Col B Col C
THH1234 1
TKK254 1
124892 2
6935 1
6935 5 6
C09654 2

It would be ideal if I didn't have to sort column A so that duplicates are next to each other before running the macro but that's not important. Thanks any help is appreciated.

[Prawny]

Have you considered using a pivot table instead of a macro? It is a lot easier and quicker, or is this not an option?

[Marcol]

Try this formula in Column C

Please Login or Register  to view this content.

Drag/Fill down

No need for VBa, and no need to sort.

[trier]

Have you considered using a pivot table instead of a macro? It is a lot easier and quicker, or is this not an option?

Thanks for the suggestion Prawny. I didn't consider pivot tables because I'm not too familiar with them myself and the other staff who will need to use this solution have even less idea about them than I do.

[trier]

Try this formula in Column C

Please Login or Register  to view this content.

Drag/Fill down

No need for VBa, and no need to sort.

Thanks Marcol. I've tried this. The only shortcoming is that every instance of a repeated item in col A has the cumulative sum of it's values put in col C. So in the example 6935 has col C entry as 1 the first time it appears and then 6 the next time. I want only the total sum of the appearances. Would it help to delete duplicates as it goes?

[Marcol]

The easiest way to do this would be ...
In C2

Please Login or Register  to view this content.

Drag/Fill Down as required, then filter out the blanks in Col C.

If you Need a list without duplicates then this array formula in say E2 for example.

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just enter, then Drag Down as required
And in F2

Please Login or Register  to view this content.

Drag/Fill Down as required.

If you go for the second option then using Dynamic Named ranges would be the better option. See the example in this workbook.

[trier]

The easiest way to do this would be ...
In C2

Please Login or Register  to view this content.

Drag/Fill Down as required, then filter out the blanks in Col C.

If you Need a list without duplicates then this array formula in say E2 for example.

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just enter, then Drag Down as required
And in F2

Please Login or Register  to view this content.

Drag/Fill Down as required.

If you go for the second option then using Dynamic Named ranges would be the better option. See the example in this workbook.

Thank's so much Marcol that is exactly what I want. There's so much more for me to learn.

[jimjones666]

I would like to do this but SUBTRACT the adjacent cells. How can this be done?