Excel - finding unmatched data

[rudeolf]

I have 2 tabs of data with a unique identifier. The identifier is not in any particular order. I need my vlookup / index / match to show me all the identifiers that are not present in tab 2.


Reason: I am working where the systems they used failed a data transfer. I have to see what data there was compared to what data is currently on the system. Any data that is missing, i will need to add to the new system.


Example;
Tab1 Column A: 123456, 654321, 789456, 456789.

Tab2 Column B: 654321, 123456, 456789.


In Tab 3, I want excel to tell me that 789456 is not present in Tab 2. As you can see in the above example, the unique identifier could be in any order, therefore i cannot put both columns side by side and ask to do a match between the 2 - i need it to look through the whole column.


All the tutorials i have seen assume that column A matches in order of column B


I have 70,000 rows to go through.


Any help would be appreciated.


Thanks in advance.

 

[AhoyNC]

Maybe something like this.
Formula in A1 is an array formula and must be entered with CTRL-SHIFT-ENTER (command return on MAC). Copy down as needed.

Excel Workbook
	A	B
1	789456	# unmatched records
2	25	2
3	*	*
Sheet3

Excel Workbook
	B
1	654321
2	123456
3	456789
Sheet2

Excel Workbook
	A
1	123456
2	654321
3	789456
4	456789
5	*
6	*
7	25
8	*
9	*
Sheet1

 

[Veritan]

If you'd like to take a VBA approach, you could use this code. Insert it into a module and run it. Please note that I put the missing ID's in column A in the third tab starting at A2, while A1 had the header "Missing ID's".

Option Explicit
Sub UniqueIDs()
    Dim MissingID() As Variant, i&, r As Range, UniqueID
    
    With Sheets("Sheet1")
    For Each r In .Range("A1:A" & .Range("A1").End(xlDown).Row)
        With Sheets("Sheet2")
        Set UniqueID = .Range("B1:B" & .Range("B1").End(xlDown).Row).Find(What:=r, LookAt:=xlWhole, SearchDirection:=xlNext)
        End With
        If UniqueID Is Nothing Then
            i = i + 1
            ReDim Preserve MissingID(1 To i)
            MissingID(i) = r
        End If
    Next r
    End With
    Sheets("Sheet3").Range("A2:A" & i + 1) = Application.Transpose(MissingID)
End Sub