07-18-2008, 09:24 AM | #1 | ||
College Benchwarmer
Join Date: Oct 2000
Location: Louisiana
|
Friday Fun- Looking for some excel help
I am trying to put together a spread sheet for an audit, and I can't quite figure this formula out...
I have 1 sheet (Sheet A) which lists about 300 employees and their job code. I then have multiple sheets for different pay periods, and I want a formula that can look back to Sheet A and pull the correct job code for the correct employee. On Sheet A (Now called updated employee list) Column A is the employees name, column C is his code- that is all there is. Lets say on my sheet for the 2nd QTR Column A is the name, and column *C* (sorry had originally put B) is the old code which needs to be replaced....then a bunch of columns of data, I want to replace the old code with the new one. I'm just trying to find a way that can quickly match the names, pull the correct code. What I've come up with so far has only been able to match the name and pull the same name- which doesn't do me much good And to do this by hand would take a couple days... Last edited by Doug5984 : 07-18-2008 at 09:30 AM. |
||
07-18-2008, 09:29 AM | #2 |
College Benchwarmer
Join Date: Oct 2000
Location: Louisiana
|
Dola- This is what I was using (Updated Employee List = Sheet A)
=VLOOKUP(A2,'Updates Employee List'!$C$2:$C$347,1) |
07-18-2008, 10:21 AM | #4 | |
Hattrick Moderator
Join Date: Jan 2003
Location: Pintendre, Qc, Canada
|
Quote:
if the code is in the C column (or 3rd colum), your vlookup formula should look somthing like this: =VLOOKUP(A2,'Updates Employee List'!$A$2:$C$347,3,FALSE) that is assuming the name you wanna look up is in A2 and your range has names in the A column... Try that and let us know. FM
__________________
A Black Belt is a White Belt who refused to give up... follow my story: The real life story of a running frog... |
|
07-18-2008, 10:23 AM | #5 |
Hattrick Moderator
Join Date: Jan 2003
Location: Pintendre, Qc, Canada
|
dola, the FALSE I added at the end is so it returns an error message if it doesn't find a perfect match. If set to TRUE or left blank, it will return the closest match and in cases of match a name to some specific information, you don't really want that...
fM
__________________
A Black Belt is a White Belt who refused to give up... follow my story: The real life story of a running frog... |
07-18-2008, 10:40 AM | #6 |
College Benchwarmer
Join Date: Oct 2000
Location: Louisiana
|
that appears to have worked.
there are quite a few that did not turn up exact matches, but the vast majority of the workers are foreigners with 4 names, and this is spanning 2 years, and 2 different people working on it, so sometimes the full name is spelled out, sometimes 1 middle name is just a letter, sometimes both are- but I can go through and manually fix that, its better than misclassifying someone. Thank you! Saved me a lot of unneeded work, i knew i was on the right track just couldn't quite finish it. |
07-18-2008, 10:46 AM | #7 | |
Hattrick Moderator
Join Date: Jan 2003
Location: Pintendre, Qc, Canada
|
Quote:
yeah, sometimes even only one extra space between the first and last name will screw you up, but at least you only get to troubleshoot those... Glad it worked. FM
__________________
A Black Belt is a White Belt who refused to give up... follow my story: The real life story of a running frog... |
|
07-18-2008, 10:49 AM | #8 |
College Benchwarmer
Join Date: Oct 2000
Location: Louisiana
|
Also, theoretically- if I wanted to pull the new job description as well, I could do that by simply adding that column and change it from column 3, to column 4?
That makes it a lot easier, and will make it much more presentable as well. |
07-18-2008, 10:51 AM | #9 | |
Hattrick Moderator
Join Date: Jan 2003
Location: Pintendre, Qc, Canada
|
Quote:
pretty much yeah, although you need to adjust your range in the vlookup formula to include the D column... FM
__________________
A Black Belt is a White Belt who refused to give up... follow my story: The real life story of a running frog... |
|
07-18-2008, 11:09 AM | #10 | |
Coordinator
Join Date: Oct 2000
Location: Big Ten Country
|
Quote:
I thought it was the other way around -- TRUE means it has to match exactly, and FALSE means it can be similar. |
|
07-18-2008, 11:53 AM | #11 | |
Hattrick Moderator
Join Date: Jan 2003
Location: Pintendre, Qc, Canada
|
Quote:
nope, TRUE stands for true to "find the closest match". I don't have excel in english here so I can't copy/paste the excel help text, but it should be easy for you to check. FM
__________________
A Black Belt is a White Belt who refused to give up... follow my story: The real life story of a running frog... |
|
07-18-2008, 02:51 PM | #12 |
Pro Starter
Join Date: Mar 2004
Location: Oakland, CA
|
Did anyone else quickly glance at this thread title and see "Friday Fun- Looking for some sexual help"?
|
07-22-2008, 09:38 AM | #13 |
College Benchwarmer
Join Date: Oct 2000
Location: Louisiana
|
Ok, FM solved the last one- and I got another, I think it might be much more complex, and not solved by a simple formula.
Basically because of the reclassifications they made I end up with 2 sometimes 3 rows for the same employee for different pay periods. To clean up the sheet I'd like to have only 1 row per employee. Now, Columns A, B, C = Name, Code, Description.... Is there anyway in excel to say if Column A, B, & C are the same to sum and merge the rows (sometimes 2, sometimes 3). |
07-29-2008, 04:08 PM | #14 |
College Prospect
Join Date: Apr 2006
Location: Boston, MA
|
OK here's mine:
Running office 2007 I'm trying to copy some cells from sql management studio into excel. Excel in it's infinite wisdom is space delimiting my paste. So "I hate office" in one cell is coming over as "I" "hate" "office" Anyone got a clue? |
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
Thread Tools | |
|
|