Lookup and return any missing numbers if applicable

[Chel]

Is it possible to write a formula that will autopopulate a column with any
missing numbers. I believe it should be something really simple, but I can't
get it to work.

Example: Sheet x, column A - contains employee numbers
Sheet y, column A - contains employee numbers

I want to update Sheet x, column A with any employee numbers that are on
Sheet y and not sheet x.

Thank you in advance.

 

[Gary''s Student]

Try running this simple macro:

Sub GetUm()
Set x = Sheets("x")
Set y = Sheets("y")
xn = x.Cells(Rows.Count, "A").End(xlUp).Row
yn = y.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To yn
v = y.Cells(i, "A").Value
Set LookRange = x.Range("A1:A" & xn)
k = Application.WorksheetFunction.CountIf(LookRange, v)
If k = 0 Then
xn = xn + 1
x.Cells(xn, "A").Value = v
End If
Next
End Sub

 

[Gary''s Student]

Try running this simple macro:

Sub GetUm()
Set x = Sheets("x")
Set y = Sheets("y")
xn = x.Cells(Rows.Count, "A").End(xlUp).Row
yn = y.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To yn
v = y.Cells(i, "A").Value
Set LookRange = x.Range("A1:A" & xn)
k = Application.WorksheetFunction.CountIf(LookRange, v)
If k = 0 Then
xn = xn + 1
x.Cells(xn, "A").Value = v
End If
Next
End Sub

 

[bosco_yip]

A formula way, try :

1] Assume your datas located at "Sheet x, A1:A20" and "Sheet y, A1:A20"

2] Sheet x, A21, enter the array formula and filled down :

{=INDEX('Sheet y'!A:A,SMALL(IF(ISERROR(MATCH('Sheet
y'!A$1:A$20,$A$1:$A$20,0)),ROW($1:$20),65000),ROW(1:1)))&""}


Regards
Bosco

 

[bosco_yip]

A formula way, try :

1] Assume your datas located at "Sheet x, A1:A20" and "Sheet y, A1:A20"

2] Sheet x, A21, enter the array formula and filled down :

{=INDEX('Sheet y'!A:A,SMALL(IF(ISERROR(MATCH('Sheet
y'!A$1:A$20,$A$1:$A$20,0)),ROW($1:$20),65000),ROW(1:1)))&""}


Regards
Bosco