Hi, I use office excel 2003

I have a column into which I input a series of references which can be alpha
or numeric, but never a combination of both, and represent products. The
column next to this has a series of numeric values which record units
produced and relate to the reference numbers in the previous column. Eg
columns A and B could be as follows;

A B
1 1011 15
2 xxxx 45
3 2022 360
4 1011 25

meaning that we produced 15 units of product 1011, 45 units of product xxx,
360 units of product 2022 and another 25 units of product 1011. The reference
numbers in column A could be duplicated once or more than once. I need to
have formulae in column C that show me the product references but does not
repeat any duplicates, and formulae in column D that just give me the total
units produced per product. So using the above example column C and D should
show

C D
1 1011 40
2 xxxx 45
3 2022 360

If I manually type in the product ref numbers in column C, I can get column
D to work by using the following formulae;

D1 =SUMIF(A1:A4,C1,B1:B4)
D2 =SUMIF(A1:A4,C2,B1:B4)
D3 =SUMIF(A1:A4,C3,B1:B4)
D4 =SUMIF(A1:A4,C4,B1:B4)

What I can not get to work is the formulae in column C which will populate
it with the references, but without duplicating any. In other words I need it
to check column A, and input any references it finds there into column C
unless that reference already appears in column C.

I thought I had it with the following

C1 =A1
C2 =IF(A2=C1,0,A2)
C3 =IF(AND(A3=C1,C2),0,A3)
C4 =IF(AND(A4=C1,C2,C3),0,A4)

This does work sometimes but doesnt other times, and I cant work out why.
If the references in A are ALL text it copies them all into C, but can not
recognise duplicates and so just copies A. If the references are all numbers
column C only works down to C2 and then all other cells in C are 0. If some
references are numbers and some text the results in C vary but never work all
the way down.

I have also tried replacing the formulae in column C with =IF(OR( instead of
=IF(AND but this simply replicates ALL of the data in column A including
duplicates.

I can of course just manually input the reference numbers in C myself and
ensure there are no duplicates, or allow it to copy C and the sort and delete
any duplicates, but I feel sure that there must be a way to do this
automatically, perhaps a combination of the IF(AND / IF(OR functions.

Can anyone help please?

Many thanks
John

C1: = A1
C2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$20&""),0)))

which is an array formula, so commit with Ctrl-Shift-Enter. Copy C2 down

D1: =SUMIF(A:A,C1,B:B)

and copy down

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Kroka" wrote in message
...
Hi, I use office excel 2003

I have a column into which I input a series of references which can be
alpha
or numeric, but never a combination of both, and represent products. The
column next to this has a series of numeric values which record units
produced and relate to the reference numbers in the previous column. Eg
columns A and B could be as follows;

A B
1 1011 15
2 xxxx 45
3 2022 360
4 1011 25

meaning that we produced 15 units of product 1011, 45 units of product
xxx,
360 units of product 2022 and another 25 units of product 1011. The
reference
numbers in column A could be duplicated once or more than once. I need to
have formulae in column C that show me the product references but does not
repeat any duplicates, and formulae in column D that just give me the
total
units produced per product. So using the above example column C and D
should
show

C D
1 1011 40
2 xxxx 45
3 2022 360

If I manually type in the product ref numbers in column C, I can get
column
D to work by using the following formulae;

D1 =SUMIF(A1:A4,C1,B1:B4)
D2 =SUMIF(A1:A4,C2,B1:B4)
D3 =SUMIF(A1:A4,C3,B1:B4)
D4 =SUMIF(A1:A4,C4,B1:B4)

What I can not get to work is the formulae in column C which will populate
it with the references, but without duplicating any. In other words I need
it
to check column A, and input any references it finds there into column C
unless that reference already appears in column C.

I thought I had it with the following

C1 =A1
C2 =IF(A2=C1,0,A2)
C3 =IF(AND(A3=C1,C2),0,A3)
C4 =IF(AND(A4=C1,C2,C3),0,A4)

This does work sometimes but doesn't other times, and I can't work out
why.
If the references in A are ALL text it copies them all into C, but can not
recognise duplicates and so just copies A. If the references are all
numbers
column C only works down to C2 and then all other cells in C are 0. If
some
references are numbers and some text the results in C vary but never work
all
the way down.

I have also tried replacing the formulae in column C with =IF(OR( instead
of
=IF(AND but this simply replicates ALL of the data in column A including
duplicates.

I can of course just manually input the reference numbers in C myself and
ensure there are no duplicates, or allow it to copy C and the sort and
delete
any duplicates, but I feel sure that there must be a way to do this
automatically, perhaps a combination of the IF(AND / IF(OR functions.

Can anyone help please?

Many thanks
John