compare 2 list and find missing entry

[bqheng]

Hi,

I used a dirlist.txt command to extract a of files i have in a folder and imported them into excel. I did the same thing and extracted a list of files that I have from a thumbdrive. What I hope to achieve is to compare these 2 list and find missing files that are not in my thumbdrive. Is there anyway to do this?

I attached the workbook for reference

[david000]

This will make a list of what's in Column A and not in Column B. If you run this on your attachment it pulls three files that aren't in Column B and places them in Column C. If you go to conditional formatting and choose to highlight duplicates you can visually see which files are not in both lists easily.

In order to get two distinct lists that contain what's not in Column B and what's not in Column A you'd have to run this twice with the Columns reversed while keeping in mind the code is re-witting over whats in C from the first run. So, copy the first run over to another Column.





Sub In_A_Not_B()
Dim a 'is rng1 values
Dim i As Long 'is 1 to Ubound(a,1)
Dim j As Long 'is the count of the num of errors
Dim w() 'a 2D array
Dim b 'is rng2 values
Dim x 'variable for the match function
Dim Rng1 As Range
Dim Rng2 As Range
Dim r As Long ' is the variable for the max function

Set Rng1 = Range("a1", Range("a" & Rows.Count).End(xlUp))
Set Rng2 = Range("b1", Range("b" & Rows.Count).End(xlUp))
a = Rng1.Value
b = Rng2.Value
r = Application.Max(UBound(a, 1), UBound(b, 1))

ReDim w(1 To r, 1 To 1)

For i = 1 To UBound(a, 1)
If Not IsEmpty(a(i, 1)) Then
x = Application.Match(a(i, 1), b, 0)
If IsError(x) Then
j = j + 1
w(j, 1) = a(i, 1)
End If
End If
Next
If j > 0 Then Range("c1").Resize(j).Value = w
End Sub

[snb]

In Cell C1:

=COUNTIF($A$1:$A$900;$B1)