Results 1 to 7 of 7

Need an adjustment to existing formula for new position codes

Threaded View

  1. #1
    Forum Contributor
    Join Date
    05-12-2021
    Location
    USA
    MS-Off Ver
    Microsoft Office 365
    Posts
    259

    Need an adjustment to existing formula for new position codes

    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
    Formula: copy to clipboard
    =IF(COUNTIF(K2,"*jan*"),10305318,IF(ISNUMBER(MATCH(D2,{"C61Q","C65S","C67G"},0)),IF(H2=10,20209600,20209586),IF(H2=10,10305275,10305276)))
    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?
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Compare JSON data and splitting into corrected codes, deleted codes and added codes
    By chandramouliarun in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 12-01-2020, 06:46 AM
  2. [SOLVED] Compare JSON data and splitting into corrected codes, deleted codes and added codes
    By chandramouliarun in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-29-2020, 12:06 PM
  3. Existing VBA codes are not working
    By PRADEEPB270 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-15-2020, 02:48 AM
  4. Replies: 1
    Last Post: 06-21-2019, 06:18 AM
  5. [SOLVED] adjustment in vba properties editor for sheet tabs and merging of codes
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-25-2017, 10:53 AM
  6. Unique codes from an existing list. Possible VLOOKUP?
    By s_bruno1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-03-2016, 10:19 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1