remove records when fields match in 2 worksheets??
 

Hi All,

I'm hoping this is and easy one.

I have 2 worksheets (sheet1 and sheet2)and want to create a 3rd (sheet
3) based on some filtering using the sheet 1 and sheet2. I want to
remove all the records in sheet 1 where the email field matches and
email field somewhere in sheet2. If I can just remove them in sheet 1
then I don't need to create sheet3 at all. Can anyone advise how to do
this?
TIA,

Case

remove records when fields match in 2 worksheets??
 

Sheet 1:
Create a helper column (in column B)
In B2: =COUNTIF(Sheet2!$A$2:$A$100,Sheet1!A2)0

Data Filter Auto Filter filter all the TRUE Highlight rows numbers
on the far left Edit Delete Rows


" wrote:

Hi All,

I'm hoping this is and easy one.

I have 2 worksheets (sheet1 and sheet2)and want to create a 3rd (sheet
3) based on some filtering using the sheet 1 and sheet2. I want to
remove all the records in sheet 1 where the email field matches and
email field somewhere in sheet2. If I can just remove them in sheet 1
then I don't need to create sheet3 at all. Can anyone advise how to do
this?
TIA,

Case

remove records when fields match in 2 worksheets??
 

Thanks for your quick reply.
When I type in the formula in AP2:
=COUNTIF(Sheet2!$D$2:$D$502,Sheet1!$R$2)0

it is replaced with FALSE and idea why?

FYI,
Sheet 2 D2 thru D502 contains all the emails (records) I want removed
from Sheet 1. In Sheet 1 column R contains all the emails.

Hope all the makes sense.

Thanks again,

Case

On May 8, 7:07 pm, Teethless mama
wrote:
Sheet 1:
Create a helper column (in column B)
In B2: =COUNTIF(Sheet2!$A$2:$A$100,Sheet1!A2)0

Data Filter Auto Filter filter all the TRUE Highlight rows numbers
on the far left Edit Delete Rows

" wrote:
Hi All,

I'm hoping this is and easy one.

I have 2 worksheets (sheet1 and sheet2)and want to create a 3rd (sheet
3) based on some filtering using the sheet 1 and sheet2. I want to
remove all the records in sheet 1 where the email field matches and
email field somewhere in sheet2. If I can just remove them in sheet 1
then I don't need to create sheet3 at all. Can anyone advise how to do
this?
TIA,

Case

remove records when fields match in 2 worksheets??
 

When you get FALSE it means you don't have a duplicated that email in Sheet 2
Copy your AP2 all the way down you will see some of the result are TRUE if
you think you might have some duplicated

" wrote:

Thanks for your quick reply.
When I type in the formula in AP2:
=COUNTIF(Sheet2!$D$2:$D$502,Sheet1!$R$2)0

it is replaced with FALSE and idea why?

FYI,
Sheet 2 D2 thru D502 contains all the emails (records) I want removed
from Sheet 1. In Sheet 1 column R contains all the emails.

Hope all the makes sense.

Thanks again,

Case

On May 8, 7:07 pm, Teethless mama
wrote:
Sheet 1:
Create a helper column (in column B)
In B2: =COUNTIF(Sheet2!$A$2:$A$100,Sheet1!A2)0

Data Filter Auto Filter filter all the TRUE Highlight rows numbers
on the far left Edit Delete Rows

" wrote:
Hi All,

I'm hoping this is and easy one.

I have 2 worksheets (sheet1 and sheet2)and want to create a 3rd (sheet
3) based on some filtering using the sheet 1 and sheet2. I want to
remove all the records in sheet 1 where the email field matches and
email field somewhere in sheet2. If I can just remove them in sheet 1
then I don't need to create sheet3 at all. Can anyone advise how to do
this?
TIA,

Case

remove records when fields match in 2 worksheets??
 

Dear Teethless,
Yes, I thought as much but when copying down, how do I get the $R$2 to
automatically change to $R$3, $R$4, etc (to $R$11173). i am a novice
with excel but my boss gave me this task and needs it done by end of
day today [ugh!]. I do appreciate all you help!!!
I know there are dups cause I have manually found some of them and
purposely left them in the sheet for checking my function.

=COUNTIF(Sheet2!$D$2:$D$502,Sheet1!$R$2)0

Case

On May 8, 8:02 pm, Teethless mama
wrote:
When you get FALSE it means you don't have a duplicated that email in Sheet 2
Copy your AP2 all the way down you will see some of the result are TRUE if
you think you might have some duplicated

remove records when fields match in 2 worksheets??
 

Replace $R$2 with $R2 should do the trick

=COUNTIF(Sheet2!$D$2:$D$502,Sheet1!$R2)0


" wrote:

Dear Teethless,
Yes, I thought as much but when copying down, how do I get the $R$2 to
automatically change to $R$3, $R$4, etc (to $R$11173). i am a novice
with excel but my boss gave me this task and needs it done by end of
day today [ugh!]. I do appreciate all you help!!!
I know there are dups cause I have manually found some of them and
purposely left them in the sheet for checking my function.

=COUNTIF(Sheet2!$D$2:$D$502,Sheet1!$R$2)0

Case

On May 8, 8:02 pm, Teethless mama
wrote:
When you get FALSE it means you don't have a duplicated that email in Sheet 2
Copy your AP2 all the way down you will see some of the result are TRUE if
you think you might have some duplicated

remove records when fields match in 2 worksheets??
 

YEEHAW!! That did it!! You saved me!!!!!! THANK YOU!!!

On May 9, 6:52 am, Teethless mama
wrote:
Replace $R$2 with $R2 should do the trick

=COUNTIF(Sheet2!$D$2:$D$502,Sheet1!$R2)0

" wrote:
Dear Teethless,
Yes, I thought as much but when copying down, how do I get the $R$2 to
automatically change to $R$3, $R$4, etc (to $R$11173). i am a novice
with excel but my boss gave me this task and needs it done by end of
day today [ugh!]. I do appreciate all you help!!!
I know there are dups cause I have manually found some of them and
purposely left them in the sheet for checking my function.

=COUNTIF(Sheet2!$D$2:$D$502,Sheet1!$R$2)0

Case

On May 8, 8:02 pm, Teethless mama
wrote:
When you get FALSE it means you don't have a duplicated that email in Sheet 2
Copy your AP2 all the way down you will see some of the result are TRUE if
you think you might have some duplicated