Removing duplicate numbers

[dp_fulcher]

Hi there,

I was wondering if anybody could offer me advice on removing duplicat
numbers in a workbook.

For example, column A has 5 rows of item "D111", with column
containing 5 rows of the same cost "$5".

What I would like to do is for each item (such as "D111") with a coun
greater than 1 return only one row of the cost (such as "$5" in th
example above), instead of 5 rows of the cost.

Otherwise, using the example above, when I go to sum the cost for ite
"D111", it would result in "$25", rather than "$5".

Any suggestions

 

[Biff]

Hi dp!

Do you want to keep all 5 rows of D111 or do you want just
a single entry of D111 and $5?

If you'd like just a single entry for each use an advanced
filter for unique records only and copy to new location.
Then you can delete the original list.

Biff

 

[Frank Kabel]

Hi
if you want to keep all 5 items you may use a helper column. e.g.
column A+B are your data columns then enter the following formula in
C1:
=IF(COUNTIF($A$1:$A1,A1)=1,1,0)
and copy down for all rows.

Now use the following formula for summing :
=SUMPRODUCT(--(A1:A100="D11"),--(C1:C100=1),B1:B100)