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]

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

	B
2	ABC

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell 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")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Works like a charm, thank you very much again!! I'm still unsure on how to make the table where you input the dates dynamic, right now it's fixed to A/B/C/D14:A/B/C/D20 but if I want to add row/s to this table to input more data, it would not recognize it, what I was thinking to do is to just create a sufficient enough table size such that the user would never run out of space, or do you have a better solution?

 

[cloud2828]

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

	B
2	ABC

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell 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")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Also, if it is not a problem, may you explain how this formula works, I'm trying to wrap my head around it but it just seems a bit too complicated for me to watch and understand.

 

[Aryatect]

Hi,

$A$14:$A$20=$A2)*($B$14:$B$20<=B$1)*($C$14:$C$20>=B$1) - Finds if the date and name true or false. This gives {1;0;0;0;0;0;0} for B2

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 - This gives the row number where the data is located where dates and description is given. This gives {1;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE} for B2

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) - Gives the minimum row number and delivers 1 for B2

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)) - This looks at the Desc column and fetches Desc from correct row. This gives "ABC" (description value for that row)

IFERROR - where there is no match INDEX gives error so checks that error and deliveries "W".


Just 1 thing, you dont need to quote the entire conversation for a reply, if you want to highlight something from previous conversation just highlight that and use it only for quotation.

 

[cloud2828]

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 - This gives the row number where the data is located where dates and description is given. This gives {1;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE} for B2

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) - Gives the minimum row number and delivers 1 for B2

These are the two parts I don't quite understand, I put "ROW($D$14:$D$20)" and "ROW($D$14)+1" in 2 separate cells and the value I got was 20 for both, wouldn't that just mean the value is 0? Can you explain what each of these formula is supposed to display for a particular example. For the second formula, what is the purpose of knowing the minimum row? Also I still don't know how I can make the table dynamic.

Edit: I was messing around with the formulas and I realized it automatically updates the table range so it's already dynamic, nevermind about that.

 

[Aryatect]

ROW($D$14:$D$20)-ROW($D$14)+1 (this [value_if_true] for IF statement)- this just delivers the row numbers for index we need to row in reference to the table so this gets {1;2;3;4;5;6;7} in this example. So then IF is applied the formula works like IF({1;0;0;0;0;0;0},{1;2;3;4;5;6;7}) for a zero if goes to FALSE. So this gives the output as {1;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

This is again array just putting it in a cell will get you only 1 value.

 

[cloud2828]

ROW($D$14:$D$20)-ROW($D$14)+1 (this [value_if_true] for IF statement)- this just delivers the row numbers for index we need to row in reference to the table so this gets {1;2;3;4;5;6;7} in this example. So then IF is applied the formula works like IF({1;0;0;0;0;0;0},{1;2;3;4;5;6;7}) for a zero if goes to FALSE. So this gives the output as {1;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

This is again array just putting it in a cell will get you only 1 value.

Okay, I understand everything now, thank you for everything! Truly helpful and such a simple formula, I really was overthinking everything.

 

[Aryatect]

Glad could help and thank you for the feedback!

 

[cloud2828]

Hi,

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 - This gives the row number where the data is located where dates and description is given. This gives {1;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE} for B2

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) - Gives the minimum row number and delivers 1 for B2

I was looking over these two parts and I realized I don't quite understand what you did, what I don't understand is that index first takes the table, then row, then column, but you use index to find rows twice? I'm definitely mistaken here but that is what I'm seeing. Let's say for example:

ROW($D$14:$D$20)-ROW($D$14)+1)
Where $D$14:$D$20 is at its first value, that will mean the result will be 1 and the column chosen for the index will be 1, being the name column? I apologize for keeping this discussion up longer but I'm really trying to get a good grasp of excel formulas. It is extremely different from programming languages, the logic is not very easy to understand.

 

[Aryatect]

So these are array formula, try enter =ROW($D$14:$D$20)-ROW($D$14)+1 and hit on F9 key you will get {1;2;3;4;5;6;7} not just 1 and that array is being utilized in the formula. (Do remember to undo after you F9 else the value output will get hardcoded.

If you want to understand more on array formulas please check out MrExcel or excelisfun channel.

 

[cloud2828]

So these are array formula, try enter =ROW($D$14:$D$20)-ROW($D$14)+1 and hit on F9 key you will get {1;2;3;4;5;6;7} not just 1 and that array is being utilized in the formula. (Do remember to undo after you F9 else the value output will get hardcoded.

If you want to understand more on array formulas please check out MrExcel or excelisfun channel.

Reviving an old thread here, I've been trying to figure out what the asterisk does in the code, I can't seem to find it on google but from what I understand it's acting like an AND operator is that correct?