Result should be with no blank cells

[nader assaleh]

dears
good day
in attached sheet actual output in column A contains blank cell what i want only is to get the
output without blank cells.
many thanks

 

[bosco_yip]

Try..........

In E16, array formula copy down :

=IFERROR(INDEX(F$7:F$14,SMALL(IF(INDEX(G$7:AG$14,,MATCH(B$2,G$5:AG$5,0)+MATCH(B$3,G$6:I$6,0)-1)="Y",ROW(F$7:F$14)-ROW(F$7)+1),ROWS($1:1))),"")

P.s. array formula should be confirmed enter in press with SHIFT+CTRL+ENTER 3 keystrokes together.

Regards
Bosco

 

[eFms]

Here is another solution but a longer one

=IFERROR(INDEX($E$7:$E$14,SMALL(IF(((OFFSET(INDIRECT(ADDRESS(7,MATCH($B$2&$B$3,$F$5:$AF$5&$F$6:$AF$6,0)+5)),0,0,8,1))="Y")*1*ROW($A$1:$A$8)=0,FALSE,((OFFSET(INDIRECT(ADDRESS(7,MATCH($B$2&$B$3,$F$5:$AF$5&$F$6:$AF$6,0)+5)),0,0,8,1))="Y")*1*ROW($A$1:$A$8)),ROW(A1))),"")

With ctrl+shift+enter. A brief explanation is this.

 

[rahulshewale1]

hii,

please find the attached solution with drop menu.please see attached.

 

[nader assaleh]

dears
thank you much all work charm

 

[nader assaleh]

@rahulshewale1
the formula is inconsistent have a look. also it does not cover the the days

 

[rahulshewale1]

HII NADAR,

Please find the attached rectified solution all criteria.


regard
rahul shewale

 

[nader assaleh]

dear Rahul
still not ok, if you choose THURS_Index and shift_B_shift then result is
N
N
Y
Y

 

[rahulshewale1]

hii nader,

please see rectified attachment it is perfect for all day