• I've found a way to do this in Excel 2007 - hope that you're using this latest version.. =) To find duplicates for these columns in the seperate sheets, you first need to copy/paste one of the columns into the other sheet so they are side by side. Then just highlight both of these columns. With them both highlighted, from the HOME tab, click the CONDITIONAL FORMATTING option from the STYLES tab. Under the HIGHLIGHT CELLS RULES section, select the DUPLICATE VALUES option at the bottom of the menu. A window will pop up to select how you want the differences highlighted. Click OK and all of the duplicate values will be highlighted for you.
  • Use a vlookup formula in column b of sheet1. If the value is on sheet 2 it will show that value. If it is not it will return the value n/a. =VLOOKUP(A2,Sheet2!A:A,1,FALSE)
  • The way I read your question you want to check whether a value in Column A of Sheet 2 exists ANYWHERE in Column A of Sheet 1, rather than in the corresponding cell. If I'm wrong, then Ali has given you the formula you want. Put this formula into cell B2 (B1 if your list has no header row) and autofill it as far down the column as you need to go. =IF(ISNA(VLOOKUP(A2,Sheet1!A:A,1,FALSE)),"","Exists") The word 'Exists' will appear in column B next to any cell that contains a value that occurs anywhere in Column A of Sheet 1.

Copyright 2020, Wired Ivy, LLC

Answerbag | Terms of Service | Privacy Policy