Excel Formulas

Compare and highlight differences in Excel columns

Let’s say you have List A and List B, both starting in A2 and B2

In C2, enter this:

=IF(COUNTIF($B$2:$B$14, A2)=0, A2, "")

Drag-copy that down column C as far as needed.

If it appears in List A, but not in List B, it shows in Col C.

A Ridiculously easy and fun way to compare 2 lists

  1. Select cells in both lists (select first list, then hold CTRL key and then select the second)
  2. Go to Conditional Formatting > Highlight Cells Rules > Duplicate Values
  3. Press ok.
  4. There is nothing do here. Go out and play!

Excel how to find values in 1 column exist in the range of values in another

=COUNTIF($B$2:$B$5,A2)>0

http://stackoverflow.com/questions/18489148/excel-how-to-find-values-in-1-column-exist-in-the-range-of-values-in-another

Compare each cell from column A with each cell from column B and column C

=OR( IF(ISNA(VLOOKUP(A1,B:B,2,0)),FALSE,TRUE),IF(ISNA(VLOOKUP(A1,C:C,3,0)),FALSE,TRUE))

Count unique values in a column in Excel

=SUM(IF(FREQUENCY(MATCH(C12:C26,C12:C26,0),MATCH(C12:C26,C12:C26,0))>0,1))

Used as part of conditional formatting.

IF F2 is (<,>,=) H2 then colour cell as defined in conditional formatting.

=IF(F2>H2,TRUE,FALSE)
=IF(F2<H2,TRUE,FALSE)
=IF(F2=H2,TRUE,FALSE)

Finding missing values

Look at cell A2 and compare it with sheet Week_4 cells A2-H25,000.  If A2 exists enter the value of column 4 into this cell.  If not enter “Nay There”.

=IFERROR(VLOOKUP($A2,Week_4!$A$2:$H$25000,4,FALSE),"Nay There")