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.

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.

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.

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.

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.