Find and delete

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

Find and Delete

I have asked this before, the answer I believe unfortunately was wrong after
3 hours of work

I have 7436 Numbers in Col A some of which are duplicated
I wish to delete all the duplicates in Col A including the origanal Numbers.


I have 2300 Numbers in Col B

If a number in Col B appears in Col A I wish to delete that number in Col A

If you can help thanks

in advance

Karen
 
Debra Dalgleish shows how you can get rid of duplicate records here:

http://www.contextures.com/xladvfilter01.html

You could copy the unique records to another sheet (let's say in
column A).

Then you could use this formula in B2 of the new sheet:

=IF(ISNA(VLOOKUP(A2,Sheet1!B$1:B$2300,1,0)),"Unique","Duplicate")

and copy this down to check if any of the numbers in column B of the
first sheet appear in the list in Sheet2. Then apply autofilter to
column B, select "Duplicate" from the filter drop-down, then highlight
all the visible rows and Edit | Delete Row. Choose All from the filter
drop-down, delete column B, and you will be left with the reduced list
that you want (with the original in Sheet1 untouched).

Hope this helps.

Pete
 
I now have

Col A no duplicates
Col B no duplicates

I did apply your instructions but

worksheet test sheet 08 cell a2 is the number 2
but
worksheet test sheet 09 cell a2 is the number 2

results is b2 "Unique". This should not of happened as the number 2 appears
on both sheets 08 + 09.

Some cells are showing result duplicate

this is the formula i used

=IF(ISNA(VLOOKUP(A13,'[Sheet1]09'!A$1:A$764,1,0)),"Unique","Duplicate")

Thanks

Karen






Debra Dalgleish shows how you can get rid of duplicate records here:

http://www.contextures.com/xladvfilter01.html

You could copy the unique records to another sheet (let's say in
column A).

Then you could use this formula in B2 of the new sheet:

=IF(ISNA(VLOOKUP(A2,Sheet1!B$1:B$2300,1,0)),"Unique","Duplicate")

and copy this down to check if any of the numbers in column B of the
first sheet appear in the list in Sheet2. Then apply autofilter to
column B, select "Duplicate" from the filter drop-down, then highlight
all the visible rows and Edit | Delete Row. Choose All from the filter
drop-down, delete column B, and you will be left with the reduced list
that you want (with the original in Sheet1 untouched).

Hope this helps.

Pete
 
Back
Top