Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding missing data between two worksheets
I have a master list of people and their ID number in worksheet 1. I'm trying to find the missing individuals from what I have in worksheet 2 and put them in a new worksheet 3. Please tell me the easiest way to do this... Thank you, -- KM |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding missing data between two worksheets
If you have both lists in Col A of Sheet1 and Sheet2 respectively, then in B1
of Sheet1 enter =VLOOKUP(A1,Sheet2!A:A,1,False) and copy down. Filter on Col B on #N/A... these will be the missing from Sheet2, Col A "Mossykel" wrote: I have a master list of people and their ID number in worksheet 1. I'm trying to find the missing individuals from what I have in worksheet 2 and put them in a new worksheet 3. Please tell me the easiest way to do this... Thank you, -- KM |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding missing data between two worksheets
Didn't work. Entered the exact formula and all I get in column B when
copying down is the first value in A1... -- KM "Sheeloo" wrote: If you have both lists in Col A of Sheet1 and Sheet2 respectively, then in B1 of Sheet1 enter =VLOOKUP(A1,Sheet2!A:A,1,False) and copy down. Filter on Col B on #N/A... these will be the missing from Sheet2, Col A "Mossykel" wrote: I have a master list of people and their ID number in worksheet 1. I'm trying to find the missing individuals from what I have in worksheet 2 and put them in a new worksheet 3. Please tell me the easiest way to do this... Thank you, -- KM |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding missing data between two worksheets
Did you enter this on Sheet 1?
If all cells in B are same as A then it means that all values in Sheet 1 Col A are present in Sheet 2 Col A If that is the case then try the reverse Enter in B1 of Sheet 2 and copy down =VLOOKUP(A1,Sheet1!A:A,1,False) "Mossykel" wrote: Didn't work. Entered the exact formula and all I get in column B when copying down is the first value in A1... -- KM "Sheeloo" wrote: If you have both lists in Col A of Sheet1 and Sheet2 respectively, then in B1 of Sheet1 enter =VLOOKUP(A1,Sheet2!A:A,1,False) and copy down. Filter on Col B on #N/A... these will be the missing from Sheet2, Col A "Mossykel" wrote: I have a master list of people and their ID number in worksheet 1. I'm trying to find the missing individuals from what I have in worksheet 2 and put them in a new worksheet 3. Please tell me the easiest way to do this... Thank you, -- KM |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding missing data between two worksheets
I think I figured out what the problem is... for some reason my excel
program isn't updating the formulas when I copy down. It copies the formula but also copies the same number as is in the cell above. Same thing is happening when I update numbers which are linked to a graph. The graph is not updating to represent the numbers in the cells associated. How do I fix this? -- KM "Sheeloo" wrote: Did you enter this on Sheet 1? If all cells in B are same as A then it means that all values in Sheet 1 Col A are present in Sheet 2 Col A If that is the case then try the reverse Enter in B1 of Sheet 2 and copy down =VLOOKUP(A1,Sheet1!A:A,1,False) "Mossykel" wrote: Didn't work. Entered the exact formula and all I get in column B when copying down is the first value in A1... -- KM "Sheeloo" wrote: If you have both lists in Col A of Sheet1 and Sheet2 respectively, then in B1 of Sheet1 enter =VLOOKUP(A1,Sheet2!A:A,1,False) and copy down. Filter on Col B on #N/A... these will be the missing from Sheet2, Col A "Mossykel" wrote: I have a master list of people and their ID number in worksheet 1. I'm trying to find the missing individuals from what I have in worksheet 2 and put them in a new worksheet 3. Please tell me the easiest way to do this... Thank you, -- KM |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding missing data between two worksheets
You mean when you copy down
=VLOOKUP(A1,Sheet1!A:A,1,False) you still get =VLOOKUP(A1,Sheet1!A:A,1,False) instead of what you should get =VLOOKUP(A2,Sheet1!A:A,1,False)? Do you have $ in your formula like $A1 or $A$1? If yes, then remove the $. Note that: If you don't want part of a reference to change, put a $ character before that part of the reference. Excel won't change it when you copy it down. E.g., =$A$1 change neither row nor column =A1 change both row and column =$A1 change row but not column =A$1 change column but not row You can select the reference and press F4 repeatedly to cycle through the choices. "Mossykel" wrote: I think I figured out what the problem is... for some reason my excel program isn't updating the formulas when I copy down. It copies the formula but also copies the same number as is in the cell above. Same thing is happening when I update numbers which are linked to a graph. The graph is not updating to represent the numbers in the cells associated. How do I fix this? -- KM "Sheeloo" wrote: Did you enter this on Sheet 1? If all cells in B are same as A then it means that all values in Sheet 1 Col A are present in Sheet 2 Col A If that is the case then try the reverse Enter in B1 of Sheet 2 and copy down =VLOOKUP(A1,Sheet1!A:A,1,False) "Mossykel" wrote: Didn't work. Entered the exact formula and all I get in column B when copying down is the first value in A1... -- KM "Sheeloo" wrote: If you have both lists in Col A of Sheet1 and Sheet2 respectively, then in B1 of Sheet1 enter =VLOOKUP(A1,Sheet2!A:A,1,False) and copy down. Filter on Col B on #N/A... these will be the missing from Sheet2, Col A "Mossykel" wrote: I have a master list of people and their ID number in worksheet 1. I'm trying to find the missing individuals from what I have in worksheet 2 and put them in a new worksheet 3. Please tell me the easiest way to do this... Thank you, -- KM |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding missing data between two worksheets
Try this illustrative sample in my archives:
http://savefile.com/files/103141 Extracting an exclusion list.xls Extract automatically in Z what's in Y which is not found in X (master list) -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- "Mossykel" wrote: I have a master list of people and their ID number in worksheet 1. I'm trying to find the missing individuals from what I have in worksheet 2 and put them in a new worksheet 3. Please tell me the easiest way to do this... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Similar Data in worksheets | Excel Discussion (Misc queries) | |||
Formula for finding data from 2 worksheets | Excel Worksheet Functions | |||
Finding The Missing Data | Excel Discussion (Misc queries) | |||
Finding Missing Data | Excel Worksheet Functions | |||
Compare worksheets and generate list of missing data? | Excel Worksheet Functions |