I have a psreadsheet that has Part Numbers on columns A3:A2163 and the
quantities on column B3:B2163.

Some of the part numbers have repeats so I want to set it so that the
formula will add the repeating part number quantities.

So I want column C to include the part number and column D to include
the subtotal quantity for that part number.

I would really appreciate the help.

Hi Ben,

A two-step process :
1. For Column A to produce Unique Values in Column C :
Data Filter Advanced Filter with Unique Values
2. In Column D :
=SUMIF(ColumnA,ColumnC,ColumB)

HTH
Cheers
Carim

hi Carim:
I am confused by your message.

Carim wrote:
Hi Ben,

A two-step process :
1. For Column A to produce Unique Values in Column C :
Data Filter Advanced Filter with Unique Values
2. In Column D :
=SUMIF(ColumnA,ColumnC,ColumB)

HTH
Cheers
Carim

Ben,

Sorry for the confusion ...
Let me be more explicit ...
1. Copy Cell A1 to Cell E1 , assuming A1 contains title of column A
2. Select from Menu Data Filter AdvancedFilter
3. Select Copy to another location
4. List Range select your input column A
5. Criteria range select E1:E2
6. Copy to Select destination column i.e. column C
7. DO not Forget to select Unique Records Only

HTH
Cheers
Carim


Ben wrote:
hi Carim:
I am confused by your message.

Carim wrote:
Hi Ben,

A two-step process :
1. For Column A to produce Unique Values in Column C :
Data Filter Advanced Filter with Unique Values
2. In Column D :
=SUMIF(ColumnA,ColumnC,ColumB)

HTH
Cheers
Carim

Dear Carim:

Your instructions worked! Thank you so much! All the 2100 cells now are
fixed :-D

I greatly appreciate it.

Ben
Carim wrote:
Ben,

Sorry for the confusion ...
Let me be more explicit ...
1. Copy Cell A1 to Cell E1 , assuming A1 contains title of column A
2. Select from Menu Data Filter AdvancedFilter
3. Select Copy to another location
4. List Range select your input column A
5. Criteria range select E1:E2
6. Copy to Select destination column i.e. column C
7. DO not Forget to select Unique Records Only

HTH
Cheers
Carim


Ben wrote:
hi Carim:
I am confused by your message.

Carim wrote:
Hi Ben,

A two-step process :
1. For Column A to produce Unique Values in Column C :
Data Filter Advanced Filter with Unique Values
2. In Column D :
=SUMIF(ColumnA,ColumnC,ColumB)

HTH
Cheers
Carim

Ben,

Hope everything is clear now ...
Shail's explanation is excellent

Now you are left with following
in column D, type in

=SUMIF($A$2:$A$3000,C2,$B$2:$B$3000)

and copy all the way down ...

Cheers
Carim

Or another option is to use a Pivot Table.

Make sure the active cell is somewhere in the range A3:B2163 and
choose DataPivotTable then click NextNextFinish

Drag the column B field heading into the "Drop Data Items here" area,
and the column A field heading into the "Drop Row fields here" area.

You've now got a table of part numbers and quantities. If you really
need to, you could copy this table and paste it into columns C&D on
your original sheet, but you'll probably find you've no need.

HTH


On 28 Aug 2006 06:57:33 -0700, "Ben" wrote:

I have a psreadsheet that has Part Numbers on columns A3:A2163 and the
quantities on column B3:B2163.

Some of the part numbers have repeats so I want to set it so that the
formula will add the repeating part number quantities.

So I want column C to include the part number and column D to include
the subtotal quantity for that part number.

I would really appreciate the help.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

richard:
I tried using the PIVOT table and it didn't work. For some reason what
the PIVOT table does is it finds how many of a part number exists and
spits out that numbers instead of totaling the number for each part
number.

Richard Buttrey wrote:
Or another option is to use a Pivot Table.

Make sure the active cell is somewhere in the range A3:B2163 and
choose DataPivotTable then click NextNextFinish

Drag the column B field heading into the "Drop Data Items here" area,
and the column A field heading into the "Drop Row fields here" area.

You've now got a table of part numbers and quantities. If you really
need to, you could copy this table and paste it into columns C&D on
your original sheet, but you'll probably find you've no need.

HTH


On 28 Aug 2006 06:57:33 -0700, "Ben" wrote:

I have a psreadsheet that has Part Numbers on columns A3:A2163 and the
quantities on column B3:B2163.

Some of the part numbers have repeats so I want to set it so that the
formula will add the repeating part number quantities.

So I want column C to include the part number and column D to include
the subtotal quantity for that part number.

I would really appreciate the help.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Presumably the top of the pivot table says "Count of xxx" where "xxx"
is the name of your column B field heading, rather than "Sum of xxx"

