Totalling identical cells

**tdangelo** · 02-13-2009, 04:50 PM

Hi,

I am using Excel 2003 (on Windows XP). I need to put together an Excel formula or macro, etc., which will take a table of data (which will always have the same number of columns but the number of rows will vary), and perform the following changes to it:

1)Insert a new column B in between the previous column A and B (B now becomes C)

2) Add a new quantity total to the new column B if the following “if” conditions are met. 2a- Description needs to be identical (as in C12 & C13), 2b- Serial No needs to be identical or blank (as in E12 & E13), 2c- Case # needs to be identical (as in old B12 & B13, now new A12 & A13)

If these three conditions are met a new quantity total in created at the first instance new column B (TTL_QTY), and the duplicate instances (row 13) are deleted or cleared.

Can anyone help me with this or point me in the right direction?

A before and after example is attached.

Thanks.

- Tom

**JBeaucaire** · 02-13-2009, 06:49 PM

You would need macros to manipulate the rows themselves, but a formula to help you ID duplicate entries would be something like this:

=SUMPRODUCT(--(C4&E4=$C$4:$C$26&$E$4:$E$26))

Copy that down from Row4 through 26 and you'll get a duplication count for each row.

EDIT: Just noticed you wanted 3 cells checked, that's this version:

=SUMPRODUCT(--(A4&C4&E4=$A$4:$A$26&$C$4:$C$26&$E$4:$E$26))

**tdangelo** · 02-13-2009, 10:41 PM

=SUMPRODUCT(--(A32&C32&D32=$A$32:$A$54&$C$32:$C$54&$D$32:$D$54)) as a formula in B32:B54 does a great job of count the similar or dissimilar entries.

Now in terms of getting rid of the duplicates, can anyone think of a more elegant solution than:

DATA, FILTER, ADVANCED FILTER, UNIQUE RECORDS ONLY ?

Thanks.

- Tom

**JBeaucaire** · 02-13-2009, 10:46 PM

That looks pretty simple. Define "elegant".

**tdangelo** · 02-13-2009, 11:12 PM

Originally Posted by JBeaucaire

That looks pretty simple. Define "elegant".

Well, I guess I should have tried it before I wrote that because it is not working. I think that because the =SUMPRODUCT(--(A2&C2&D2=$A$2:$A$3000&$C$2:$C$3000&$D$2:$D$3000)) forumla is changing row by row, it is making the row "unique." at least that is my theory. Regardless, when I run those commands Excel is not filtering out the duplicate entries. Any thoughts?

- Tom