identifying two names abriviated and grouped

[Don Juan]

Hello,

I am trying to write a program that will schedule persons for work. Sometimes I have to split a shift in half and have two people work it. Right now I'm just using countif formulas to identify a name in a range of cells (the schedule) and list how many times it occurs. I then multiply that amount by 8 hours in other cells to get their hourly total worked for that week. Simple enough. The problem is that the names have to be identified exactly the same as the column I have all 15 names listed in as in the schedule. I want to be able to add the first 4 letters of two different names into a cell seperated by a "-". example

Nathan and Sarah want to split a shift. Nathan is in the cell already. the countif formula displays 1 because nathan is in the schedule already. So his hourly count is 8. I'm going to modify it to Nath-Sara to indicate two persons splitting the one shift. So now instead of listing 1 whole number it would have to list it as .5 or another way is to have it list 1 for both persons but still only count it as 4 hours (if the shift wasn't split it would still have to count the person as working 8 hours). I'm thinking countif formula is too simple by itself to look for that unless I painstakingly type in a column all 15 people and then all the possible combinations of the names together: nath/sara sara/nath John/Mich Rick/paul etc. I know there is a better and quicker way. The program is attached and will give you a better idea of what i'm talking about.

Any help would be greatly appreciated. Thank you. (using 2008)

[Jazzer]

Hi,

This formula might work for you:

Please Login or Register  to view this content.

this should be in cell C25 and it is an array formula, so you should enter it by pressing shift+ctrl+enter. After that, there should appear {}'s around the formula.

The first part of the formula, NOT(ISERROR(FIND(LOWER(LEFT(A25,4)),LOWER(PO1WEEK))), uses FIND-function to find the 4 first letters of the worker's name in A25 from the area of PO1WEEK. If it does, it returns a number, but if it doesn't, it returns an error. With NOT and ISERROR formulas these values are converted to TRUE's and FALSE's and because this is an array formula, the result is something like {TRUE, FALSE, TRUE, FALSE}.

The second part, IF(ISERROR(FIND("/",LOWER(PO1WEEK))),1,0.5), tries to find the / symbol from the searched area. If / is found, the IF return's 0.5, other wise it returns 1. So the result is something like {1, 1, 0.5, 1}.

After that, these 2 arrays are multiplyed. Because TRUE=1 and FALSE=0, the answer of the example is {TRUE, FALSE, TRUE, FALSE}*{1, 1, 0.5, 1}={TRUE*1, FALSE*1, TRUE*0.5, FALSE*1}={1*1, 0*1, 1*0.5, 0*1}={1, 0, 0.5, 0}.

Around this all is a SUM-function, which sum's the terms of the array. So SUM({1, 0, 0.5, 0}) = 1.5.

Hope this helps.

- Asser

[Dav]

Its not as simple as you think as:
some firstnames

1. are not 4 or more characters long
2. you have shortened

michael to mike in your example

but the following should work as an array (shift ctrl enter when entering). Michaels needs to be mich or you change the name in the full name sheet to mike

Please Login or Register  to view this content.

Does that help

Regards

Dav