Friday Fun- Looking for some excel help

Doug5984 · 07-18-2008, 09:24 AM

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...

Doug5984 · 07-18-2008, 09:29 AM

Dola- This is what I was using (Updated Employee List = Sheet A)
=VLOOKUP(A2,'Updates Employee List'!$C$2:$C$347,1)

PackerFanatic · 07-18-2008, 10:04 AM

I know I have done this before, and I can't for the life of me recall what I did...I will try and find it if no one else has it...

FrogMan · 07-18-2008, 10:21 AM

Quote:

Originally Posted by Doug5984

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...

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

FrogMan · 07-18-2008, 10:23 AM

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

Doug5984 · 07-18-2008, 10:40 AM

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.

FrogMan · 07-18-2008, 10:46 AM

Quote:

Originally Posted by Doug5984

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.

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

Doug5984 · 07-18-2008, 10:49 AM

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.

FrogMan · 07-18-2008, 10:51 AM

Quote:

Originally Posted by Doug5984

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.

pretty much yeah, although you need to adjust your range in the vlookup formula to include the D column...

FM

Passacaglia · 07-18-2008, 11:09 AM

Quote:

Originally Posted by FrogMan

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

I thought it was the other way around -- TRUE means it has to match exactly, and FALSE means it can be similar.

FrogMan · 07-18-2008, 11:53 AM

Quote:

Originally Posted by Passacaglia

I thought it was the other way around -- TRUE means it has to match exactly, and FALSE means it can be similar.

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

Rizon · 07-18-2008, 02:51 PM

Did anyone else quickly glance at this thread title and see "Friday Fun- Looking for some sexual help"?

Doug5984 · 07-22-2008, 09:38 AM

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).

Fighter of Foo · 07-29-2008, 04:08 PM

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?

07-18-2008, 10:04 AM	#3
PackerFanatic Pro Starter Join Date: Jul 2005 Location: Appleton, WI	I know I have done this before, and I can't for the life of me recall what I did...I will try and find it if no one else has it... __________________ Commissioner of the RNFL

07-18-2008, 10:23 AM	#5
FrogMan 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, 09:29 AM	#2
Doug5984 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:40 AM	#6
Doug5984 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:49 AM	#8
Doug5984 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-22-2008, 09:38 AM	#13
Doug5984 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
Fighter of Foo 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)