find missing numbers

[inosent]

i have two columns of numbers that represent file numbers

one column is (eg)


2
3
5
6
7
9

the other is

3
6
7

the second column is 'missing'

2
5
9

how do i look at the long column, compare it to the short column, and produce the list of missing numbers?

 

[hiker95]

inosent,


Sample raw data before the macro:

Excel Workbook
	A	B	C
1	2	3
2	3	6
3	5	7
4	6
5	7
6	9
7
Sheet1

After the macro:

Excel Workbook
	A	B	C
1	2	3	2
2	3	6	5
3	5	7	9
4	6
5	7
6	9
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub FindMissing()
' hiker95, 06/23/2011
' http://www.mrexcel.com/forum/showthread.php?t=559503
Dim c As Range, FR As Long, NR As Long
Columns(3).ClearContents
NR = 0
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  FR = 0
  On Error Resume Next
  FR = Application.Match(c, Columns(2), 0)
  On Error GoTo 0
  If FR = 0 Then
    NR = NR + 1
    Cells(NR, 3) = c
  End If
Next c
End Sub

Then run the FindMissing macro.

 

[inosent]

thank you. worked perfectly!

 

[hiker95]

inosent,

You are very welcome.

Glad I could help.

Thanks for the feedback.

Come back anytime.