vlookup with employees with the same shift

[jordiejones]

So I have been working on a sales floor planner that pulls a ton of info onto one sheet that would be printed out every day. Part of this sheet is the schedule for the day. I have a dummy workbook showing how I am pulling data but I have a vlookup problem where if more than one person is working the same shift, it only looks up the first record it finds. Its kind of a big staff and on a rare occasion 11 people could theoretically come in at the same time.

I'm Excel self taught and if there is a better more efficient way to get to the same result, by all means!!!!

What I want:

Column A Column B
Mark 10-6
Angie 10-3
Bill 12-8
Larry 12-8
Lenny 12-5
Micky 3-8

What Im getting:

Column A Column B
Mark 10-6
Angie 10-3
Bill 12-8
Bill 12-8
Lenny 12-5
Micky 3-8


here is a RapidShare link to the file
Example of Pull from Schedule

 

[T. Valko]

So I have been working on a sales floor planner that pulls a ton of info onto one sheet that would be printed out every day. Part of this sheet is the schedule for the day. I have a dummy workbook showing how I am pulling data but I have a vlookup problem where if more than one person is working the same shift, it only looks up the first record it finds. Its kind of a big staff and on a rare occasion 11 people could theoretically come in at the same time.

I'm Excel self taught and if there is a better more efficient way to get to the same result, by all means!!!!

What I want:

Column A Column B
Mark 10-6
Angie 10-3
Bill 12-8
Larry 12-8
Lenny 12-5
Micky 3-8

What Im getting:

Column A Column B
Mark 10-6
Angie 10-3
Bill 12-8
Bill 12-8
Lenny 12-5
Micky 3-8


here is a RapidShare link to the file
Example of Pull from Schedule

I have your file in front of me.

Where is the data that you need to extract? Is from the schedule sheet?

 

[alansidman]

Look at this thread.

http://www.mrexcel.com/forum/showthread.php?t=344561&highlight=muvlookup

Alan

 

[jordiejones]

@Valko, yes. I am pulling data from the schedule sheet. I then have a ton of helper ranges(including a list that puts the text shifts in order as excel can't really sort the values in the manner they need to be sorted in). From there I just link all result of the helper cells to their destination on my chart (printable area).

 

[jordiejones]

@alansidman I reviewed the thread but could not find a connection between the two problems. I basically have a new problem due to weak excel skills. The problem is that I have a schedule and I want to (with a formula) paste only the people who are actually working and sort them based on the shift. To do this, I VLOOKUPed the day of the week in the schedule. I then eliminated "","0","REQ","PTO","OFF"&"MUH" which are office codes that should be ignored. I then look what was left (just the sample shifts for that day) and RANKed it to put it in the order that I needed. From there, I tried to reattach the people's names who are working those shifts...this is where I found the problem of IF duplicate VLOOKUP values, VLOOKUP only returns the first value found. I would be nice if once it found a name that matched accending, that it would just go to the next person who worked the same shift.

Ive played with different solutions but since the shifts change daily, the duplicate shifts may not be in the same cell

Jordan

 

[T. Valko]

@Valko, yes. I am pulling data from the schedule sheet. I then have a ton of helper ranges(including a list that puts the text shifts in order as excel can't really sort the values in the manner they need to be sorted in). From there I just link all result of the helper cells to their destination on my chart (printable area).

OK...

I created a couple of named ranges:

• Name: Schedule
• Refers to: =INDEX(schedule!$B$3:$O$13,,MATCH('goal sheet'!$A$1,schedule!$B$2:$O$2,0))
• Name: Hours
• Refers to: =ISNUMBER(FIND("-",Schedule))

I entered this formula in cell C1 of the Goal Sheet:

=COUNTIF(Schedule,"*-*")

That will return the count of how many people are scheduled for the date entered in cell A1.

To list the names and shifts...

Enter this array formula** in A2:

=IF(ROWS(A$2:A2)>C$1,"",INDEX(schedule!A:A,SMALL(IF(Hours,ROW(Schedule)),ROWS(A$2:A2))))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Enter this formula in B2:

=IF(A2="","",INDEX(Schedule,MATCH(A2,schedule!A$3:A$13,0)))

Select both A2 and B2 and copy down to A9:B9.

Here's your file with these formulas implemented:

schedule(1).xlsx 17kb

http://cjoint.com/?1drezSOctWb

 

[jordiejones]

WOW!!!! I can't believe how amazing that is! I have been working on this forever! Its so clean! I only recently found out about array functions so its crazy nice to see a compact clean formula! Still need to disect it to figure out what you did, but wow! Next step...sort! Thanks again!

Jordan

 

[T. Valko]

WOW!!!! I can't believe how amazing that is! I have been working on this forever! Its so clean! I only recently found out about array functions so its crazy nice to see a compact clean formula! Still need to disect it to figure out what you did, but wow! Next step...sort! Thanks again!

Jordan

If you want to sort based on the shift times then you'd have to enter the times as TIMES. Right now you have the times entered as TEXT strings.

You'd have to add another column for each day of the week and put the start time in one cell and the end time in another cell.

Then, depending on how you want it sorted, you'd look for either the earliest start times or the latest start times.

 

[jordiejones]

@Valko, I had applied a rank next to each possible shift at the bottom of the . Longer Shifts or listed before shorter shifts with the same start time until 3pm. It actually works really well with what we need it for! I don't understand named ranges yet so its still a little difficult once Ive applied the sort order to MATCH the name back with their scheduled time.

 

[jordiejones]

@tvalko is there a way to sort the shifts based on the ranking table on the bottom of the goal sheet?