Large Numbers of Criteria and relevant formula?

[Eazygeezer]

Hi,

I am looking to try and add two automated functions to my stats that I have now been tasked with running every month.

I have a group of 95 members of inspectors, each one is allocated to a manager and there are 5 managers. Is there an automated method of adding a formula to automatically add the manager name? At the moment I have to use the filter function, select each name and then manually enter the manager name in the field and copy it across all relevant cells. When there are 40k plus cells this becomes a nightmare.

Secondly, I also need to try some method of counting the time for unavailability for each booked out option in the data. A working day equates to 7.5 hours so when a member of the team enters a full day absence I should be able to count it as 1, if it is less than a day (for a variety of reasons) I should also be able to add up the time for that day. Unfortunately none of the team are consistent and a day off can be anywhere between 7.5 and 24 hours. Is there any way I can look at the start and end times and if between 7.5 and 24 hours record it as a 7.5, unless of course it is 1.5 days leave, in which case I would have a similar problem, it could be recorded as 36 hours and if using 7.5 as a daily calculator this would count as up to 5 days absence when it should be only 1.5. Also if duplicate absences cover the same times is there a way to ignore duplicates?

Tearing what little i have left of my hair out with this!

I have attached a small sample in the hope that someone can help, also I may not have worded it in the right way so apologies if my request is unclear.

Many thanks.

[BadlySpelledBuoy]

For the first question, if you have a list of all 95 inspectors stored somewhere along with the respective manager then you could simple use a VLOOKUP or INDEX/MATCH to return the managers name in one go. That would save a lot of hair!!

For the second one, I'm not 100% sure I understand. I'll have to mull it over along with the attachment and come back to you on that.

BSB

[Eazygeezer]

HI, I did think of index match with the lists in a separate sheet but I wasn't aware you can use it for large numbers.

The second question is probably easier if i give an example.

Inspector 1 - records a days leave in diary as 7.5 hours
Inspector 2 - records a days leave in diary as 8.5 hours
Inspectors 3 to 11 record any thing between 8.5 and 23hours 59 mins
Inspector 12 - records a day as 24 hours.

Is there a formula that will look at the entries and allow me to calculate just 1 day of absence. This will also apply to multiple and part days of unavailability.

At the moment I am having to go through each diary one by one with a sheet of paper, list the absence type and write the hours down then add them up, onerous is not the word.
I want to be able to count all of the

[BadlySpelledBuoy]

You shouldn't have any issue using an INDEX/MATCH over large numbers. My work often sees me using them to feed data into tables that are 250k rows without too much hassle. And even if you have to wait a few seconds for it to calculate, it's still going to be a tad quicker than updating it manually

The second one sounds like a problem that needs fixing at the source by making them fill things out properly rather than trying to butcher the results using formulas. That's not to say it cannot be done, just that it's not ideal.
A sample working show a handful of examples of the different way these things are recorded along with manually generated expected results would be helpful.

BSB

[Eazygeezer]

HI,

Thanks for the feedback. I am very much an excel novice, although I have used index match I am not sure how I can get the formula to say if inspector 1 then Manager 1 and so on.

Re the completing diaries accurately, unfortunately the system we use doesn't limit the entries to a maximum for the day nor avoid either duplicated or multiple entries at the same time and so the guys can and do put in any old times, we have been trying to get them to put accurate times in for the last 13 years..............

[BadlySpelledBuoy]

In the attached version of your file I've created a table on "Sheet1" and called it tblManagerLookup.
Obviously the data in there won't match what you have as I have no idea which AOMs manage which inspectors, but it should serve the purpose of demonstration.

Then in the AOM column of the table on the "Stats View" sheet, I've added the formula below:

Please Login or Register  to view this content.

This will search the Inspector Name column in the table I added and when it finds the relevant inspector name it will return the corresponding AOM name.

Hopefully that makes some sense, but let me know if it doesn't and I'll try to explain better.

As for the second part of the question, I'm still a bit confused on what the actual ask is. An example workbook showing expected outcome is going to be necessary for that one.
Although if you've been struggling to get them to do it correctly for 13 years then it's either time to find a new system for them to use, or to start firing people for general incompetence!

BSB

[Eazygeezer]

Hi,

That is exactly what I was looking for, that's amazing! Thank you for so much for your help. I will try to get an example of the other issue from a single person and try to explain it a bit better.

[AliGW]

When you do, please start a new thread with a better (meaningful and properly descriptive) title. Thanks.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[Eazygeezer]

Hi,

I have already thanked the super helpful person that solved my problem. I will start a new thread and try to make it easier to understand, the problem is I don't exactly know the right way to word it but will try my best

[AliGW]

Your thread title needs to tell us what you are trying to do, so will be a one-line summary of your opening post. I take it you DO know what you are trying to achieve???

[Eazygeezer]

Hi,

Yes I definitely know what I want to achieve, just not sure 100% how to explain it, once I can add a small sample file hopefully it will make it easier to understand and easier for me to explain.

[AliGW]

OK. Have a go and I'll then let you know if the title is OK or not.

Which version of Excel are you using? Is it a Mac or Windows version?