• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting daily shift data

K

kabooboo

New Member
Hello Everybody

I am seeking your assistance with a formula to extract shift data from an annual roster, and then to display the names of those people working either Early or late shifts against the actual date on a new worksheet (or at the bottom of each daily column of the annual sheet for transfer to a new sheet)

Vlookup could work, but the staff list wont be in A>Z format and with 4 teams of 20, there are some vacancies to contend with as well as different duty types in between them.

On average, we'd have about 15 staff on each shift, but they could come from any of the 4 teams so I need to be able to extract the information from the annual list and then condense the names into a list for each shift type.

Hope this makes sense. Attached sample sheet for reference


grateful if anybody has a neat trick to accomplish this.


regards

Kabooboo
 

Attachments

  • sample.xlsx
    34.3 KB · Views: 13
Try,

In D116, formula copied across to AH116 and all copied down :

=IFERROR(INDEX($B$11:$B$99,AGGREGATE(15,6,ROW(D$11:D$99)-ROW(D$10)/(D$11:D$99=OFFSET($B$115,INT((ROWS($1:1)-1)/15)*15+1,0)),MOD(ROWS($1:1)-1,15)+1)),"")

Regards
Bosco
 

Attachments

  • SampleOfDailyShift.xlsx
    43.7 KB · Views: 13
Bosco

thank you so much. What a neat solution

now to figure out transferring the data from this formula for each shift based on the date in row 1 to another worksheet..... without using VBA
 
Bosco,

I have the transfer to dashboard sheet formula resolved, but unfortunately the end user is still using excel 2007, so their version does not recognise the formula above

is there a possible solution that would work in the older 2007 version ?

regards

Kabooboo
 
kabooboo said:
Bosco,

I have the transfer to dashboard sheet formula resolved, but unfortunately the end user is still using excel 2007, so their version does not recognise the formula above

is there a possible solution that would work in the older 2007 version ?

regards

Kabooboo
Click to expand...

Formula for Excel 2007 or earlier version.

1] In "Annual (2)" sheet D116, array formula copied across to AH116 and all copied down :


=IFERROR(INDEX($B$11:$B$99,SMALL(IF(D$11:D$99=OFFSET($B$115,INT((ROWS($1:1)-1)/15)*15+1,0),ROW(D$11:D$99)-ROW(D$10)),MOD(ROWS($1:1)-1,15)+1)),"")

p.s. Array formula to be confirmed by pressing SHIFT+CTRL+ENTER 3 keystrokes together.

2] See revised attachment

Regards
Bosco
 

Attachments

  • SampleOfDailyShift(1).xlsx
    94.2 KB · Views: 10
How do i autonumber based on a value but in my case there is no "TOTAL". This is how my data is:

UNKNOWN
UNKNOWN
UNKNOWN
SEG034-PI-012.038
SEG034-PI-012.039
SEG034-PI-012.038
SEG034-PI-012.038
SEG034-PI-012.038
SEG034-PI-001.019
SEG034-PI-001.019
UNKNOWN
UNKNOWN
SEG034-PI-001.019
SEG034-PI-001.019
SEG034-PI-001.019
SEG034-PI-001.021
SEG034-PI-001.021
SEG034-PI-001.021
UNKNOWN
UNKNOWN
UNKNOWN
UNKNOWN
UNKNOWN
UNKNOWN
UNKNOWN
UNKNOWN
UNKNOWN
UNKNOWN
UNKNOWN
UNKNOWN
UNKNOWN
UNKNOWN
CT0034.0202.004
CT0034.0202.004

So i basically want to put number (ID), if the value repeats itll get the same value if it already has assigned (in the example data unknown repeats randomly).

Please help.
Regards Topazy.
 
You must log in or register to reply here.
Back
Top