Attendance tracker filter issues

[sabi.kiss]

Hi there

Has been a while since I posted here, and most probably someone else asked the very same question, however could not find exactly what I was looking for. Hope everyone is safe during these crazy times.

I have been building an attendance tracker for my employees (200+ employees to be exact) and my monthly dashboard looks quite busy and not the way I would like it to look.
Instead of having 200+ employees showing on my dashboard, and most of them having no leave taken whatsoever within the particular month I have chosen, I would like to filter down and only show those employees who have any leave booked in within the chosen month.

So, here we go, I have an input table with 3 columns, column B being my names field, column C leave start date and column D leave end date.
My filter filed looks like this. Cell K would be the month chosen, then as a helper cell K first day of the chosen month and cell M last day of chosen month.

I have used the below formula to filter through the names column, nd it works, however if I have one employee whose leave is peeking through another month, that filed won't be shown within my result list (result column is K).
INDEX($B$3:$B$10, SMALL(IF($C$3:$C$10>=$L$3, IF($C$3:$C$10<=$M$3, ROW($C$3:$C$10)-ROW($C$3)+1)), ROWS(K$6:K6)))

Example:

As you can see in the second image, my name isn't picked up within FEBURARY.

Any help would be appreciated.

Many thanks in advance.

Sabi

 

[lrobbo314]

unique.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		Name	Start	End						Year	2020
2		Boon	1/25/2020	1/25/2020						Month	January
3		Taylor	1/14/2020	1/22/2020
4		Tarata	1/18/2020	1/18/2020							Boon
5		Kiss	1/27/2020	2/5/2020							Taylor
6		Venour	2/2/2020	2/5/2020							Tarata
7
8
9
10
Sheet2

Cell Formulas
Range	Formula
K4:K10	K4	=IFERROR(INDEX($B$2:$B$6, AGGREGATE(15,6,(1/((PROPER(TEXT($C$2:$C$6,"MMMM"))=$K$2)*(PROPER(TEXT($D$2:$D$6,"MMMM"))=$K$2)))*ROW($C$2:$C$6)-1,ROWS($K$4:K4))),"")

 

[sabi.kiss]

Thanks for your reply lrobbo314, however one name hasn't been picked up within the JANUARY filter, even though "KISS" does fall within the month of January with a could of days.
Hope you understand what I'm trying to achieve here. So, "KISS" would have to come up in both January & February listings, as its creeping into both months.

 

[alz]

Try this!
{=INDEX($B$2:$B$6,SMALL(IF((--(TEXT($C$2:$C$6,"yyyymmmm")=$K$1&$K$2)+(--(TEXT($D$2:$D$6,"yyyymmmm")=$K$1&$K$2)))>0,ROW($B$2:$B$6)-ROW($B$1)),ROWS($K$4:K4)))}

 

[sabi.kiss]

Thanks heaps alz .. works a charm

 

[alz]

Thanks heaps alz .. works a charm

Thanks for the feedback @sabi.kiss

 

[sabi.kiss]

Now is there a possibility on removing duplicate names? At the moment if I have separate leave dates booked for the same person, it will pick the name up twice.

 

[alz]

Hi Try this in K4!

{=IFERROR(INDEX($B$2:$B$6,
SMALL(IF(FREQUENCY(IF(IF((--(TEXT($C$2:$C$6,"yyyymmmm")=$K$1&$K$2)+(--(TEXT($D$2:$D$6,"yyyymmmm")=$K$1&$K$2)))>0,$B$2:$B$6)<>FALSE,
MATCH(IF((--(TEXT($C$2:$C$6,"yyyymmmm")=$K$1&$K$2)+(--(TEXT($D$2:$D$6,"yyyymmmm")=$K$1&$K$2)))>0,$B$2:$B$6),IF((--(TEXT($C$2:$C$6,"yyyymmmm")=$K$1&$K$2)+(--(TEXT($D$2:$D$6,"yyyymmmm")=$K$1&$K$2)))>0,$B$2:$B$6),0)),
ROW($B$2:$B$6)-ROW($B$2)+1),ROW($B$2:$B$6)-ROW($B$2)+1),ROWS($K$4:K4))),"")}

 

[sabi.kiss]

legend ALZ .. many thanks again.