Thread: Duplicates and Consolidation

Hey Everyone,

This is the issue I'm having in excel, I have a worksheet with 8,000 plus rows of data, with about 14 columns. The first columnn (A) contains product ID's, all of the subsequent columns contain price information in dollars, what I would like to do, is combine all fo the duplicate product ID's together and at the same time, add their corresponding price data together as well.

For example below is a generic example, as you can see, there are several products in column a that are the same, but they have differing amounts in the subsequent column,s what i need to be able to do, is take all of the price amounts for duplicate products in column a, and add them all together and at the same getting rid of the duplicate product entries, so the amounts for duplicate products all apear on the same row. I am familar with the countif function, however with over 8,000 rows of data, what i really need, is something to consolidate the duplicates and add their differences together.

Column A Column B Column C Column D

Product Price 1 Price 2 Price 3

ball $1 $2 $3
bat $2 $4 $6
ball $8 $5 $5
hat $4 $7 $8
glove $9 $12 $18
glove $6 $4 $13

Code:

Column A   Column B  Column C Column D

Product      Price 1    Price 2     Price 3

ball            $1         $2         $3
bat             $2         $4         $6
ball            $8         $5         $5
hat             $4         $7         $8
glove           $9         $12        $18
glove           $6         $4         $13

Based on this sample data you provided, what would the result look like?

This is what the end result should look like, the duplicate entries removed, but the totals have been consolidated together. Thanks in advance for your help!

Product Price 1 Price 2 Price 3
ball $9 $7 $8
bat $2 $4 $6
glove $15 $16 $31
hat $4 $7 $8

Code:

Product   Price 1   Price 2   Price 3
ball	     $9	        $7       $8
bat	     $2	        $4       $6
glove	     $15        $16      $31
hat	     $4	        $7       $8

I'd do the following, as exampled in the attached workbook.

Highlight all 8000 IDs and name them IDs in the name box, or use Insert > Name > Define to do it.

I also named the data in the price columns Price1, Price2 and Price3. This makes the formulas easier to understand.

Now, below the original data, use my sample to first create a unique list of IDs. I started at A11. It finds the first unique entry, then displays it. Copy your adjusted formula down until it stops showing unique additions to the list.

Then use the formulas to the right to use the SUMIF function to group them all.

Funny thing, if you do this on a separate Sheet in the same workbook, then extend the list down on the new sheet, the form will update itself as you add new entries on your original sheet. Could be useful.

Oh, I forgot to mention, that formula at A11 is an array. When you create your version, you can't type and press Enter, you have to type and press Ctrl+Shift+Enter. If you don't, you'll get a #/Value error.

I realized I uploaded the simpler version, take a look at the formulas in this version which use NAMES for everything in the SUMIF functions. Much easier to read this way.

Cheers.