Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I delete duplicate cells?
Is there a way to delete cells if they are already listed on a separate
spreadsheet? For example, I have two list of names (list A & B) List A List B dog cat cat goose monkey kangaroo goose Since cells containing cat and goose are in list B, is there a function that will delete those cells from list A? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I delete duplicate cells?
One formulas play to get there ..
Assume List A is in Sheet1 col A, List B is in Sheet2 col A, data assumed from row2 down In a new Sheet3, Put in A2: =IF(ROW(A1)COUNT(B:B),"",INDEX(Sheet1!A:A,MATCH(S MALL(B:B,ROW(A1)),B:B,0))) Put in B2: =IF(Sheet1!A2="","",IF(ISNUMBER(MATCH(Sheet1!A2,Sh eet2!A:A,0)),"",ROW())) (Leave B1 empty) Select A2:B2, copy down to say, B100, to cover the max expected extent of data in Sheet1's col A Col A will return the required results neatly bunched at the top, ie items in Sheet1's col A not found in Sheet2's col A. (Hide away the criteria col B, if desired) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AYANG" wrote: Is there a way to delete cells if they are already listed on a separate spreadsheet? For example, I have two list of names (list A & B) List A List B dog cat cat goose monkey kangaroo goose Since cells containing cat and goose are in list B, is there a function that will delete those cells from list A? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete row where there is duplicate data in Column E | New Users to Excel | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Delete text in cells but keep formula | Excel Worksheet Functions | |||
Preventing Duplicate Cells | Excel Discussion (Misc queries) | |||
Delete contents of unprotected cells in workbook | Excel Worksheet Functions |