How to display the cell value(s) searched for but not found in a list? (Excel)

[spraff]

List A has the following sites: x, y, z


List B has the following sites: x, y, z, a, b, c


What formula will reveal to me that sites a, b, and c are in the range I'm looking at but missing not listed in List A?

Reason: I'm have a countif formula looking for the counts of reports created for various sites - but sometimes that site list grows but hasn't quite grown on my formula

 

[JoeMo]

Copy the formula in C2 down after you adjust the A-range to include all data cells in col A.

Excel Workbook
	A	B	C
1	List A	List B	Missing in Col A?
2	x	x
3	y	y
4	z	z
5		a	Yes
6		b	Yes
7		c	Yes
Sheet2

 

[JLGWhiz]

Using code:
Search from the other list.

'psuedo code
Dim c As Range
For Each c In Range("List B")
 If Application.CountIf(Range("ListA")) = 0 Then
  MsgBox "Item " & c.Value & " not found." 'you can sent it to a range instead of msgbox.
 End If
Next

 

[spraff]

Using code:
Search from the other list.

'psuedo code
Dim c As Range
For Each c In Range("List B")
 If Application.CountIf(Range("ListA")) = 0 Then
  MsgBox "Item " & c.Value & " not found." 'you can sent it to a range instead of msgbox.
 End If
Next

So I'm pretty green when it comes to VBA... if my ranges

'2014 Q3'!D:D and 'Report Stats'!A:A and I'm looking for values such as "Beijing R/D, CHINA (BEI)", "Hong Kong, CHINA (HKG)", "Melbourne, AUSTRALIA (MLB)", etc... how would the above look?

 

[JLGWhiz]

Assuming '2014 Q3'!D:D is the short list and you want to know which in 'Report Stats'!A:A does not match.

Sub donotMatch()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, c As Range
Set sh1 = Sheets("2014 Q3")
Set sh2 = Sheets("Report Stats")
lr = sh1.Cells(Rows.Count, 4).End(xlUp).Row
    For Each c In sh1.Range("D2:D" & lr)
        If Application.CountIf(sh2.Range("A:A"), c.Value) = 0 Then
            lst = lst & c.Value & ", "
        End If
    Next
MsgBox lst
End Sub

Reverse the sheet names if it is the other way around for most and least.