ExcelBanter - Index Match Help

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Index Match Help (https://www.excelbanter.com/excel-worksheet-functions/239595-index-match-help.html)

Index Match Help

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

Index Match Help

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 A2:D9

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,,COU NTIF($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.

--
Biff
Microsoft Excel MVP

"Sara" wrote in message
...
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

Index Match Help

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

Index Match Help

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.

--
Biff
Microsoft Excel MVP

"T. Valko" wrote in message
...
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 A2:D9

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,,COU NTIF($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.

--
Biff
Microsoft Excel MVP

"Sara" wrote in message
...
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

Index Match Help

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

Index Match Help

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

--
Biff
Microsoft Excel MVP

"Sara" wrote in message
...
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 (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 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

Index Match Help

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