# Attendance tracker filter issues

#### sabi.kiss

##### Board Regular
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.

Sabi

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### lrobbo314

##### Well-known Member
unique.xlsx
ABCDEFGHIJK
1NameStartEndYear2020
2Boon1/25/20201/25/2020MonthJanuary
3Taylor1/14/20201/22/2020
4Tarata1/18/20201/18/2020Boon
5Kiss1/27/20202/5/2020Taylor
6Venour2/2/20202/5/2020Tarata
7
8
9
10
Sheet2
Cell Formulas
RangeFormula
K4:K10K4=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

##### Board Regular
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

##### Board Regular
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)))}

#### Attachments

• Screenshot (20).png
27.2 KB · Views: 6

#### sabi.kiss

##### Board Regular

Thanks heaps alz .. works a charm

#### sabi.kiss

##### Board Regular

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

##### Board Regular
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

##### Board Regular
legend ALZ .. many thanks again.

alz

Replies
1
Views
127
Replies
7
Views
877
Replies
1
Views
173
Replies
2
Views
132
Replies
10
Views
261

1,129,837
Messages
5,638,639
Members
417,039
Latest member
Sworks

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back