How to match values of one sheet on another sheet

1
votes

I have two sheets sheet1, sheet2. Sheet2 having the with records and duplicate number. like
sheet1 

1----a------ 100  
1----a-------200  
2----b-------200  
2----b-------200  
2----b-------200  
3----c-------200  
3----c-------200

sheet2 having the data like 

1---a-----300  
2---b-----600  
4---d-----200

How to match these two sheets and highlight the values in sheet1 where the values are unmatched.

thanks in advance

1
excelvba
You show a one digit number, a letter and a three digit on each line. Are they stored in one cell or split over 3 cells? Then which data do you want to use to do the comparison? In either case, I think that a simple =vlookup(A1, sheet2 A1:A7, 1,0) would do the trick or is there something I've missed? – Robert Ilbrink
Can you explain what output you need? Are you saying simply highlight the values in sheet1 that do not appear in sheet 2? e.g. highlight all 'a' and 'b' values in sheet 1 as they do not appear in sheet 2? – Alex P
Sorry for the confusion, – user1049518
I need to compare on first column i.e., 1,2,4 values from sheet2. 1 and 2 will be matched 3 is not matching with sheet2 values. then the 3 will be highlited with yellow. – user1049518
Thanks Remnant for your early response. I need to highlite the 3-c-200 in sheet1. coz it do not appear in sheet2. I require to do it through VBA. – user1049518

1 Answers

1
votes

The best way to handle this is to use Conditional Formating.

You have to use a Named Range to handle multi-sheet CF, so on Sheet2, select the first column of the range you want to check (A1 to A3 in your example) and on the top left of the screen, just below the ribbon, type a name, something like MySource or whatever.

Select the whole range where you want to apply the formatting (Sheet1, cells A1 to C7 in your example) then, go to the ribbon (first tab): Conditional Formating > New rule....

Select Use a formula.

Add this kind of formula to the field:

=ISNA(VLOOKUP($A1, MySource, 1,0))

Note: you can remove the ISNA to select the cells that match.