Combining rows of like data?

[Guest]

I have a spreadsheet that contains rows with multiple entries for the same
item. How can I use a filter or a function to combine the values for the
like items in to one row?

Example:
In the same chart, item number 40-011 appears three times with quantities of
5, 3, and 2. How can I filter the data so there is one occurence of 40-011
with a quantity of 10?

 

[Guest]

Kinda hard to say without a bit more info on wht your source data looks like
but I might be inclined to use a pivot table to combine the data... Data ->
Pivot table -> Follow the wizard...

 

[Guest]

Kinda hard to say without a bit more info on wht your source data looks like
but I might be inclined to use a pivot table to combine the data... Data ->
Pivot table -> Follow the wizard...

 

[Guest]

Thanks Jim. I will try and describe my source data better. The data looks
like this:

Item Qty
40-001 4
40-011 5
40-028 4
40-011 3
10-012 4
40-011 2

So, as you can see, 40-011 appears in the list three times. How can I
consolidate the data so there is one line item for 40-011 that displays a Qty
of 10?

My actual source data is about 15,000 rows long, so you can see how it would
be tedious to try and find all the occurences of 40-011 and then doing the
math to total them under one line.

 

[Guest]

Thanks Jim. I will try and describe my source data better. The data looks
like this:

Item Qty
40-001 4
40-011 5
40-028 4
40-011 3
10-012 4
40-011 2

So, as you can see, 40-011 appears in the list three times. How can I
consolidate the data so there is one line item for 40-011 that displays a Qty
of 10?

My actual source data is about 15,000 rows long, so you can see how it would
be tedious to try and find all the occurences of 40-011 and then doing the
math to total them under one line.

 

[Guest]

I feel like the answer will be something similar to the Advanced Filter under
the Data tab. I want to filter a range of cells, leaving only unique
entries, but I want the values of the deleted entries to be aggregated under
the one remaining unique entry.

 

[Guest]

I feel like the answer will be something similar to the Advanced Filter under
the Data tab. I want to filter a range of cells, leaving only unique
entries, but I want the values of the deleted entries to be aggregated under
the one remaining unique entry.

 

[Guest]

Look at SUMPRODUCT: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Dave

 

[Guest]

Look at SUMPRODUCT: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Dave

 

[Guest]

See specifically the section of the paper linked to below titled "Evolving
use of SUMPRODUCT"

There's your answer.

 

[Guest]

See specifically the section of the paper linked to below titled "Evolving
use of SUMPRODUCT"

There's your answer.

 

[Guest]

=SUMPRODUCT(--(A2:A7="40-011"),B2:B7)

Adjuct range to suit.
Note: SUMPRODUCT function does not work if you use a whole columns

 

[Guest]

=SUMPRODUCT(--(A2:A7="40-011"),B2:B7)

Adjuct range to suit.
Note: SUMPRODUCT function does not work if you use a whole columns