Index Match Help

[Sara]

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

 

[T. Valko]

Here's one way...

On Sheet2 you'll have to include dates as a column header and you'll have to
repeat the date like this:

|8/16/2009......|8/16/2009....|8/17/2009.......|8/17/2009......|
| Sunday Start | Sunday End | Monday Start | Monday End |

Let's assume your data on Sheet1 is in the range A29

Sheet2 B1:O1 = dates as shown above
Sheet2 A2 = some ID number

Enter this formula in B2:

=SUMPRODUCT(--(Sheet1!$A$2:$A$9=$A2),--(Sheet1!$B$2:$B$9=B$1),INDEX(Sheet1!$C$2:$D$9,,COUNTIF($B$1:B$1,B$1)))

Copy across to O2

Custom format B2:O2 as h:mm;;;

That will suppress the results that end up as 0.

 

[Domenic]

The table looks like it's sorted by ID, in ascending order. If this is
the case, try the following...

Assumptions:

A29 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

 

[T. Valko]

Clarification:

Custom format B2:O2 as h:mm;;;
That will suppress the results that end up as 0.

That will suppress the *display* of results that end up as 0.

The cell will contain numeric 0 you just won't see it.

 

[Sara]

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 couldn’t 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 (D1381)

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 Time
=IF(ISNA(INDEX(START,MATCH(1,(ID=$A3)*(SHIFT=B$1),0))),"",INDEX(START,MATCH(1,(ID=$A3)*(SHIFT=B$1),0)))

End Time
=IF(ISNA(INDEX(END,MATCH(1,(ID=$A3)*(SHIFT=C$1),0))),"",INDEX(END,MATCH(1,(ID=$A3)*(SHIFT=C$1),0)))

I don’t understand how the formulas work
I do get:
=IF(ISNA(formula,"")
Stating that if it’s blank – leave blank


=INDEX(START,MATCH(1,(ID=$A3)*(SHIFT=B$1),0))
Retrieve Start time info if the rest matches
But I don’t get the MATCH formula with multiplication

 

[T. Valko]

with a start or finish time of midnight

Well, I didn't think about midnight!

If you format your times on Sheet1 as [h]:mm and enter 12:00 AM as 24:00...

Then on Sheet2 change the custom format to [h]:mm;;;

Here's a small sample file with those changes implemented.

xSara1.xls 20kb

http://cjoint.com/?iniFhYuAZR

 

[Domenic]

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.