Remove row based on contents

[uncleslinky]

Hi there

I have a list of account numbers in column A and values in column B. There are two entries for each account number.

Eg,

Row 1 = Account1
Row 2 = Account1
Row 3 = Account2
Row 4 = Account2

I need to delete the entire row for the 2nd entry of each Account number.
I have attached a sheet to demonstrate.

In the example below it's by coincidence that the 2nd entry for each account is "0". This will not always be the case.

Any help would be great
Thanks

[rwgrietveld]

Make headers in col A,B,C
Put this in C2: =COUNTIF($A$2:A2,"="&A2)=1, drag down
Filter col C on FALSE
Select visible cells only
Press Del
Sort on column A

[uncleslinky]

Hi there

Thanks for reply
Is there a way in which I could put this into a macro. The problem I would have is that the length of the data is not always the same.... thanks

[rwgrietveld]

Funny, just discussed that in another link. This is what JonvdHeyden made

Please Login or Register  to view this content.

[uncleslinky]

Hi again, thanks for that!

[uncleslinky]

Hi again,

Hope you don't mind but I have another question. At the moment the macro/forumla is returning the first entry for each account. Is there a way in which it can be changed to return the last entry. Eg,

A B
Account1 500
Account1 200
Account1 50

Then only the "50" row would be remain and the others would get deleted

Thanks

[uncleslinky]

edit (posted by mistake, sorry)

[rwgrietveld]

This is simply adjusted by changing this line

Please Login or Register  to view this content.

into

Please Login or Register  to view this content.