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
(e-mail address removed) Bob (e-mail address removed) Sue
(e-mail address removed) Jon (e-mail address removed) Bob
(e-mail address removed) Gary (e-mail address removed) 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
(e-mail address removed) Bob (e-mail address removed) Sue
(e-mail address removed) Jon (e-mail address removed) Bob
(e-mail address removed) Gary (e-mail address removed) Gary

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

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...

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

(e-mail address removed) Bob (e-mail address removed) Sue

(e-mail address removed) Jon (e-mail address removed) Bob

(e-mail address removed) Gary (e-mail address removed) 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!
 
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

(e-mail address removed) Bob (e-mail address removed) Sue

(e-mail address removed) Jon (e-mail address removed) Bob

(e-mail address removed) Gary (e-mail address removed) 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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top