Been at it for a while... still trying to figure it out

All fields marked * are to be modified, while those marked otherwise
are static

I am working on a duty roster table that looks like this:

Name* - Shift* - Mon* - Tue* - Wed* - *etc etc etc
dude1 - shft1- D1 - D1 - D3 <---- let's call these Markers
dude2 - shft1 - D2 - D2 - D2
dude3 - shft2 - N1 - N1 - N2
dude4 - shft3 - D2 - D2 - D1

I have a legend table that says what D1, D2, N1, etc means
like so:

Leg - DAYS* - desc
D1 - 1.0 - Day shift
D2 - 0.5 - Day shift (half)
D3 - 1.0 - Day shift (training)
N1 - 1.0 - Night shift
N2 - 0.5 - Night shift (half)

where DAYS denotes how whether it is a full day or half day shift

Now I have a Headcount table to list the number of people that are
planned for duty:
Shift* - Mon - Tue - Wed
shft1
shft2
shft3

Now in the Headcount table, I want to count how many people are working
on each day who belong to a Shift by first matching their Markers to the
Legend for the number of days, then summing them up.

Sample DESIRED result, based on info provided above:
A - B - C - D
1 Shift* - Mon - Tue - Wed
2 shft1 - 1.5 - 1.5 - 1.5
3 shft2 - 1.0 - 1.0 - 0.5
4 shft3 - 0.5 - 0.5 - 1.0

I'm currently using Array Formulas with the IF function, but I'm having
trouble matching the Markers to the legend table.
This is in cell B2:
{=SUM(IF(<roster shft column range=A2, IF(ISNUMBER(SEARCH("D?",<roster
date range)),VLOOKUP(<roster Mon column,<legend table,2,false),o)))}

This only works halfway cause the VLOOKUP doesn't match all the Markers
in the array with the Legend. Instead uses the value of the first
Match.
So it uses the value for "shft1" on "Mon" which is D1, and counts the
it as 2.0 instead of 1.5 (D1 = 1.0) + (D2 = 0.5).

Help anyone?


--
ronlim
------------------------------------------------------------------------
ronlim's Profile: http://www.excelforum.com/member.php...o&userid=35738
View this thread: http://www.excelforum.com/showthread...hreadid=555209

Assumptions:

A1:E5 contains...

Name - Shift - Mon - Tue - Wed
dude1 - shft1- D1 - D1 - D3
dude2 - shft1 - D2 - D2 - D2
dude3 - shft2 - N1 - N1 - N2
dude4 - shft3 - D2 - D2 - D1

A10:C14 contains...

Leg - DAYS - desc
D1 - 1.0 - Day shift
D2 - 0.5 - Day shift (half)
D3 - 1.0 - Day shift (training)
N1 - 1.0 - Night shift
N2 - 0.5 - Night shift (half)

A20:D23 contains...

Shift - Mon - Tue - Wed
shft1
shft2
shft3

Formula:

B21, copied down and across:

=SUMPRODUCT(--($B$2:$B$5=$A21),SUMIF($A$10:$A$14,INDEX($C$2:$E$5 ,0,MATCH(
B$20,$C$1:$E$1,0)),$B$10:$B$14))

Hope this helps!

In article ,
ronlim wrote:

Been at it for a while... still trying to figure it out

All fields marked * are to be modified, while those marked otherwise
are static

I am working on a duty roster table that looks like this:

Name* - Shift* - Mon* - Tue* - Wed* - *etc etc etc
dude1 - shft1- D1 - D1 - D3 <---- let's call these Markers
dude2 - shft1 - D2 - D2 - D2
dude3 - shft2 - N1 - N1 - N2
dude4 - shft3 - D2 - D2 - D1

I have a legend table that says what D1, D2, N1, etc means
like so:

Leg - DAYS* - desc
D1 - 1.0 - Day shift
D2 - 0.5 - Day shift (half)
D3 - 1.0 - Day shift (training)
N1 - 1.0 - Night shift
N2 - 0.5 - Night shift (half)

where DAYS denotes how whether it is a full day or half day shift

Now I have a Headcount table to list the number of people that are
planned for duty:
Shift* - Mon - Tue - Wed
shft1
shft2
shft3

Now in the Headcount table, I want to count how many people are working
on each day who belong to a Shift by first matching their Markers to the
Legend for the number of days, then summing them up.

Sample DESIRED result, based on info provided above:
A - B - C - D
1 Shift* - Mon - Tue - Wed
2 shft1 - 1.5 - 1.5 - 1.5
3 shft2 - 1.0 - 1.0 - 0.5
4 shft3 - 0.5 - 0.5 - 1.0

I'm currently using Array Formulas with the IF function, but I'm having
trouble matching the Markers to the legend table.
This is in cell B2:
{=SUM(IF(<roster shft column range=A2, IF(ISNUMBER(SEARCH("D?",<roster
date range)),VLOOKUP(<roster Mon column,<legend table,2,false),o)))}

This only works halfway cause the VLOOKUP doesn't match all the Markers
in the array with the Legend. Instead uses the value of the first
Match.
So it uses the value for "shft1" on "Mon" which is D1, and counts the
it as 2.0 instead of 1.5 (D1 = 1.0) + (D2 = 0.5).

Help anyone?

Hi,
I haven't been able to work out a formula-based solution but I have
a simple UDF-based solution. If this is of interest, let me have an e-mail
address (or contact me at toppers<atjohntopley.fsnet.co.uk) and I'll forward
to you.

"ronlim" wrote:


Been at it for a while... still trying to figure it out

All fields marked * are to be modified, while those marked otherwise
are static

I am working on a duty roster table that looks like this:

