Find last occurance of text in range
 

I need to find the last occurance of text in a range of cells. The
range can have empty cells and various text values. This is a project
staff schedule and I want to have a formula that will display the Last
Sick Day and Next Vacation Day.

Column A, Row 2 contains the consultant name
Row 1 contains the date (each day from project start to project end)
Intersecting sells contain "MC" for a sick day and "H" for a vacation
day or blank for neither.

In laymans terms:
For Last Sick Day I want to look at today's date, search across Row 5
to find that date, go backwards in time (columns) to find the cell
reference for the last occurance of "MC" and return the date value from
Row 5.

For Next Vacation Day I want to look at today's date, search across Row
5 to find that date, go forward in time (colums) to find the cell
reference for the next occurance of "H" and return the date value from
Row 5.

I've tried FIND, MATCH (returns the first occurance and doesn't seem to
work across empty cells) and LOOKUP. My guess is that this will involve
a HLOOKUP and possibly MAX but I am lost.

Any help, pointers or a nice cold beer are appreciated! My project
team is large and we want to make sure we control their movements
well.

Cheers,
Andy


--
farutherford
------------------------------------------------------------------------
farutherford's Profile: http://www.excelforum.com/member.php...o&userid=26663
View this thread: http://www.excelforum.com/showthread...hreadid=399389

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())* (2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1 <=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())* (2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1 <=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

RP
(remove nothere from the email address if mailing direct)


"farutherford"
wrote in message
news:farutherford.1ud6ag_1125047143.7846@excelforu m-nospam.com...

I need to find the last occurance of text in a range of cells. The
range can have empty cells and various text values. This is a project
staff schedule and I want to have a formula that will display the Last
Sick Day and Next Vacation Day.

Column A, Row 2 contains the consultant name
Row 1 contains the date (each day from project start to project end)
Intersecting sells contain "MC" for a sick day and "H" for a vacation
day or blank for neither.

In laymans terms:
For Last Sick Day I want to look at today's date, search across Row 5
to find that date, go backwards in time (columns) to find the cell
reference for the last occurance of "MC" and return the date value from
Row 5.

For Next Vacation Day I want to look at today's date, search across Row
5 to find that date, go forward in time (colums) to find the cell
reference for the next occurance of "H" and return the date value from
Row 5.

I've tried FIND, MATCH (returns the first occurance and doesn't seem to
work across empty cells) and LOOKUP. My guess is that this will involve
a HLOOKUP and possibly MAX but I am lost.

Any help, pointers or a nice cold beer are appreciated! My project
team is large and we want to make sure we control their movements
well.

Cheers,
Andy


--
farutherford
------------------------------------------------------------------------
farutherford's Profile:
http://www.excelforum.com/member.php...o&userid=26663
View this thread: http://www.excelforum.com/showthread...hreadid=399389

Thanks Bob. Wicked stuff. I hope you don't mind me asking for another
point of clarification so here goes.

For the Previous MC and Next Holiday formulae, they are the same in
your message so I changed the Next Holiday to read:

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1=TODAY())* (2:2="h"),2:2),1)),"No
planned
holiday",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1 =TODAY())*(2:2="h"),2:2),1)))

The problem is that it does find the next holiday but since its
=TODAY, it returns the last day of the next holiday. I need it to
return the first day. If you have a different formula for this and
could post it, that would be great. Time for me to pick up a good book
on Excel covering formulae and arrays.

Cheers,
Andy


--
farutherford
------------------------------------------------------------------------
farutherford's Profile: http://www.excelforum.com/member.php...o&userid=26663
View this thread: http://www.excelforum.com/showthread...hreadid=399389

Andy,

You can't use LOOKUP this way as it finds the highest instance less that the
search value after TODAY(), whereas you want the first instance after
TODAY().

It can be done though ( and I did work it out last time, just messed up the
cut and paste :-(), with

=IF(ISNA(MATCH("H",IF(($1:$1=TODAY())*(2:2="H"),2 :2),1)),"No planned
holiday",INDEX($1:$1,MATCH("H",IF((1:1=TODAY())*( 2:2="H"),2:2),0)))

again an array formula.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"farutherford"
wrote in message
news:farutherford.1uf3qb_1125137113.8977@excelforu m-nospam.com...

Thanks Bob. Wicked stuff. I hope you don't mind me asking for another
point of clarification so here goes.

For the Previous MC and Next Holiday formulae, they are the same in
your message so I changed the Next Holiday to read:

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1=TODAY())* (2:2="h"),2:2),1)),"No
planned

holiday",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1 =TODAY())*(2:2="h"),2:2),1
)))

The problem is that it does find the next holiday but since its
=TODAY, it returns the last day of the next holiday. I need it to
return the first day. If you have a different formula for this and
could post it, that would be great. Time for me to pick up a good book
on Excel covering formulae and arrays.

Cheers,
Andy


--
farutherford
------------------------------------------------------------------------
farutherford's Profile:
http://www.excelforum.com/member.php...o&userid=26663
View this thread: http://www.excelforum.com/showthread...hreadid=399389

Sorry, a small typo

=IF(ISNA(MATCH("H",IF(($1:$1=TODAY())*(2:2="H"),2 :2),0)),"No planned
holiday",INDEX($1:$1,MATCH("H",IF((1:1=TODAY())*( 2:2="H"),2:2),0)))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
Andy,

You can't use LOOKUP this way as it finds the highest instance less that
the
search value after TODAY(), whereas you want the first instance after
TODAY().

It can be done though ( and I did work it out last time, just messed up
the
cut and paste :-(), with

=IF(ISNA(MATCH("H",IF(($1:$1=TODAY())*(2:2="H"),2 :2),1)),"No planned
holiday",INDEX($1:$1,MATCH("H",IF((1:1=TODAY())*( 2:2="H"),2:2),0)))

again an array formula.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"farutherford"
wrote in message
news:farutherford.1uf3qb_1125137113.8977@excelforu m-nospam.com...

Thanks Bob. Wicked stuff. I hope you don't mind me asking for another
point of clarification so here goes.

For the Previous MC and Next Holiday formulae, they are the same in
your message so I changed the Next Holiday to read:

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1=TODAY())* (2:2="h"),2:2),1)),"No
planned


holiday",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1 =TODAY())*(2:2="h"),2:2),1
)))

The problem is that it does find the next holiday but since its
=TODAY, it returns the last day of the next holiday. I need it to
return the first day. If you have a different formula for this and
could post it, that would be great. Time for me to pick up a good book
on Excel covering formulae and arrays.

Cheers,
Andy


--
farutherford
------------------------------------------------------------------------
farutherford's Profile:
http://www.excelforum.com/member.php...o&userid=26663
View this thread:
http://www.excelforum.com/showthread...hreadid=399389

Thanks a ton Bob. The forumla worked great and now I understand arrays
just a little bit better.

:)


--
farutherford
------------------------------------------------------------------------
farutherford's Profile: http://www.excelforum.com/member.php...o&userid=26663
View this thread: http://www.excelforum.com/showthread...hreadid=399389