cj21 Wrote:
> Sorry about the last post, ignore it.
>
> My problem is this:
>
> tariff Product code
> 25 0118
> 25 0118
> 45 0320
> 45 0320
> 10 0460
> 45 0460
> 25 0470
> 45 0470
> 45 0481
> 50 0481
> 45 0484
> 45 0484
>
>
> I have the above table, but product codes are duplicated. I want to ge
> rid of the duplicates, even if the tariff is different. Any ideas, ther
> are about 2000 rows .
>
> Chris
Here is how I get rid of my duplicates ... I use formulas to do i
because the "Data/Filter/Advanced Filter" procedure does not work fo
me all the time.
In C1, enter the formula
=IF(COUNTIF($B$1:B1,B1)=1,B1,\"\"
and copy down until your range requirements are met (C2000). Thi
formula will eliminate all the duplicates but will leave blanks in you
Column C.
In Cell D1, enter the formula
=IF(ROW()-ROW($D$1:$D$2000)+1>ROWS($C$1:$C$2000)-COUNTBLANK($C$1:$C$2000),\"\",INDIRECT(ADDRESS(SMALL((IF($C$1:$C$2000<>\"\",ROW($C$1:$C$2000),ROW()+ROWS($C$1:$C$2000))),ROW()-ROW($D$1:$D$2000)+1),COLUMN($C$1:$C$2000),4))
and copy down until D2000. This formula is an array formula so instea
of just using the "Enter" key, use the "Ctrl" + "Shift" + "Enter" key
to confirm your formula.
Your entries in Column D will all be unique and all the blank space
that were in Column C have been eliminated.
There may be a more elegant solution out there but meantime, this wil
work for you.
Regards
--
BenjieLo
-----------------------------------------------------------------------
BenjieLop's Profile:
http://www.excelforum.com/member.php...fo&userid=1101
View this thread:
http://www.excelforum.com/showthread.php?threadid=39687
Similar Threads
