Creating a Leave Tracker/Attendance Tracker

[cloud2828]

I'm extremely new to excel, initially I created this program in VBA but after running it, it would take over five minutes to process so I had to start over using Excel formulas. I am familiar with coding that's why I decided to do it VBA but the logic is Excel formulas are so complex to me I can't figure out what I would need to do. I already have a calendar that goes horizontally across my worksheet for a year period. What I would like to do is have employee names next to the calendar and highlight the days they are here and not through a table on another worksheet where you would type in the information (e.g. Person's name, type of leave, date they're leaving and date they're coming back). The issue I'm having is within this table, there will be duplicate names that must be accounted for and when I try to use Match Index functions, it only matches the first duplicate it sees. Doing some research I found a formula to find duplicate names which worked find, I altered it to my use but the problem with it is that it only accounts for one duplicate name and I can ONLY get back duplicate names and not the rest of names.

Let me explain more what this formula is supposed to do, it is supposed to match a name from one worksheet to the next worksheet with the calendar. The worksheet without the calendar has a table where the name and date range and type of leave etc is inputted. Once this is inputted, the date range should be highlighted and state the type of leave on the calculator under their name.

=IF(AND(E$8 >= INDEX(Table1;MATCH($AJ29;Table1[Column1];0);2); E$8<=INDEX(Table1;MATCH($AJ29;Table1[Column1];0);3));"Z";"W")

The code above is not able to account for duplicates but it works as intended

=IF(ROWS(AR$30:AR30)<=COUNTIF($AO$11:AO16;AJ$29);INDEX(AP$11:AP$16;SMALL(IF(AO$11:AO$16=AJ$29;ROW(AO$11:AO$16)-10);ROWS(AR$30:AR30)));INDEX(AO$11:AQ16;MATCH($AO11;AO$11:AO16;0);2))

This one accounts for the column where you put in the starting date in the table but can only account for ONE duplicate name at a time.

I've been trying for quite some time to implement both of these. My logic is that it should be a nested if statement where it will account for all the duplicates then afterwards account for the remaining names in the table. I know there must be an easier way to do this but I can't think of any other way.

All I basically want, is to find a way for match index to account for duplicate names, I feel like there should be a very simple method to do this and that my formula is extremely excessive.

 

[cloud2828]

Sure, you can just post this on a different sheet and point the reference to this sheet i have posted the output on the new sheet3 and it is referring to sheet2:

	A	B	C	D	E	F	G
