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.

 

[Aryatect]

It is a multiplication operator in excel as in 3*4=12, here it is used to multiply 2 or more arrays. You are right that as per Booleon algebra multiplication acts as AND operator

 

[cloud2828]

Also I've rewritten the code such that:

=IF(OR(WEEKDAY(C16;2)>5;COUNTIF(Input!$J$2:$J$19;C16)>0);"";IFERROR(INDEX(Table15[DESCRIPTION];SMALL(IF((Table15[EMPLOYEE NAME]=$C$5)*(Table15[DESCRIPTION]<>"T")*(Table15[DESCRIPTION]<>"O")*(Table15[DESCRIPTION]<>"P")*(Table15[DESCRIPTION]<>"S")*(Table15[DESCRIPTION]<>"Ts")*(Table15[DESCRIPTION]<>"Os")*(Table15[DESCRIPTION]<>"Ps")*(Table15[DESCRIPTION]<>"Ss")*(Table15[DESCRIPTION]<>"Ap")*(Table15[DESCRIPTION]<>"Ar")*(Table15[START DATE]<=C16)*(Table15[END DATE]>=C16);ROW(Table15[DESCRIPTION])-ROW(Input!$D$2)+1);1));""))

You'll notice I added several conditions with the asterisk, it works but it isn't quite what I want, It completely blanks out the letter in the calendar but what I actually want is a way to have priority such that if someone puts "A" in the table later down the line, it will overwrite the "P"s, "O"s etc. IF it falls within the date range. What this current code does is stop these particular letters from showing up. Also, I would like to know if there is an easier way to do this without writing out each letter individually.

Edit: How I did this before was use sort but that method takes a while and having to press "Reapply" everytime proves to be an issue when working with this excel workbook.