Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and return any missing numbers if applicable
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and return any missing numbers if applicable
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 - gsnu200852 "Chel" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and return any missing numbers if applicable
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 "Chel" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom format applicable to negative and positive numbers? | Excel Discussion (Misc queries) | |||
lookup range of numbers; return corresponding data for the fixed r | Excel Worksheet Functions | |||
BIFF - Help!! lookup numbers in multiple columns and return one nu | Excel Worksheet Functions | |||
LOOKUP and return the column heading for IF/THEN return for False | Excel Discussion (Misc queries) | |||
to find missing serial numbers in randomly generated numbers | Excel Worksheet Functions |