That means that one of your 'values' in column B is either blank or a
text cell which looks like a number. When this condition occurs Excel
defaults to a count of the part number occurrences rather than
totalling the numbers of parts.

Double click the words "Count of xxx" in the Pivot Table and select
the "Sum" option.

In case one of your column B 'values' is actually text which looks
like a number and which therefore won;t be counted in the SUM, you
should check column B by typing the formula "= IsNumber(B1)" in C1- or
whichever is the top row of your data, and copy it down.
Look for cells that say False and correct the B column value.

When you've corrected the B column values you can then right click
anywhere in the pivot table and choose 'Refresh Data'

Post back if this is still confusing.

Rgds



On 28 Aug 2006 07:42:48 -0700, "Ben" wrote:

richard:
I tried using the PIVOT table and it didn't work. For some reason what
the PIVOT table does is it finds how many of a part number exists and
spits out that numbers instead of totaling the number for each part
number.

Richard Buttrey wrote:
Or another option is to use a Pivot Table.

Make sure the active cell is somewhere in the range A3:B2163 and
choose DataPivotTable then click NextNextFinish

Drag the column B field heading into the "Drop Data Items here" area,
and the column A field heading into the "Drop Row fields here" area.

You've now got a table of part numbers and quantities. If you really
need to, you could copy this table and paste it into columns C&D on
your original sheet, but you'll probably find you've no need.

HTH


On 28 Aug 2006 06:57:33 -0700, "Ben" wrote:

I have a psreadsheet that has Part Numbers on columns A3:A2163 and the
quantities on column B3:B2163.

Some of the part numbers have repeats so I want to set it so that the
formula will add the repeating part number quantities.

So I want column C to include the part number and column D to include
the subtotal quantity for that part number.

I would really appreciate the help.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Dear Richard:

Thank you for the help, I used it and it worked!

Richard Buttrey wrote:
Presumably the top of the pivot table says "Count of xxx" where "xxx"
is the name of your column B field heading, rather than "Sum of xxx"

That means that one of your 'values' in column B is either blank or a
text cell which looks like a number. When this condition occurs Excel
defaults to a count of the part number occurrences rather than
totalling the numbers of parts.

Double click the words "Count of xxx" in the Pivot Table and select
the "Sum" option.

In case one of your column B 'values' is actually text which looks
like a number and which therefore won;t be counted in the SUM, you
should check column B by typing the formula "= IsNumber(B1)" in C1- or
whichever is the top row of your data, and copy it down.
Look for cells that say False and correct the B column value.

When you've corrected the B column values you can then right click
anywhere in the pivot table and choose 'Refresh Data'

Post back if this is still confusing.

Rgds



On 28 Aug 2006 07:42:48 -0700, "Ben" wrote:

richard:
I tried using the PIVOT table and it didn't work. For some reason what
the PIVOT table does is it finds how many of a part number exists and
spits out that numbers instead of totaling the number for each part
number.

Richard Buttrey wrote:
Or another option is to use a Pivot Table.

Make sure the active cell is somewhere in the range A3:B2163 and
choose DataPivotTable then click NextNextFinish

Drag the column B field heading into the "Drop Data Items here" area,
and the column A field heading into the "Drop Row fields here" area.

You've now got a table of part numbers and quantities. If you really
need to, you could copy this table and paste it into columns C&D on
your original sheet, but you'll probably find you've no need.

HTH


On 28 Aug 2006 06:57:33 -0700, "Ben" wrote:

I have a psreadsheet that has Part Numbers on columns A3:A2163 and the
quantities on column B3:B2163.

Some of the part numbers have repeats so I want to set it so that the
formula will add the repeating part number quantities.

So I want column C to include the part number and column D to include
the subtotal quantity for that part number.

I would really appreciate the help.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Hi Ben

Carim gave you the good trick, to make you more clear about it. Let us
assume, we have the data at A2 till A7 as below

aaa 2
bbb 3
ccc 7
ddd 8
eee 9
ddd 2

Click on Advanced Filter by Data/Filter/Advanced Filter after selecting
the header of the data till the end of the data. At Action select "Copy
to another Location", Then at Copy to: select a cell ( I have choosen
G1). Select "Unique records" check box. Click "OK" button. The unique
records will be pasted over starting from G1.

Now at the adjacent cells starting from H2 I have entered the formula
as :
=SUMIF(A$2:B$10,G2,B$2:B$10)
and copy down till H7. Here I got the desired values.


Hope I made it clear to you.


Thanks

Shail



Ben wrote:
I have a psreadsheet that has Part Numbers on columns A3:A2163 and the
quantities on column B3:B2163.

Some of the part numbers have repeats so I want to set it so that the
formula will add the repeating part number quantities.

So I want column C to include the part number and column D to include
the subtotal quantity for that part number.

I would really appreciate the help.