Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and delete
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and delete
Debra Dalgleish shows how you can get rid of duplicate records he
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)),"Uniqu e","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 On Mar 5, 1:07*am, "Karen" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and delete
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 "Pete_UK" wrote in message ... Debra Dalgleish shows how you can get rid of duplicate records he 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)),"Uniqu e","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 On Mar 5, 1:07 am, "Karen" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Delete Row | Excel Worksheet Functions | |||
Find and delete | Excel Worksheet Functions | |||
Find and delete! | Excel Discussion (Misc queries) | |||
find row and delete | Excel Discussion (Misc queries) | |||
Find delete | Excel Discussion (Misc queries) |