If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
One partial table, one master table - update the partial
I've got a listing of 3000 personnel. Personnel in column A, supervisors in
column B. In a separate worksheet, I have a listing of 400 personnel in column A. Same names in column A, sheet 1 as in column A, sheet 2, just a shorter listing in sheet 1.(smaller worksheet). Short list: Sheet 1 Master list: Sheet 2 How do I get EXCEL to compare sheet 1 with sheet 2, and fill in the appropriate supervisor in sheet 1? |
#2
|
|||
|
|||
One partial table, one master table - update the partial
You can use VLOOKUP to find the supervisors for sheet 1 from sheet 2
In the column next to the personell in the short list try this formula =VLOOKUP(A1,Sheet2!$A$1:$B$3000,2,FALSE) The formula will look at A1 in your short list. It will then go to sheet2 (or the name of your master list) to find the persons name. Once found it will look at the 2nd column from the name (name 1st column, supervisor 2nd column) and will return the supervisors name. The FALSE part just tells the formula that you want an exact match. See if that works for you. "boilermaker" wrote: I've got a listing of 3000 personnel. Personnel in column A, supervisors in column B. In a separate worksheet, I have a listing of 400 personnel in column A. Same names in column A, sheet 1 as in column A, sheet 2, just a shorter listing in sheet 1.(smaller worksheet). Short list: Sheet 1 Master list: Sheet 2 How do I get EXCEL to compare sheet 1 with sheet 2, and fill in the appropriate supervisor in sheet 1? |
#3
|
|||
|
|||
One partial table, one master table - update the partial
=VLOOKUP(A2,'Sheet 2'!A:B,2,FALSE)
-- Best Regards, Luke M "boilermaker" wrote in message ... I've got a listing of 3000 personnel. Personnel in column A, supervisors in column B. In a separate worksheet, I have a listing of 400 personnel in column A. Same names in column A, sheet 1 as in column A, sheet 2, just a shorter listing in sheet 1.(smaller worksheet). Short list: Sheet 1 Master list: Sheet 2 How do I get EXCEL to compare sheet 1 with sheet 2, and fill in the appropriate supervisor in sheet 1? |
#4
|
|||
|
|||
One partial table, one master table - update the partial
Thank you, Tim and Luke.
I'm darned close, but not quite there. And I now realize why. One part of the database had a middle initial, yet the other part of the database did not. Not a real problem, yet. The formula worked, but not quite right (I removed the "FALSE" statement). Most of the supervisors are one row higher than they should be. Now I need to figure out how to remove the middle initial, and maybe it'll work as designed. Thanks again. Rick (BoilerMaker) (I could send you the file to look at if you'd like. I'd rather learn by myself with a little help though, as I have a real problem with other people doing my job for me) "Luke M" wrote: =VLOOKUP(A2,'Sheet 2'!A:B,2,FALSE) -- Best Regards, Luke M "boilermaker" wrote in message ... I've got a listing of 3000 personnel. Personnel in column A, supervisors in column B. In a separate worksheet, I have a listing of 400 personnel in column A. Same names in column A, sheet 1 as in column A, sheet 2, just a shorter listing in sheet 1.(smaller worksheet). Short list: Sheet 1 Master list: Sheet 2 How do I get EXCEL to compare sheet 1 with sheet 2, and fill in the appropriate supervisor in sheet 1? . |
Thread Tools | |
Display Modes | |
|
|