Need an adjustment to existing formula for new position codes

[PitchNinja]

Hello, I am looking for an alteration to a formula that assigns a position code based on a few different categories. There is an upcoming change that will go into effect soon that has given me quite the puzzle, but for now, I have attached a sample file to show what is currently happening. Here is the explanation of the "BEFORE" scenario:

-There are 5 different position codes assigned to employees - These are found in column N which is the formula that assigns the numbers based on certain criteria. The formula currently reads

Please Login or Register  to view this content.

The criteria are as follows:
*If an employee has the letters "Jan" in any combination(taken from column K) - hence the "*jan*" in the first part of the formula, they were automatically assigned position #10305318
*If an employee has a dept. code (column D) that is "C61Q","C65S", or "C67G", they were assigned one of two position codes: 20209600 if column H value was a 10 hour position, or 20209586 if it was anything else(we only have 8 hour or 10 hour positions hence why only the 10 is actually specified in the next part of the formula.
*If an employee has NONE of those above two asterisked stipulations, then the formula simply looks in column H to see if they are 8 or 10 hour shift. If they are a 10 hour shift, they are position number 10305275 and if they are 8 hours, it is 10305276. As with before, since there is only either 8 or 10 for choice, only 10 is specified in the last part of the formula.

Here is what is changing - the "AFTER" scenario:
-There will now be even more position codes coming into play. I am open to suggestions if something else other than altering the existing formula would be a better option but it's quite the puzzle they have now:

Warehouse 1 - This encompasses both Grocery and Dairy departments (G or D which are in column L)
*If they have the letters "Jan" in any combination(again taken from column K), they are to be assigned position #20216076
*If they work Daylight Shift(Day Whse or Day Whse 10) OR have an 11A start(Shifts are located in column C and 11A gets a nite rate differential so that's why its dept code in column D was a nite shift code), they should be either 20216086 for 8 hour shift or 20216075 for 10 hour shift. It's the 11AM start that kinda throws a wrench into this one. I found out all along we've had that as the wrong position code. Live and learn...
*If they work Evening or Night Shift(Whse or Whse 10), they should be either 20216113 for 8 hours and 99999 for 10 hours. (99999 is a placeholder number - we don't YET have a 10 hour shift for evening or nite in that department but it could be coming in the near future)

Warehouse 2 - This is the Perishable Department (P in Column L)
*If they have the letters "Jan" in any combination(again taken from column K), they are to be assigned position #10305318
*If they work Daylight Shift(Day Whse or Day Whse 10) - no 11 AM one-offs like Warehouse 1, they should be either 10305276 for 8 hours or 10305275 for 10 hours.
*If they work Evening or Night Shift(Whse or Whse 10), they should be either 20209586 for 8 hours or 20209600 for 10 hours

So this is my puzzle that I have to somehow combine to work in a formula. I could use some help here as I was definitely at a loss to even try with the BEFORE scenario. Maybe some of the other columns I haven't mentioned could be utilized?

[WideBoyDixon]

OK. Complex but this is what I came up with in N2:

Please Login or Register  to view this content.

Does that generate the codes you're expecting?

WBD

[PitchNinja]

Hi WBD, thanks so much for the reply! I put the formula in and it is doing exactly according to the specifications I gave you which I then realized after doing so and pasting the formula in the cell are slightly off. I apologize as staring at it as long as I did with all these crazy different scenarios I wondered if I was going to mess up somewhere We are VERY close though I think. Okay so here goes:

For Warehouse 2
So I think the department codes column will come back into play in this formula as they did in the BEFORE scenario but in less of a capacity. After looking at the explanation I gave you, I believe my error was in telling you "*If they work Evening or Night Shift(Whse or Whse 10), they should be either 20209586 for 8 hours or 20209600 for 10 hours" That part should ONLY be if they have the department code C67G (column D) so I will amend my above statement now to read:
"*If they have a dept. code of C67G, they should be either 20209586 for 8 hours or 20209600 for 10 hours."

So in this case, any Perishable shift that is NOT C67G, (so if they are either C65F or C65L), will either be 10305276 for 8 hours, 10305275 for 10 hours or 10305318 in the case of "Jan". "Jan" will ALWAYS be 10305318 in Warehouse 2 no matter what. Maybe we do not need the Whse or Whse 10 conditions in the formula and it would be better to use dept codes instead?

As of now we don't currently have any 10 hour shifts in the C67G departments, so that previously given number 20209600 won't be in use YET. I've been told they will soon be creating some though so it's good that we at least put it in there as a sort of proactive placeholder as with the 99999 placeholder in the case of Warehouse 1.

Sorry if I've created a lot more difficulties in this with that amendment. That alteration according to the corrected instructions I believe should give me what I need. If you are able to adjust the formula as such? Thanks so much for what you came up with, that really is incredible!

[WideBoyDixon]

Tricky to digest all that but I think this is what you're describing?

Please Login or Register  to view this content.

WBD

[PitchNinja]

I think this is what I need. At first it wasn't, but I did have to change the part where it said if $G2 = "C67G" to $D2 = "C67G" as column G is actually slightly different, it's not a department code column, it's a sort code column which is simplified to only two codes and these designate the actual warehouse building whse 1 or whse 2. Once I changed it to pull from column D, I think that did the trick and to me it looks right. Now I just have to wait until Friday when the boss gets in to review this to be sure it is what they are looking for and that my understanding of their info was correct. I'll be in touch either way to follow up either with an alteration request or a "hey this is exactly what I need" Thread solved + rep deal. Fingers crossed...

[PitchNinja]

Okay here it is...."hey this is exactly what I need!" XD Thank you so much WBD, she looked at it and everything was working exactly as it should. Best part too is now I think I have an understanding of the entire formula. I was even able to add to it in the event that they should isolate another position like they did with "jan" I figured out exactly where to put put those conditions and it works so that now I have a proactive formula when/if it does. Thanks again so much!

[WideBoyDixon]

Hey. You're welcome; glad it's sorted for you

WBD