Skip over 1st number and locate 2nd - ?
 

Top chart is the schedule - bottom chart is the name of the person and the
hours they are working for the day.

Reference: Beaudreau, Wed 9/2, working 3pm & 11pm

The challenge: at the Bottom of Column E ,showing duplicate 3pm. How can I
get this formula to skip the 1st number (3p) and record the 2nd (11p)? If
the person doesn't work a double the value would be empty. Please Advise.
Thanks!

=CONCATENATE((IF(ISNA(MATCH($A28,E$5:E$24,0)),0,IN DEX($A$5:$A$24,MATCH($A28,E$5:E$24,0)))),"/",(IF(ISNA(MATCH($A28,E$5:E$24,0)),0,INDEX($A$5:$A $24,MATCH($A28,E$5:E$24,0)))))

A B C D E
Sunday Monday Tuesday Wed
8/30/2009 8/31/2009 9/1/2009 9/2/2009
7A Rondeau McNichols McNichols McNichols
7A Jenkes Casavant
7A-C Labonte Bergeron Chiarini DiSandro
7A-C DiSandro Labonte
8A Machado Chiarini Sousa Cairone
8A Pereira Pereira
9A Baughan Colombier Jenkes
9A Vaughan Lawson McKinnon
11A Sousa Baughan Baughan
11A Trainor Machado Houle
3P Ferri Ferri Ferri Beaudreau
3P Rondeau Howard Perry Machado
3P-C Beaudreau Beaudreau DiSandro
3P-C Snowling Snowling
4P Houle White Sousa Campbell
4P Machado Rondeau Morrison Pereira
11P Ferri Howard Ferri Beaudreau
11P White McKinnon Perry Howard
11P-C DiSandro DeCesare Bergeron Perry
11P-C Snowling DiSandro Snowling

Shift/Week
Baughan 0 9A 11A 11A
Beaudreau 0 3P-C 3P-C 3P/3P

Skip over 1st number and locate 2nd - ?
 

Try...

B27, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=CHOOSE(COUNTIF(B$5:B$24,$A27)+1,0,INDEX($A$5:$A$2 4,MATCH($A27,B$5:B$24,0
)),INDEX($A$5:$A$24,MATCH($A27,B$5:B$24,0))&"/"&INDEX($A$5:$A$24,SMALL(IF
(B$5:B$24=$A27,ROW($A$5:$A$24)-ROW($A$5)+1),2)))

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
Schedule Formula - Please Help - Thanks!
soft.com wrote:

Top chart is the schedule - bottom chart is the name of the person and the
hours they are working for the day.

Reference: Beaudreau, Wed 9/2, working 3pm & 11pm

The challenge: at the Bottom of Column E ,showing duplicate 3pm. How can I
get this formula to skip the 1st number (3p) and record the 2nd (11p)? If
the person doesn't work a double the value would be empty. Please Advise.
Thanks!

=CONCATENATE((IF(ISNA(MATCH($A28,E$5:E$24,0)),0,IN DEX($A$5:$A$24,MATCH($A28,E$
5:E$24,0)))),"/",(IF(ISNA(MATCH($A28,E$5:E$24,0)),0,INDEX($A$5:$A $24,MATCH($A2
8,E$5:E$24,0)))))

A B C D E
Sunday Monday Tuesday Wed
8/30/2009 8/31/2009 9/1/2009 9/2/2009
7A Rondeau McNichols McNichols McNichols
7A Jenkes Casavant
7A-C Labonte Bergeron Chiarini DiSandro
7A-C DiSandro Labonte
8A Machado Chiarini Sousa Cairone
8A Pereira Pereira
9A Baughan Colombier Jenkes
9A Vaughan Lawson McKinnon
11A Sousa Baughan Baughan
11A Trainor Machado Houle
3P Ferri Ferri Ferri Beaudreau
3P Rondeau Howard Perry Machado
3P-C Beaudreau Beaudreau DiSandro
3P-C Snowling Snowling
4P Houle White Sousa Campbell
4P Machado Rondeau Morrison Pereira
11P Ferri Howard Ferri Beaudreau
11P White McKinnon Perry Howard
11P-C DiSandro DeCesare Bergeron Perry
11P-C Snowling DiSandro Snowling

Shift/Week
Baughan 0 9A 11A 11A
Beaudreau 0 3P-C 3P-C 3P/3P

Skip over 1st number and locate 2nd - ?
 

Thanks Domenic - You are awesome! You made my day!!!

Karen


"Domenic" wrote:

Try...

B27, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=CHOOSE(COUNTIF(B$5:B$24,$A27)+1,0,INDEX($A$5:$A$2 4,MATCH($A27,B$5:B$24,0)),INDEX($A$5:$A$24,MATCH($ A27,B$5:B$24,0))&"/"&INDEX($A$5:$A$24,SMALL(IF(B$5:B$24=$A27,ROW($A$5 :$A$24)-ROW($A$5)+1),2)))

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
Schedule Formula - Please Help - Thanks!
soft.com wrote:

Top chart is the schedule - bottom chart is the name of the person and the
hours they are working for the day.

Reference: Beaudreau, Wed 9/2, working 3pm & 11pm

The challenge: at the Bottom of Column E ,showing duplicate 3pm. How can I
get this formula to skip the 1st number (3p) and record the 2nd (11p)? If
the person doesn't work a double the value would be empty. Please Advise.
Thanks!

=CONCATENATE((IF(ISNA(MATCH($A28,E$5:E$24,0)),0,IN DEX($A$5:$A$24,MATCH($A28,E$
5:E$24,0)))),"/",(IF(ISNA(MATCH($A28,E$5:E$24,0)),0,INDEX($A$5:$A $24,MATCH($A2
8,E$5:E$24,0)))))

A B C D E
Sunday Monday Tuesday Wed
8/30/2009 8/31/2009 9/1/2009 9/2/2009
7A Rondeau McNichols McNichols McNichols
7A Jenkes Casavant
7A-C Labonte Bergeron Chiarini DiSandro
7A-C DiSandro Labonte
8A Machado Chiarini Sousa Cairone
8A Pereira Pereira
9A Baughan Colombier Jenkes
9A Vaughan Lawson McKinnon
11A Sousa Baughan Baughan
11A Trainor Machado Houle
3P Ferri Ferri Ferri Beaudreau
3P Rondeau Howard Perry Machado
3P-C Beaudreau Beaudreau DiSandro
3P-C Snowling Snowling
4P Houle White Sousa Campbell
4P Machado Rondeau Morrison Pereira
11P Ferri Howard Ferri Beaudreau
11P White McKinnon Perry Howard
11P-C DiSandro DeCesare Bergeron Perry
11P-C Snowling DiSandro Snowling

Shift/Week
Baughan 0 9A 11A 11A
Beaudreau 0 3P-C 3P-C 3P/3P