The table looks like it's sorted by ID, in ascending order. If this is
the case, try the following...
Assumptions:
A2:D9 contains the data
G2 contains 113809
G3 contains 148044
Helper Column:
E2, copied:
=WEEKDAY(B2)
Define Names:
Select H2
Insert Name Define
Name: Day
Refers to:
=INDEX($E$2:$E$9,MATCH($G2,$A$2:$A$9,0)):INDEX($E$ 2:$E$9,MATCH($G2,$A$2:$
A$9,1))
Click Add
Name: Times
Refers to:
=INDEX($C$2:$D$9,MATCH($G2,$A$2:$A$9,0),1):INDEX($ C$2:$D$9,MATCH($G2,$A$2
:$A$9,1),2)
Click Ok
Formula:
H2, copied across and down:
=IF(ISNUMBER(MATCH(INT((COLUMNS($H2:H2)-1)/2)+1,Day,0)),INDEX(Times,MATCH
(INT((COLUMNS($H2:H2)-1)/2)+1,Day,0),MOD((COLUMNS($H2:H2)-1),2)+1),"")
Note that a unique list of ID's can be generated by using...
Data Filter Advanced Filter Unique records only
--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions
In article ,
Sara wrote:
Hi there
I'm trying to create a index/match formula for rosters.
Sheet 1 looks like this
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
And I'm trying to change it into the following format on sheet 2
ID | Sunday Start | Sunday End | Monday Start | Monday End | Tuesday Start
€¦etc
No one works all 7 days of the week so I know I need some sort of ISERROR or
ISNA as well
The frustrating thing is that I did this last year and can't find the answer