Find Mismatched Data

J

jade

Hi,

I'm hoping someone can help I have 4 columns of data as so:

A B C D
email address Instructor Name email address
[email protected] Bob [email protected] Sue
[email protected] Jon [email protected] Bob
[email protected] Gary [email protected] Gary

so in the example above line 3 is the only true match lines one and 2 not.

There are 800 lines of data. Columns A&B is the correct "partner" data, columns C&D I need to find which ones from these columns do not match the pairing in A&B.

I would like to either highlight them in column c&D or in column e show 'false'.

Not sure if there's a vb code or a lookup or if...

Thank you for your help!
 
C

Claus Busch

Hi Jade,

Am Thu, 28 Nov 2013 07:10:04 -0800 (PST) schrieb jade:
A B C D
email address Instructor Name email address
[email protected] Bob [email protected] Sue
[email protected] Jon [email protected] Bob
[email protected] Gary [email protected] Gary

so in the example above line 3 is the only true match lines one and 2 not.
Click to expand...

line 3 does not match. The address in A is yahoo and the address in C is
gmail.

Select C2:D last cell => Conditional formatting => Formula to
determinate.... => =$A2&$B2<>$C2&$D2


Regards
Claus B.
 
J

jade

Thanks Claus,

Sorry one thing I didn't mention, the line items do not match column A&B have 670 fields and column C&D have 630...
 
C

Claus Busch

Hi,

Am Thu, 28 Nov 2013 08:04:56 -0800 (PST) schrieb jade:
Sorry one thing I didn't mention, the line items do not match column A&B have 670 fields and column C&D have 630...
Click to expand...

in E2:
=IF(A2="","",IF(ISERROR(MATCH(A2&B2,$C$2:$C$630&$D$2:$D$630,0)),"no match","match"))
array-enter the formula with CRTL+Shift+Enter and copy down


Regards
Claus B.
 
J

jade

Hi,



I'm hoping someone can help I have 4 columns of data as so:



A B C D

email address Instructor Name email address

[email protected] Bob [email protected] Sue

[email protected] Jon [email protected] Bob

[email protected] Gary [email protected] Gary



so in the example above line 3 is the only true match lines one and 2 not.



There are 800 lines of data. Columns A&B is the correct "partner" data, columns C&D I need to find which ones from these columns do not match the pairing in A&B.



I would like to either highlight them in column c&D or in column e show 'false'.



Not sure if there's a vb code or a lookup or if...



Thank you for your help!
Click to expand...
 
J

jade

Hi,



I'm hoping someone can help I have 4 columns of data as so:



A B C D

email address Instructor Name email address

[email protected] Bob [email protected] Sue

[email protected] Jon [email protected] Bob

[email protected] Gary [email protected] Gary



so in the example above line 3 is the only true match lines one and 2 not.



There are 800 lines of data. Columns A&B is the correct "partner" data, columns C&D I need to find which ones from these columns do not match the pairing in A&B.



I would like to either highlight them in column c&D or in column e show 'false'.



Not sure if there's a vb code or a lookup or if...



Thank you for your help!
Click to expand...
 
Top