Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete all rows if criteria not matched
Column C (C2:C101) in my Sheet1 contain different criteria (names or number)
and my Sheet2 is a large database. Is there a quick way to delete all rows in Sheet2 except the ones in Column C of Sheet2 that matched Column C of Sheet1? Thank you so much for any help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete all rows if criteria not matched
The quickest way is to add an auxilary column and put a 0 if the data doesn't
appear and a 1 if the data does appear. then sort on the auxilary column and delete the rows with zero. This formula will help =IF(ISNA(MATCH(A1,Sheet2!$A:$A,0)),0,1) put in row 1 and then copy down the column. Then sort and delete. i really only takes less than a minute. A1 is the data that gets looked up and $A:$A is the column where you are looking up the data. "ongcong" wrote: Column C (C2:C101) in my Sheet1 contain different criteria (names or number) and my Sheet2 is a large database. Is there a quick way to delete all rows in Sheet2 except the ones in Column C of Sheet2 that matched Column C of Sheet1? Thank you so much for any help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete all rows if criteria not matched
Thank you Joel. But how do I set up the formula correctly for up to 100
criteria (C2:C101) in Sheet1? "Joel" wrote: The quickest way is to add an auxilary column and put a 0 if the data doesn't appear and a 1 if the data does appear. then sort on the auxilary column and delete the rows with zero. This formula will help =IF(ISNA(MATCH(A1,Sheet2!$A:$A,0)),0,1) put in row 1 and then copy down the column. Then sort and delete. i really only takes less than a minute. A1 is the data that gets looked up and $A:$A is the column where you are looking up the data. "ongcong" wrote: Column C (C2:C101) in my Sheet1 contain different criteria (names or number) and my Sheet2 is a large database. Is there a quick way to delete all rows in Sheet2 except the ones in Column C of Sheet2 that matched Column C of Sheet1? Thank you so much for any help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete all rows if criteria not matched
The formula goes on sheet 2. Put this in an auxilary column (Clo X) i nrow 1
and copy down the auxilary column. Sort (or filter) on the auxilary colum and delete the rows with 0. =IF(ISNA(MATCH(C1,Sheet1!$C:$C,0)),0,1) "ongcong" wrote: Thank you Joel. But how do I set up the formula correctly for up to 100 criteria (C2:C101) in Sheet1? "Joel" wrote: The quickest way is to add an auxilary column and put a 0 if the data doesn't appear and a 1 if the data does appear. then sort on the auxilary column and delete the rows with zero. This formula will help =IF(ISNA(MATCH(A1,Sheet2!$A:$A,0)),0,1) put in row 1 and then copy down the column. Then sort and delete. i really only takes less than a minute. A1 is the data that gets looked up and $A:$A is the column where you are looking up the data. "ongcong" wrote: Column C (C2:C101) in my Sheet1 contain different criteria (names or number) and my Sheet2 is a large database. Is there a quick way to delete all rows in Sheet2 except the ones in Column C of Sheet2 that matched Column C of Sheet1? Thank you so much for any help. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete all rows if criteria not matched
It works very nicely.
Thank you so much Joel. Regards, "Joel" wrote: The formula goes on sheet 2. Put this in an auxilary column (Clo X) i nrow 1 and copy down the auxilary column. Sort (or filter) on the auxilary colum and delete the rows with 0. =IF(ISNA(MATCH(C1,Sheet1!$C:$C,0)),0,1) "ongcong" wrote: Thank you Joel. But how do I set up the formula correctly for up to 100 criteria (C2:C101) in Sheet1? "Joel" wrote: The quickest way is to add an auxilary column and put a 0 if the data doesn't appear and a 1 if the data does appear. then sort on the auxilary column and delete the rows with zero. This formula will help =IF(ISNA(MATCH(A1,Sheet2!$A:$A,0)),0,1) put in row 1 and then copy down the column. Then sort and delete. i really only takes less than a minute. A1 is the data that gets looked up and $A:$A is the column where you are looking up the data. "ongcong" wrote: Column C (C2:C101) in my Sheet1 contain different criteria (names or number) and my Sheet2 is a large database. Is there a quick way to delete all rows in Sheet2 except the ones in Column C of Sheet2 that matched Column C of Sheet1? Thank you so much for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Rows based on criteria in excel | Excel Discussion (Misc queries) | |||
Delete rows if specific criteria not met. | Excel Worksheet Functions | |||
Delete rows based on criteria | Excel Discussion (Misc queries) | |||
Delete rows based on certain criteria | Excel Discussion (Misc queries) | |||
Delete Rows where cells does not meet criteria | Excel Worksheet Functions |