compare 2 sets of columns. If both are equal, highlight

[buzz71023]

I have 2 different sets of data in a total of 4 columnsColumns A & B are a set and columnsE & F are a set

I need to compare these two columns for duplicatevalues/entries.


Column A is a Serial Number and column B is quantity from adatabase
Column E is also a Serial Number and F is from a differentdatabase

So if the entries column A AND B are equal to any entry in EAND F then to highlight in some fashion.

This will be used to find what entries are in one databasebut not the other.

I am open to any suggestions, VBA or formula/function.


Thank you in advance.

 

[Fluff]

Select A2:B last row & use this formula in conditional formattin
=ISNUMBER(MATCH($A2&"|"&$B2,INDEX($E$2:$E$100&"|"&$F$2:$F$100,0),0))
Change the values in red to match the end of your data

 

[Gerald Higgins]

So this is a form of lookup situation.

Try something like

=IF(ISNA(VLOOKUP(A1,E$1:F$3,1,FALSE)),"NO MATCH",IF(B1=VLOOKUP(A1,E$1:F$3,2,FALSE),"MATCH","NO MATCH"))

Change the row numbers to match your actual situation.

 

[buzz71023]

Fluff,
It highlighted some of the cells that matched but not all the matches. Looking through really fast I see 15-20 entries that are the same but are not highlighted.

 

[buzz71023]

Gerald,
This only worked with matches that were on the same row.

 

[Fluff]

Make sure you have the $ signs in the correct place, other wise it may not find all matches.
Also make sure that all numbers are numbers rather than text & none of the cells have leading/trailing spaces.

 

[Gerald Higgins]

Gerald,
This only worked with matches that were on the same row.

Not for me it doesn't.

It checks each value in column A, to see if it is anywhere in column E, and also checks the corresponding value in column B, to see if it is on the corresponding row in column F.

For example with this data . . . .

Col A......B............E.....F
A...........1............C.....3
B...........2............B.....5
C...........3.....................

it returns "MATCH" for the 3rd entry in col A, and no others.