Removing duplicate entries?

[Aphex Kid]

I have about 5000 rows (a1:g5000) of information, but some of the rows have duplicate information. How do I set up my document so that all duplicate entries in the B column remove the entire corresponding row?

(ie. if B501, b502 and b503 were the same value as b500, it deletes (a501:g503)

Thanks..

[Mark W.]

Are you only interested in duplicate values on adjacent rows? If so, use an Advanced AutoFilter with a computed criteria of =B2=B1, select the displayed data rows and delete them.

[ This Message was edited by: Mark W. on 2002-02-20 08:23 ]

[Aphex Kid]

Yes, because it's sorted by value in the B column...

There can be anywhere from 1-10 occurences of the same value, but because they're always sorted, they're always adjacent.

[Mark W.]

See my edited response above.

[Aphex Kid]

If I put in =b2=b1 in the criteria range, it tells me the reference is not valid. Am I doing something wrong?

[Aphex Kid]

Never mind ... got it sorted. Thanks!

[Mark W.]

On 2002-02-20 08:31, Aphex Kid wrote:
If I put in =b2=b1 in the criteria range, it tells me the reference is not valid. Am I doing something wrong?

=B2=B1 isn't entered into the Advanced AutoFilter's criteria field -- the cell range that contains this formula is. If you're not familiar with computed criteria they must as a mininum consist of 2 rows -- the first row being blank. So, if you entered =B2=B1 into E2 you're criteria range should be E1:E2.