| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need to number but skip blank rows | Excel Discussion (Misc queries) | |||
| Fill Cells with same number in three rows then skip to next number | Excel Worksheet Functions | |||
| Locate negative number | Excel Worksheet Functions | |||
| number columns skip blanks | Excel Discussion (Misc queries) | |||
| Locate first number that exceeds reference value | Excel Worksheet Functions | |||
![Schedule Formula - Please Help - Thanks![_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
