| 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
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... |
|
#4
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 |
|
#5
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 |
|
#6
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 |
|
#7
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
finding missing data between two worksheets
I somehow had it set for manual update of formulat values instead of
automatic so the formulas were coping but the exact value was as well. Got that fixed but I'm still unable to get your formula to work.... I'm using your =vlookup(A1,Sheet2!A:A,1,False) in col B1 of Sheet 1 which is my master copy (has more values than Sheet 2) and all I'm getting is #N/A. Checked the format of both columns they are the same... both in number format.... -- KM "Sheeloo" wrote: 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
finding missing data between two worksheets
Just to test
Copy what is in A1 of Sheet1 to any cell in Column A of Sheet2. You should get a match in B1. When this works it compare the other value in Col A Sheet2 which is equal to A1 and why it is not being picked up. Can you send the file to me? id to_sheeloo and add @hotmail.com to the id. "Mossykel" wrote: I somehow had it set for manual update of formulat values instead of automatic so the formulas were coping but the exact value was as well. Got that fixed but I'm still unable to get your formula to work.... I'm using your =vlookup(A1,Sheet2!A:A,1,False) in col B1 of Sheet 1 which is my master copy (has more values than Sheet 2) and all I'm getting is #N/A. Checked the format of both columns they are the same... both in number format.... -- KM "Sheeloo" wrote: 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
finding missing data between two worksheets
I sent you the file...
-- KM "Sheeloo" wrote: Just to test Copy what is in A1 of Sheet1 to any cell in Column A of Sheet2. You should get a match in B1. When this works it compare the other value in Col A Sheet2 which is equal to A1 and why it is not being picked up. Can you send the file to me? id to_sheeloo and add @hotmail.com to the id. "Mossykel" wrote: I somehow had it set for manual update of formulat values instead of automatic so the formulas were coping but the exact value was as well. Got that fixed but I'm still unable to get your formula to work.... I'm using your =vlookup(A1,Sheet2!A:A,1,False) in col B1 of Sheet 1 which is my master copy (has more values than Sheet 2) and all I'm getting is #N/A. Checked the format of both columns they are the same... both in number format.... -- KM "Sheeloo" wrote: 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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
finding missing data between two worksheets
I have sent the file back.
Values in Col B had a character in the first position. Converted all to numbers by multiplying by 1 [enter 1 in any cell, Copy, Select Col B, PASTE SPECIAL|Multiply "Mossykel" wrote: I sent you the file... -- KM "Sheeloo" wrote: Just to test Copy what is in A1 of Sheet1 to any cell in Column A of Sheet2. You should get a match in B1. When this works it compare the other value in Col A Sheet2 which is equal to A1 and why it is not being picked up. Can you send the file to me? id to_sheeloo and add @hotmail.com to the id. "Mossykel" wrote: I somehow had it set for manual update of formulat values instead of automatic so the formulas were coping but the exact value was as well. Got that fixed but I'm still unable to get your formula to work.... I'm using your =vlookup(A1,Sheet2!A:A,1,False) in col B1 of Sheet 1 which is my master copy (has more values than Sheet 2) and all I'm getting is #N/A. Checked the format of both columns they are the same... both in number format.... -- KM "Sheeloo" wrote: 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 |
| 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 | |||
Linear Mode
