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

spraff

New Member
Joined
Oct 8, 2014
Messages
18
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 :rolleyes:
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,246
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Copy the formula in C2 down after you adjust the A-range to include all data cells in col A.
Excel Workbook
ABC
1List AList BMissing in Col A?
2xx 
3yy
4zz
5aYes
6bYes
7cYes
Sheet2
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,450
Office Version
  1. 2013
Platform
  1. Windows
Using code:
Search from the other list.
Code:
'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

New Member
Joined
Oct 8, 2014
Messages
18
Using code:
Search from the other list.
Code:
'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

Well-known Member
Joined
Feb 7, 2012
Messages
12,450
Office Version
  1. 2013
Platform
  1. Windows
Assuming '2014 Q3'!D:D is the short list and you want to know which in 'Report Stats'!A:A does not match.
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,550
Messages
5,529,472
Members
409,884
Latest member
Msinmath
Top