Cheers Domenic but I got completely lost¦
In the solution I offered, I tried to do two things. First, I tried to
take advantage of the fact that the data was sorted by ID, in ascending
order. Then I tried to keep the format for the results unchanged.
With regards to the former, I created dynamic named ranges so that only
the information for the relevant ID would be referenced, not the whole
range. This would both avoid using array formulas and likely improve
the calculation speed.
With regards to the latter, changing the format as Biff suggested and as
you now have it makes it easier.
If you'd like to pursue this further, post back.
--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions
In article ,
Sara wrote:
Cheers for your help Biff
I had a few problems and when copying across the formula had #VALUE as a
result in most cells. I couldnt follow the formula to identify the issue.
In all the cells that had a result of 0, the formatting changed to 0:00
So I ensured that anyone with a start or finish time of midnight became
00:00:01 on sheet 1, and changed the Custom Format to HH:mm for 24 hour
format.
Cheers Domenic but I got completely lost¦
I managed to do the following:
Created a separate sheet (sheet 2) with the unique records from the advanced
filter (omitting all shift data)
Defined the columns in Sheet 1
Column A = ID (A1:A381)
Column B = SHIFT (B1:B381)
Column C = START (C1:C381)
Column D = END (D1:D381)
ID | Shift Day | Start | End
113809 | 17-Aug | 13:00 | 17:00
113809 | 18-Aug | 13:00 | 17:00
113809 | 19-Aug | 13:00 | 17:00
113809 | 20-Aug | 13:00 | 17:00
113809 | 21-Aug | 13:00 | 17:00
148044 | 17-Aug | 13:00 | 20:35
148044 | 18-Aug | 13:00 | 20:35
148044 | 19-Aug | 13:00 | 20:35
Sheet 2
Altered to:
| A | B | C | D | E
| F | G
ROW 1 | | 16-Aug | 16-Aug | 17-Aug | 17-Aug | 18-Aug
| 18-Aug
ROW 2 | ID | Sun Start | Sun End | Mon Start | Mon End | Tues Start | Tues
End
Then the array formula becomes: (found the file from last year)
Start Times
=IF(ISNA(INDEX(START,MATCH(1,(ID=$A3)*(SHIFT=B$1), 0))),"",INDEX(START,MATCH(1,
(ID=$A3)*(SHIFT=B$1),0)))
End Times
=IF(ISNA(INDEX(END,MATCH(1,(ID=$A3)*(SHIFT=C$1),0) )),"",INDEX(END,MATCH(1,(ID=
$A3)*(SHIFT=C$1),0)))
I dont understand how the formulas work
I do get:
=IF(ISNA(formula,"")
Stating that if its blank leave blank
=INDEX(START,MATCH(1,(ID=$A3)*(SHIFT=B$1),0))
Retrieve Start time info if the rest matches
But I dont get the MATCH formula with multiplication