Name* - Shift* - Mon* - Tue* - Wed* - *etc etc etc
dude1 - shft1- D1 - D1 - D3 <---- let's call these Markers
dude2 - shft1 - D2 - D2 - D2
dude3 - shft2 - N1 - N1 - N2
dude4 - shft3 - D2 - D2 - D1

I have a legend table that says what D1, D2, N1, etc means
like so:

Leg - DAYS* - desc
D1 - 1.0 - Day shift
D2 - 0.5 - Day shift (half)
D3 - 1.0 - Day shift (training)
N1 - 1.0 - Night shift
N2 - 0.5 - Night shift (half)

where DAYS denotes how whether it is a full day or half day shift

Now I have a Headcount table to list the number of people that are
planned for duty:
Shift* - Mon - Tue - Wed
shft1
shft2
shft3

Now in the Headcount table, I want to count how many people are working
on each day who belong to a Shift by first matching their Markers to the
Legend for the number of days, then summing them up.

Sample DESIRED result, based on info provided above:
A - B - C - D
1 Shift* - Mon - Tue - Wed
2 shft1 - 1.5 - 1.5 - 1.5
3 shft2 - 1.0 - 1.0 - 0.5
4 shft3 - 0.5 - 0.5 - 1.0

I'm currently using Array Formulas with the IF function, but I'm having
trouble matching the Markers to the legend table.
This is in cell B2:
{=SUM(IF(<roster shft column range=A2, IF(ISNUMBER(SEARCH("D?",<roster
date range)),VLOOKUP(<roster Mon column,<legend table,2,false),o)))}

This only works halfway cause the VLOOKUP doesn't match all the Markers
in the array with the Legend. Instead uses the value of the first
Match.
So it uses the value for "shft1" on "Mon" which is D1, and counts the
it as 2.0 instead of 1.5 (D1 = 1.0) + (D2 = 0.5).

Help anyone?


--
ronlim
------------------------------------------------------------------------
ronlim's Profile: http://www.excelforum.com/member.php...o&userid=35738
View this thread: http://www.excelforum.com/showthread...hreadid=555209

Domenic came up trumps!

"Toppers" wrote:

Hi,
I haven't been able to work out a formula-based solution but I have
a simple UDF-based solution. If this is of interest, let me have an e-mail
address (or contact me at toppers<atjohntopley.fsnet.co.uk) and I'll forward
to you.

"ronlim" wrote:


Been at it for a while... still trying to figure it out

All fields marked * are to be modified, while those marked otherwise
are static

I am working on a duty roster table that looks like this:

Name* - Shift* - Mon* - Tue* - Wed* - *etc etc etc
dude1 - shft1- D1 - D1 - D3 <---- let's call these Markers
dude2 - shft1 - D2 - D2 - D2
dude3 - shft2 - N1 - N1 - N2
dude4 - shft3 - D2 - D2 - D1

I have a legend table that says what D1, D2, N1, etc means
like so:

Leg - DAYS* - desc
D1 - 1.0 - Day shift
D2 - 0.5 - Day shift (half)
D3 - 1.0 - Day shift (training)
N1 - 1.0 - Night shift
N2 - 0.5 - Night shift (half)

where DAYS denotes how whether it is a full day or half day shift

Now I have a Headcount table to list the number of people that are
planned for duty:
Shift* - Mon - Tue - Wed
shft1
shft2
shft3

Now in the Headcount table, I want to count how many people are working
on each day who belong to a Shift by first matching their Markers to the
Legend for the number of days, then summing them up.

Sample DESIRED result, based on info provided above:
A - B - C - D
1 Shift* - Mon - Tue - Wed
2 shft1 - 1.5 - 1.5 - 1.5
3 shft2 - 1.0 - 1.0 - 0.5
4 shft3 - 0.5 - 0.5 - 1.0

I'm currently using Array Formulas with the IF function, but I'm having
trouble matching the Markers to the legend table.
This is in cell B2:
{=SUM(IF(<roster shft column range=A2, IF(ISNUMBER(SEARCH("D?",<roster
date range)),VLOOKUP(<roster Mon column,<legend table,2,false),o)))}

This only works halfway cause the VLOOKUP doesn't match all the Markers
in the array with the Legend. Instead uses the value of the first
Match.
So it uses the value for "shft1" on "Mon" which is D1, and counts the
it as 2.0 instead of 1.5 (D1 = 1.0) + (D2 = 0.5).

Help anyone?


--
ronlim
------------------------------------------------------------------------
ronlim's Profile: http://www.excelforum.com/member.php...o&userid=35738
View this thread: http://www.excelforum.com/showthread...hreadid=555209

Thanks Domenic, it work great... except for one weird glitch.

One of the Day Shift markers I used is "*D" (in the example I gave D1)
Somehow any cell marked *D counts as 2 days. All my other markers *D1,
*D2, etc work fine except *D

I've checked my legend, and it is marked as 1 day. I've tried the value
0 (zero) in the legend, and it still gives me a 1. I can't seem to
figure out where that extra day comes from.


--
ronlim
------------------------------------------------------------------------
ronlim's Profile: http://www.excelforum.com/member.php...o&userid=35738
View this thread: http://www.excelforum.com/showthread...hreadid=555209

That's probably because your legend contains a shift marker where D is
the last character in the text string. An asterisk is considered a
wildcard character. So is a question mark. So *D will match xD, xxD,
xxxD, etc. Try using characters other than an asterisk or question
mark...

In article ,
ronlim wrote:

Thanks Domenic, it work great... except for one weird glitch.

One of the Day Shift markers I used is "*D" (in the example I gave D1)
Somehow any cell marked *D counts as 2 days. All my other markers *D1,
*D2, etc work fine except *D

I've checked my legend, and it is marked as 1 day. I've tried the value
0 (zero) in the legend, and it still gives me a 1. I can't seem to
figure out where that extra day comes from.