Contiguous text in a row

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,561
Office Version
  1. 365
  2. 2010
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.
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,399
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 8, 2018
Messages
1,561
Office Version
  1. 365
  2. 2010
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:

MondayTuesdayWednesdayThursdayFridaySaturdaySunday
SamMarySamMarySamSamSam

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

If it looks like this:

MondayTuesdayWednesdayThursdayFridaySaturdaySunday
SamSamSamMaryMarySamSam

Then Sam gets 4 and Mary gets 2.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,399
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 8, 2018
Messages
1,561
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
12,399
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 8, 2018
Messages
1,561
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,399
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 8, 2018
Messages
1,561
Office Version
  1. 365
  2. 2010
Good point. I have to think about what might take place. Thanks again!!!
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,399
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,944
Messages
5,621,756
Members
415,854
Latest member
Tutu123

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