0

I have two excel worksheets that I need to find names that do not appear on one to the other. They are both very similar, however because of the added names a quick If true/false function will not work because of the shifted cells and formating. An example is

Is there any simple way to look up the name in Work Sheet 1 in Column B and cross reference it with Work Sheet 2/Column B? If it is there do nothing, however if it is not there, highlight it or mark is somehow?

EDIT: If it makes it any easier, I copied and pasted both of the columns next to each other in a new worksheet. I only need the names of the ones do not appear in both columns and nothing else.

Improve this question

1 Answer 1

Reset to default
2

You could use a VLookup formula to do this.
Basically since you have the two columns next to each other, use if(Vlookup(B1,A:A, 1,false)=B1, "Present","Not Present") in the next empty column and copy down until all rows have one. Then you can filter, sort or set conditional formatting based on the results in this column.

Improve this answer
3
  • The only reason this doesn't work (I did try a VLOOKUP before I posted) is because the two forms are different, but very similar. When someone gets added, it shifts some names down and the like. So, comparing them side by side in theory works, but because of the shift sometimes the name is a row down on Column B than it is in Column A, it would result in a FALSE, however it is actually there.
    – AgainstClint
    Oct 26, 2010 at 18:06
  • 1
    The only reason I could think of this not working is the Range reference not set correctly. One of the reasons I used A:A to get the whole column. Not something like A1:A200 which would only include the first 200 rows. The other thing you might have to do is refresh the formula by recopying it down the column if for some reason references were getting broken/corrupted. A simple macro could be created that could generate and populate a column when needed for this type of comparison
    – KMessenger
    Oct 26, 2010 at 18:08
  • 1
    AgainstClint's solution should work, no matter if the names have shifted. The Vlookup is against all of column A. I'd do a CountIf myself, e.g., =Countif(A:A,B1) and copy it down as far as the names in Column B.
    – Doug Glancy
    Oct 27, 2010 at 2:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .