what seems a complex problem looking up a value in a table

A

Andre C

The problem revolves around people's duty roster.

The table looks like this

jan 1 start jan 1 stop jan 2 start jan 2 stop ...
fred 8am 3pm 3pm 6pm
jane 3pm 6pm
john 8am 3pm
peter


The times are in 24 hour format in actual fact. What I want to achieve
is the following outcome table


hours jan 1 jan 2
8 fred john
9 fred john
10 fred john
11 fred john
12 fred john
13 fred john
14 fred john
15 jane fred
16 jane fred
17 jane fred
18 jane fred

To complicate matters futher there can be over lap of hours.

I have looked as LOOKUP and MATCH etc but can't kind find a solution.

Andre
 
B

BrianB

I suggest you try this out with the 3 names first as indicated. I do no
know how many names you need to cover, but you will see that even fo
this small number the formula gets quite long. I would break th
formula down into individual cells (a column for each employee. Sam
formula.) and concatenate those. If employees have an extra shift
would duplicate their name in the list with the different start/sto
times.

This example uses the facility of having named row/column header
instead of cell references.
'-----------------------------------------------------------------------------
1. Make table with list of names fred, jane, john in A2:A4. Header
:-'Start' cell B1,'Stop' Cell C1. Fill in start & stop times
2. Select range A1:C4. From Excel menu - Insert/Name/Create ...To
Row/Left Column …..OK.
3. Range A8:A18 enter times 8:00 to 18:00
4. Range B8:B18 formula (copy & paste from below) :-
=CONCATENATE(IF(AND(A8>=fred start,A8<=fred stop),"fre
",""),IF(AND(A8>=jane start,A8<=jane stop),"jane ",""),IF(AND(A8>=joh
start,A8<=john stop),"john ",""))
'----------------------------------------------------------------------------
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top