Remove duplicates

[HawkStream]

So lets say I have a spread sheet like this, the | will mean the column line -

1 | 12 | bla bla bla
2 | 13 | bla bla bla
3 | 1 | bla bla bla
4 | 14 | bla bla bla
5 | 15 | bla bla bla
6 | 2 | bla bla bla

So I want a way to automatically remove the line that is 3 | 1 | bla bla bla, and 6 | 2 | bla bla bla because the 1 and the 2 in the 2nd column had already been used in the first column.

How can I do this? I could have the #s in the first column on a different SS altogether but need a way for it to go through that 2nd column and delete the entire row if the # is a duplicate from 1st column.

Please Help

[HawkStream]

Maybe this is an easier way to ask the question. I have SS that has ticket numbers in column A. I have a 2nd SS that is the same. I want to automatically remove any rows in the first spreadsheet that start with the same ticket # as in column A in the 2nd SS.

[Osvaldo Palmeiro]

Hi.

see if this code could do what you want

Please Login or Register  to view this content.

[Marcol]

Or without a loop, assuming you have headers in row 1

Please Login or Register  to view this content.

Hope this helps

Hope this helps

[broro183]

hi Marcol,

I haven't tried your code, but wouldn't it be safer if you stated that only visible cells should be deleted?
For example:

Please Login or Register  to view this content.

Notes:
1. I haven't error handling which may be needed in case there are no visible cells after the autofilter is applied.
2. If the OP has large datasets, it would be worth sorting the data by your helper column before deleting to prevent any chance of the limitations of specialcells causing issues (see http://www.rondebruin.nl/specialcells.htm).

hth
Rob

[Marcol]

@ Rob
Thought I did that by returning "" or "Delete" in the temporary helper column "D". Hence "<>"
Maybe not enough beer, the problem might be if data starts in row 1 and this is not a header row.

[broro183]

lol, it would seem that it's a case of not enough beer for me

hmmm, interesting, I've tried the code now & I didn't realise that the code would work like that (with only the visible rows being deleted, even though the entire range is named in the .delete statement), but it seems to...

Thanks, I've learnt something new today

Rob

[HawkStream]

Thanks for this.. Going to try it now.