Comparing data within 2 lists

[Jenn L.]

I have been asked to compare lists from my 2 main systems. They believe that
the information of them is not consistent. How would I compare the 2 lists in
Excel since there are over 30 thousand records on them. Information:

List 1 List 2
Building no. Color Building no. Color

Building 1 Blue Building 2 Red
Building 2 Red Building 4 Yellow
Building 3 Yellow Building 5 Orange
Building 4 Green Building 1 Green
Building 5 Orange Building 3 Yellow

THANKS!

 

[Trevor Shuttleworth]

I think the easiest way would be a VLOOKUP from one list to the other.
Assuming that the building number is in column A and the colour is in column
B in both worksheets, you could add a column C with the VLOOKUP

=VLOOKUP(A1,Sheet2!$A:$B,2,FALSE) would pick up the colour from the second
sheet. You could then add a further column D to highlight the differences.

=IF(B1=C1,"","inconsistent")

Drag C1 and D1 down to the end of your data.

Or you could use a similar formula in Conditional Formatting to highlight
the whole row.

Regards

Trevor