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