Help on a project utilizing formulas, lookups, and sorting

bh24524

Board Regular
Joined
Dec 11, 2008
Messages
224
Office Version
  1. 2013
  2. 2007
Hello, I have a project that is rather involved that I'm not sure how to go about doing. This project involves tracking vacations each year for employees in different departments. Currently, we are using an application that uses FoxPro which I have no familiarity with and only one person in our entire company does. Recently, we had a malfunction in the program and needed it fixed ASAP to be able to post vacation charts which are generated from the program, one chart for each dept which is exported to Excel. In each employees name, we will have their dept. code as well as a shift code which designates what shift they are on and what days they have off during the week respectively. Example for the Grocery Shifts - the dept code for a daylight shift is C61C and their are several different shift codes for it to designate the days off. There are many different codes, I think 44 in total right now, but I'll just list 3 of them for simplicity sake: "3", "7", and "R". So you could have C61C - 3, C61C - 7, and C61C - R.

I am ultimately looking to see if this function in the program we are currently using could ultimately work in Excel. This way should the person who has the knowledge of the program leave the company, we wouldn't be stuck if the program malfunctioned again. What I picture is having a chart template on its own tab that has a drop-down menu for the depts. and the shift codes. Cells A11 thru A42 and Cells B11 thru B42 would be the cells that contain Employee numbers and names respectively. These numbers and names would be looked up from a different tab and would actually change depending on what Dept. Code and Shift code is chosen from the Shift Drop-down menu that I mentioned above. Also, I would want it to automatically sort the populated names by seniority date which is actually contained in cells C11 thru C42 - Now those dates get tricky because we haev two groups of employees so those dates are actually looked up from two different tabs, not one.

I know how to do Index/match lookups when an employee number is typed in to return that specific value but not how to do lookups where when you type a code that fits multiple employees that it would bring multiple names up. So this would be my first hurdle. Is something like this possible to do? I'll give more specific info as to the other tabs but just first want to find out if it's doable.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

bh24524

Board Regular
Joined
Dec 11, 2008
Messages
224
Office Version
  1. 2013
  2. 2007
Okay, so I did a little test on a separate sheet with a few additional dept codes, and here's what I have so far:

Book1
ABCDEFGHI
1john 1C61CA123DeptCode
2john 2C61C3124C61C3124
3john 3C65L1125127
4john 4C65DM126132
5john 5C61C3127137
6john 6C65DM128142
7john 7C61CA129147
8john 8C65DM130 
9john 9C65L1131 
10john 10C61C3132 
11john 11C65DM133 
12john 12C65L1134 
13john 13C61CA135 
14john 14C67G4136 
15john 15C61C3137 
16john 16C65L1138 
17john 17C61CA139 
18john 18C67G4140 
19john 19C65L1141 
20john 20C61C3142 
21john 21C65DM143 
22john 22C61CA144 
23john 23C65DM145 
24john 24C65L1146 
25john 25C65L3147 
26john 26C65DM148 
27john 27C65L1149 
28john 28C61CA150 
Sheet1
Cell Formulas
RangeFormula
I2:I28I2=IFERROR(INDEX($D$1:$D$200, SMALL(IF(($H$2=$C$1:$C$200), MATCH(ROW($C$1:$C$200), ROW($C$1:$C$200)), ""),ROWS($A$1:A1))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.


With this formula, I can get it to yield the results of all occurrences of a "3". What I am looking to do if possible is have it yield all the results that fit C61C and 3. If I need to I can just use a macro to sort the dates that I will eventually put in so thats no problem. But for this example, there is a C65L dept. with a 3 that should not be counted. What formula could I use for this to capture those 2 criteria in G2 and H2?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,448
Messages
5,596,210
Members
414,046
Latest member
mbeutler1203

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top