Search worksheets for items in a column and report anything missing

[AndyMb]

Hi all,

I am looking for a macro that will look down a list and report anything that's not on the list.

My list is on Sheet1 in column A
Starting with the value of cell A1 I want to search 3 sheets, "AttendanceA", "AttendanceB" and "Returns".
There might be multiple instances of the search string, however I'm only interested if there are no matches.
If there is No match, i'd like to paste that unmatched value to a list in a separate sheet.
I would then like to search the next item down on the list (A2) and repeat the search, again adding the item to the unmatched list if not found.
and so on until the end of the list.

Any help appreciated
regards

 

[Fluff]

How about

Sub AndyMb()
    Dim Ary As Variant, Shts As Variant
    Dim i As Long, r As Long
    
    Shts = Array("AttendanceA", "AttendanceB", "Returns")
    With CreateObject("scripting.dictionary")
        Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
        For r = 1 To UBound(Ary)
            .Item(Ary(r, 1)) = Empty
        Next r
        For i = 0 To UBound(Shts)
            Ary = Sheets(Shts(i)).Range("A1").CurrentRegion.Value2
            For r = 1 To UBound(Ary)
                If .exists(Ary(r, 1)) Then .Remove Ary(r, 1)
            Next r
        Next i
        Sheets("Sheet2").Range("A1").Resize(.Count).Value = Application.Transpose(.keys)
    End With
End Sub

This assumes all values are in col A

 

[AndyMb]

How about

Sub AndyMb()
    Dim Ary As Variant, Shts As Variant
    Dim i As Long, r As Long
   
    Shts = Array("AttendanceA", "AttendanceB", "Returns")
    With CreateObject("scripting.dictionary")
        Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
        For r = 1 To UBound(Ary)
            .Item(Ary(r, 1)) = Empty
        Next r
        For i = 0 To UBound(Shts)
            Ary = Sheets(Shts(i)).Range("A1").CurrentRegion.Value2
            For r = 1 To UBound(Ary)
                If .exists(Ary(r, 1)) Then .Remove Ary(r, 1)
            Next r
        Next i
        Sheets("Sheet2").Range("A1").Resize(.Count).Value = Application.Transpose(.keys)
    End With
End Sub

This assumes all values are in col A

Works perfectly, thank you!

 

[Fluff]

You're welcome & thanks for the feedback