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

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
__________________________

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

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 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

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.

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

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
__________________________

Now that I have that figured out, the last thing I want to do is this.
One set of columns contains the information for the customer and the
other set of column contains my information. I want to then compare the
Part Number and Qty that I have versus what the customer has. If there
is a Part # that the customer has that I don't have (or vice versa), I
want the formula to tell me. My information is as follows:

My Info
A B
Part No Qty
102634 2
102635 8
102637 16
103319 3
103320 4
.. .
.. .
.. .


Customer Info
D E
Part No Qty
102634 1
102635 1
102637 16
103319 1
103320 4
.. .
.. .
.. .



Carim wrote:
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

In column F: checks "My Part" vs "Customer Part" - if matched, checks
quantities

=IF(ISNA(VLOOKUP($A2,$D$2:$E$6,2,0)),"Customer Part
missing",IF(VLOOKUP($A2,$D$2:$E$6,2,0)<$B2,"Quant ities differ",""))

In column G: checks "Customer Part" vs "My Part"- if matched, checks
quantities


=IF(ISNA(VLOOKUP($D2,$A$2:$B$6,2,0)),"My Part
missing",IF(VLOOKUP($D2,$A$2:$B$6,2,0)<$E2,"Quant ities differ",""))

HTH

"Ben" wrote:

Now that I have that figured out, the last thing I want to do is this.
One set of columns contains the information for the customer and the
other set of column contains my information. I want to then compare the
Part Number and Qty that I have versus what the customer has. If there
is a Part # that the customer has that I don't have (or vice versa), I
want the formula to tell me. My information is as follows:

My Info
A B
Part No Qty
102634 2
102635 8
102637 16
103319 3
103320 4
.. .
.. .
.. .


Customer Info
D E
Part No Qty
102634 1
102635 1
102637 16
103319 1
103320 4
.. .
.. .
.. .



Carim wrote:
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

Could you please further explain your instructions? I'm having a little
bit of a problem.

Toppers wrote:
In column F: checks "My Part" vs "Customer Part" - if matched, checks
quantities

=IF(ISNA(VLOOKUP($A2,$D$2:$E$6,2,0)),"Customer Part
missing",IF(VLOOKUP($A2,$D$2:$E$6,2,0)<$B2,"Quant ities differ",""))

In column G: checks "Customer Part" vs "My Part"- if matched, checks
quantities


=IF(ISNA(VLOOKUP($D2,$A$2:$B$6,2,0)),"My Part
missing",IF(VLOOKUP($D2,$A$2:$B$6,2,0)<$E2,"Quant ities differ",""))

HTH

"Ben" wrote:

Now that I have that figured out, the last thing I want to do is this.
One set of columns contains the information for the customer and the
other set of column contains my information. I want to then compare the
Part Number and Qty that I have versus what the customer has. If there
is a Part # that the customer has that I don't have (or vice versa), I
want the formula to tell me. My information is as follows:

My Info
A B
Part No Qty
102634 2
102635 8
102637 16
103319 3
103320 4
.. .
.. .
.. .


Customer Info
D E
Part No Qty
102634 1
102635 1
102637 16
103319 1
103320 4
.. .
.. .
.. .



Carim wrote:
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

Put the first formula in F2 and copy down. This uses VLOOKUP to match "My
Part" against "Customer Part"; if match is found it checks to see if
quantities are equal.

Put the second formula in G2 and copy down. Again this uses VLOOKUP to match
"Customer Part" against "My Part"; if match is found it checks to see if
quantities are equal.

In both formulae, you will need to change the ranges to suit your data i.e
the B6/E6 need to be changed to the maximum number of rows.

HTH

"Ben" wrote:

Could you please further explain your instructions? I'm having a little
bit of a problem.

Toppers wrote:
In column F: checks "My Part" vs "Customer Part" - if matched, checks
quantities

=IF(ISNA(VLOOKUP($A2,$D$2:$E$6,2,0)),"Customer Part
missing",IF(VLOOKUP($A2,$D$2:$E$6,2,0)<$B2,"Quant ities differ",""))

In column G: checks "Customer Part" vs "My Part"- if matched, checks
quantities


=IF(ISNA(VLOOKUP($D2,$A$2:$B$6,2,0)),"My Part
missing",IF(VLOOKUP($D2,$A$2:$B$6,2,0)<$E2,"Quant ities differ",""))

HTH

"Ben" wrote:

Now that I have that figured out, the last thing I want to do is this.
One set of columns contains the information for the customer and the
other set of column contains my information. I want to then compare the
Part Number and Qty that I have versus what the customer has. If there
is a Part # that the customer has that I don't have (or vice versa), I
want the formula to tell me. My information is as follows:

My Info
A B
Part No Qty
102634 2
102635 8
102637 16
103319 3
103320 4
.. .
.. .
.. .


Customer Info
D E
Part No Qty
102634 1
102635 1
102637 16
103319 1
103320 4
.. .
.. .
.. .



Carim wrote:
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

Ok, I found another way to do it but I want to do this:
I am comparing two lists in excel. The first list contains part
numbers and it is my list. The next list contains part numbers and it
is the customer's list. I have compared both lists to determine what
the customer has and what I have. If the customer has it and I have it,

then the formular returns "yes" if not then "no." I would like to
create a macro that cuts and pastes the "no's" into a new column.

The Data is organized as follows


Customer Data (cells 2 thru cells 584)
Column A
Part No.
102634
..
..
..
-------------
My Data (cells 2 thru cells 953)
Column D
Part No.
102635
..
..
..



Toppers wrote:
Put the first formula in F2 and copy down. This uses VLOOKUP to match "My
Part" against "Customer Part"; if match is found it checks to see if
quantities are equal.

Put the second formula in G2 and copy down. Again this uses VLOOKUP to match
"Customer Part" against "My Part"; if match is found it checks to see if
quantities are equal.

In both formulae, you will need to change the ranges to suit your data i.e
the B6/E6 need to be changed to the maximum number of rows.

HTH

"Ben" wrote:

Could you please further explain your instructions? I'm having a little
bit of a problem.

Toppers wrote:
In column F: checks "My Part" vs "Customer Part" - if matched, checks
quantities

=IF(ISNA(VLOOKUP($A2,$D$2:$E$6,2,0)),"Customer Part
missing",IF(VLOOKUP($A2,$D$2:$E$6,2,0)<$B2,"Quant ities differ",""))

In column G: checks "Customer Part" vs "My Part"- if matched, checks
quantities


=IF(ISNA(VLOOKUP($D2,$A$2:$B$6,2,0)),"My Part
missing",IF(VLOOKUP($D2,$A$2:$B$6,2,0)<$E2,"Quant ities differ",""))

HTH

"Ben" wrote:

Now that I have that figured out, the last thing I want to do is this.
One set of columns contains the information for the customer and the
other set of column contains my information. I want to then compare the
Part Number and Qty that I have versus what the customer has. If there
is a Part # that the customer has that I don't have (or vice versa), I
want the formula to tell me. My information is as follows:

My Info
A B
Part No Qty
102634 2
102635 8
102637 16
103319 3
103320 4
.. .
.. .
.. .


Customer Info
D E
Part No Qty
102634 1
102635 1
102637 16
103319 1
103320 4
.. .
.. .
.. .



Carim wrote:
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