Summing duplicates excel.

Hi,

EDIT: Attached example workbook. 3 tabs - 'Starting Point' is unprocessed. 'What I Want' is how I want the data to look when processed. 'What I Get' is what happens when I use excels consolidate tool.

I've read a few forums/webpages on this subject but none of them seem to cover my issue.

I have a list with multiple columns with text and numbers (a parts list) that has the same parts repeated throughout the list.

1.JPG

I want to consolidate this list to look this this (i.e. only sum the columns highlighted green):

2.JPG

But what I actually get is this:

3.JPG

Where ALL the columns are being summed and the text disappears.

Can anyone tell me if there is an easier way of doing this than manual going through adding up the quantities and the deleting the duplicates?

Thanks,
Luke.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Luke
I created a Pivot Table in the attached that resembles what you are looking for.

Firstly unique extract.
"c16"

Formula:

Please Login or Register  to view this content.

ctrl+shift+enter
Above formula copy paste upto "H" column. & column "J" & "K"
"I16"

Formula:

Please Login or Register  to view this content.

"L16"

Formula:

Please Login or Register  to view this content.

Easy one first:

=SUMIF('Starting Point'!$C$3:$C$32,$C16,'Starting Point'!$I$3:$I$32)

Red: for every cell in this range
Orange: that is equal to this value
Black return the correspondng vin I2:I32 and add them up.

IFERROR(INDEX('Starting Point'!C$3:C$32,MATCH(0,COUNTIF(C$15:C15,'Starting Point'!C$3:C$32),0)),"")

Red: look in this range and see if any cells contain the same as
Orange: this range.

It will return a string of 0s or 1s. Zero if it finds matching values, 1 if it does.

Cyan: return the row number of the first zero it finds. In the first cell it will always be the first row, as you haven't treturned any unique values yet.

Blue, return the corresponding value from column C.

Black, if an error is returned, return a blank instead.

Personally, I'd use this instead:

=IFERROR(INDEX('Starting Point'!C$3:C$32,MATCH(0,INDEX(COUNTIF(C$15:C15,'Starting Point'!C$3:C$32),),0)),"")

the bit in blue removes the need to set the formula as an array. Ordinary enter will work fine.

That seems a bit clanky. Are you searching by order number? If so, then wouldn't it be nice to have a dropdown box with a list of unique order number. You choose one and the whole thing populates??

See sheet for a sample. happy to explain. If you like it, I need to know roughly... the maximum number of rows in Starting Point and roughly the maximum number of unique order numbers you will have.

Can be done. I'll look at it later.

Hi. I was having a senior moment... Take a look. You still need to answer my Qs in Post 11, before we can finalise this.

BtW, I accidentally deleted your two named ranges!!

Sure. Tomorrow morning. Enjoying some heady Spanish wine now and don't want to mess up!

Ok. I'll add it to the list.

G.

You have 5 Named Ranges. I managed to delete your survivingone, so you'll have to add them both back in!!

Four are exactly the same, autmatically adjusting the range in a column (e.g. column C) from C4, where the data start to the last non-empty cell in the column. As the number of rows increase/decrease, the range compensates automatically. They all look like this:
=Raw!$C$4:INDEX(Raw!$C:$C,MATCH(1E+100,Raw!$C:$C))

The different one, does exactly the same, but looks different as it needs to deal with formula blanks in the range. A formula blank is not the same as an empty cell. It provides the dropdown list for D1 on the report sheet. It takes the results of the formula (explained previously) to produce unique order numbers. So the list contains "All", unique order numbers and formula blanks. The setup of this range uses LEN(range)>0 to select only the non-blank cells in the range. It looks like this:

=Raw!$P$2:INDEX(Raw!$P$2:$P$27,SUMPRODUCT(--(LEN(Raw!$P$2:$P$27)>0)))

The SUMIF formulae you had have been replaced by SUMIFS:
=IF(C4="","",SUMIFS(Quantity,Components,C4,Orders,IF($D$1="All","<>",$D$1)))

so that if ALL is selected, it will sum all the non-blank cells in the range, otherwise it only sums those = D1.

Finally, the big formula:

=IFERROR(INDEX(Raw!C:C,AGGREGATE(15,6,ROW(Components)/((Orders=IF($D$1="All",Orders,$D$1))*(COUNTIF($C$3:$C3,Components)=0)),1)),"")

Red: If D1 = All, return all, otherwise return only those equal to D1
Orange: providing they have not already occurred in the range C$3:C3
Cyan: return the row number
Cyan: in ascending row order
Purple: return the first instance
Dark green: finally returning the corresponding value from C:C
Blank: returning a blank when valid results run out.

There's no fool like an old fool. I patiently went through all the named ranges in the data body, changing the start row from row 3 to row 4. Now... I have no idea why I did that. Reversed. They all start in row 3 one again!!

You're welcome. Thanks for the kind words and for the rep.