# Contiguous text in a row

#### kweaver

##### Well-known Member
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.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### jasonb75

##### Well-known Member
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

##### Well-known Member
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

##### Well-known Member
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

##### Well-known Member

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

##### Well-known Member
Does this help?
Book123.xlsm
ABCDEFGHIJ
1MonTueWedThuFriSatSunSamMary
2SamMarySamMarySamSamSam64
3SamSamSamMaryMarySamSam42
kweaver
Cell Formulas
RangeFormula
I2:J3I2=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

##### Well-known Member

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

#### jasonb75

##### Well-known Member
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
ABCDEFGHIJK
1WkMonTueWedThuFriSatSunSamMary
21SamMarySamMarySamSamSam86
32SamSamSamMaryMarySamSam
kweaver
Cell Formulas
RangeFormula
J2:K2J2=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

##### Well-known Member
Good point. I have to think about what might take place. Thanks again!!!

#### jasonb75

##### Well-known Member
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.

Replies
1
Views
83
Replies
5
Views
412
Replies
3
Views
203
Replies
11
Views
819
Replies
1
Views
135

1,127,653
Messages
5,626,105
Members
416,161
Latest member
David1966Lewis

### 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.

### Which adblocker are you using?

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

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