make a list from another list ignoring duplicate entries from orig

[Kroka]

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

 

[Bob Phillips]

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)