Contiguous text in a row

[kweaver]

In a scheduling application, I have a rows (representing weeks in a month) for people-shifts with 7 columns (days of the week, cols B to H).
The cells in a row will have names of people. There are 20 people to choose from using drop downs.
The people get drive-time compensation. That's the tricky issue.

If in week 1, for example, a given person works on Friday and Sunday (i.e., not contiguous days), he doesn't stay overnight, so he's counted twice to get drive-time compensation for Friday (coming and going) and for Sunday (coming and going).

If, however, in a given week, this person works Monday, Tuesday, and Wednesday, he stays overnight for 2 nights and he's only counted twice for drive-time compensation (coming on Monday and leaving on Wednesday).

I need to know this situation for each person for each week. Ideally, it sounds like a UDF to me that looks for a (each, ultimately) person in a given week, but I cannot wrap my head around it.
Would appreciate the brain-trust offering any suggestions on how I might do this.

 

[jasonb75]

Do you really need it to be a UDF? The first thing that comes to mind is a FREQUENCY array formula, but I wouldn't like to commit to anything without a visual example of the layout. Your description doesn't tell us things like if it's one row per person per week, or multiple people in the same row.

 

[kweaver]

No, it doesn't have to be a UDF but I need the computation for various rows.

A better explanation, I hope, for my examples above:

If a week looks like this:

Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
Sam	Mary	Sam	Mary	Sam	Sam	Sam

Then Sam gets drive time 6 times and Mary gets drive time 4 times.

If it looks like this:

Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
Sam	Sam	Sam	Mary	Mary	Sam	Sam

Then Sam gets 4 and Mary gets 2.

 

[jasonb75]

So are you saying that you would only ever have 1 person getting drive time on any given day of a specific week?

What I'm trying to visualise is for example, Sam on Mon, Tue and Wed, Mary on Tue, Wed, Thu and Fri of the same week.

 

[kweaver]

If Sam is on Tues and Wed, Mary can't be. It could be: S S S M M M M which gives S 2 and M 2

S M S M M S M; S=6 and M=6
S M S M M M M; S = 4 and M = 4

 

[jasonb75]

Does this help?

Book123.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Mon	Tue	Wed	Thu	Fri	Sat	Sun		Sam	Mary
2	Sam	Mary	Sam	Mary	Sam	Sam	Sam		6	4
3	Sam	Sam	Sam	Mary	Mary	Sam	Sam		4	2
kweaver

Cell Formulas
Range	Formula
I2:J3	I2	=SUM(IF(FREQUENCY(IF($A2:$G2=I$1,COLUMN($A$1:$G$1)),IF($A2:$G2<>I$1,COLUMN($A$1:$G$1))),2))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[kweaver]

GREAT! Thanks for keeping me from pulling all of my hair out.

 

[jasonb75]

You're welcome.
The only issue that you might encounter is going from the end of one week to the start of the next as a continuous period. i.e. if E2 to C3 for Sam was considered as a single 6 day period, Fri-Wed.

Here's an alternative way for that scenario, but it doesn't allow for individual weeks. Maybe possible to adapt if needed.

Book123.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Wk	Mon	Tue	Wed	Thu	Fri	Sat	Sun		Sam	Mary
2	1	Sam	Mary	Sam	Mary	Sam	Sam	Sam		8	6
3	2	Sam	Sam	Sam	Mary	Mary	Sam	Sam
kweaver

Cell Formulas
Range	Formula
J2:K2	J2	=SUM(IF(FREQUENCY(IF($B2:$H3=J$1,$A$2:$A$3*7+COLUMN($B$1:$H$1)),IF($B2:$H3<>J$1,$A$2:$A$3*7+COLUMN($B$1:$H$1))),2))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[kweaver]

Good point. I have to think about what might take place. Thanks again!!!

 

[jasonb75]

If you mock up a sample to show how you want the results displayed once you've given it some thought, I'll have a look at adapting the formula to suit.