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.
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.