Names	Rows
Name10
Name14

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=IFERROR(AGGREGATE(15,6,(ROW(Sheet2!$A$2:$A$13)-ROW(Sheet2!$A$2)+1)/($A2=Sheet2!$A$2:$A$13),COLUMNS(B2:$B2)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you again, there's a lot of work I need to do before I get this to work, will let you know if I get through with it!

 

[Aryatect]

Glad could help & sure, let us know if you face any issues.

 

[cloud2828]

Glad could help & sure, let us know if you face any issues.

I'm already facing an issue, basically I would like to refer the rows back to the rows in the table I have, is that possible? For help in understanding, currently in sheet 1 the table's rows are at cells AJ20 to AJ25 but as you know I currently have the row numbers in sheet 2 at E2:I2 and downwards. So is it possible I can translate the row numbers to the table that I have in sheet 1? I still feel like I'm over-complicating this but if I can figure this out I will be done with this. Would also like to know how I can add an OR statement to this formula:

=IF(AND(E$8 >= INDEX(Table1;MATCH($AJ29;Table1[Column1];0);2); E$8<=INDEX(Table1;MATCH($AJ29;Table1[Column1];0);3));"Z";"W")

Currently "MATCH($AJ29;Table1[Column1];0);2)" is giving the row of the table for the index but in order to have multiple date ranges for one person, I'd need an OR statement by that part of the index formula where you enter the row to account for the other rows if there are duplicate names.

 

[Aryatect]

Can you upload a sample dummy file to dropbox.com or google drive and can share the link. Please do fill in the expected result. will check it out.

 

[cloud2828]

Can you upload a sample dummy file to dropbox.com or google drive and can share the link. Please do fill in the expected result. will check it out.

https://drive.google.com/file/d/1NxcwnW7Txp0lRyKqLgyrOEzVmrPkWwpT/view?usp=sharing

This is the link to my excel file, i highlighted a border so everything beyond that is just me testing stuff and is not important to you, currently I have the calendar set from January 1st to 31st 2019. It matches the names in the two tables and gives the row of the table which has the dates, the index function is used with match to get the row and the column is set to 2/3 depending on if it is start date or end date. I created an AND statement with an IF statement that basically compares the start date and end date such that if the value falls between the range, return "Z" and if it doesn't return "W". The problem I am currently getting is that match cannot deal with duplicates so it will never see the duplicated name thus not being able to obtain the row in which it is resulting in not getting the start and end date to be compared. I think you will understand what I'm trying to do once you look at the excel file.

 

[Aryatect]

Hi, I copied your data on an excel and just worked on the double part, Think below should work for you:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	Names	Tue 01	Wed 02	Thu 03	Fri 04	Sat 05	Sun 06	Mon 07	Tue 08	Wed 09	Thu 10	Fri 11	Sat 12	Sun 13	Mon 14	Tue 15	Wed 16	Thu 17	Fri 18	Sat 19	Sun 20	Mon 21	Tue 22	Wed 23	Thu 24	Fri 25	Sat 26	Sun 27	Mon 28	Tue 29	Wed 30	Thu 31
2	Name1	Z	Z	Z	Z	W	W	Z	Z	Z	Z	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W
3	Name2	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W
4	Name3	Z	Z	Z	Z	Z	Z	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W
5	Name4	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W
6	Name5	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	W	W	Z	Z	Z	Z	Z	W	W	W	W	W	W	W	W
7	Name6	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W
8
9
10
11
12
13	Column1	Column2	Column3	Desc
14	Name1	1/1/2019	1/4/2019	A
15	Name2	1/1/2019	1/16/2019	B
16	Name3	1/1/2019	1/6/2019	C
17	Name4	1/1/2019	1/16/2019	A
18	Name5	1/1/2019	1/16/2019	B
19	Name5	1/19/2019	1/23/2019	E
20	Name1	1/7/2019	1/10/2019	A
Sheet1

Cell Formulas
Range	Formula
B2	=IF(AGGREGATE(14,6,($A$14:$A$20=$A2)*($B$14:$B$20<=B$1)*($C$14:$C$20>=B$1),1),"Z","W")

Copy the formula from on B2:AF7

 

[cloud2828]

Hi, I copied your data on an excel and just worked on the double part, Think below should work for you:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
Names
Name1	Z	Z	Z	Z	W	W	Z	Z	Z	Z	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W
Name2	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W
Name3	Z	Z	Z	Z	Z	Z	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W
Name4	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W
Name5	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	Z	W	W	Z	Z	Z	Z	Z	W	W	W	W	W	W	W	W
Name6	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W
Column1	Column2	Column3	Desc
Name1	A
Name2	B
Name3	C
Name4	A
Name5	B
Name5	E
Name1	A

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]Tue 01[/TD]
[TD="align: right"]Wed 02[/TD]
[TD="align: right"]Thu 03[/TD]
[TD="align: right"]Fri 04[/TD]
[TD="align: right"]Sat 05[/TD]
[TD="align: right"]Sun 06[/TD]
[TD="align: right"]Mon 07[/TD]
[TD="align: right"]Tue 08[/TD]
[TD="align: right"]Wed 09[/TD]
[TD="align: right"]Thu 10[/TD]
[TD="align: right"]Fri 11[/TD]
[TD="align: right"]Sat 12[/TD]
[TD="align: right"]Sun 13[/TD]
[TD="align: right"]Mon 14[/TD]
[TD="align: right"]Tue 15[/TD]
[TD="align: right"]Wed 16[/TD]
[TD="align: right"]Thu 17[/TD]
[TD="align: right"]Fri 18[/TD]
[TD="align: right"]Sat 19[/TD]
[TD="align: right"]Sun 20[/TD]
[TD="align: right"]Mon 21[/TD]
[TD="align: right"]Tue 22[/TD]
[TD="align: right"]Wed 23[/TD]
[TD="align: right"]Thu 24[/TD]
[TD="align: right"]Fri 25[/TD]
[TD="align: right"]Sat 26[/TD]
[TD="align: right"]Sun 27[/TD]
[TD="align: right"]Mon 28[/TD]
[TD="align: right"]Tue 29[/TD]
[TD="align: right"]Wed 30[/TD]
[TD="align: right"]Thu 31[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]1/4/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]1/16/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]1/6/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]1/16/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]1/16/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]1/19/2019[/TD]
[TD="align: right"]1/23/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]1/7/2019[/TD]
[TD="align: right"]1/10/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=IF(AGGREGATE(14,6,($A$14:$A$20=$A2)*($B$14:$B$20<=B$1)*($C$14:$C$20>=B$1),1),"Z","W")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Copy the formula from on B2:AF7

Thank you so much... I'm actually mind blown that you made this work with such simple code, I will have to do some research on AGGREGATE because I was trying to create a VBA module to add functions to existing formula but this is SO much simpler. I am still blown away by this, the only problem now is that the list with the reoccurring names and dates will keep expanding so that table will continuously be getting larger, is there a way to alter the formula such that when the table size increases, so does the ratio in the formula?

 

[cloud2828]

Also, instead of "Z" I need it write the value in the "Desc" column when true e.g, A, B, C, etc.

 

[cloud2828]

Also, instead of "Z" I need it write the value in the "Desc" column when true e.g, A, B, C, etc.

This might make things a lot more complicated maybe, because Name1 can have different descriptions for different days and same for the other Names.

 

[Aryatect]

Yeah, below formula will have to be used for getting description:

Book1
	B
2	ABC
Sheet1

Cell Formulas
Range	Formula
B2	{=IFERROR(INDEX($D$14:$D$20,SMALL(IF(($A$14:$A$20=$A2)*($B$14:$B$20<=B$1)*($C$14:$C$20>=B$1),ROW($D$14:$D$20)-ROW($D$14)+1),1)),"W")}
Press CTRL+SHIFT+ENTER to enter array formulas.