Creating a Leave Tracker/Attendance Tracker

cloud2828

New Member
Joined
Jul 16, 2019
Messages
43
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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
To put it simply, I will give an example, looking at the formula below:

=MATCH(AL38;AL$38:AL49;0)

This gives the table as shown:
Name101
Name112
Name112
Name134
Name101
Name146
Name146
Name178
Name189
Name1910
Name1511
Name101

<colgroup><col><col></colgroup><tbody>
</tbody>


What I am trying to achieve is that each Match will result in the actual row of the duplicates and not just the first time it sees it in order to create what I am required to.
 
Upvote 0
Hi,

will below type of output serve your purpose:


Book1
ABCDE
1NameNumberNamesRows
2Name101Name101/5/12
3Name112Name146/7
4Name112
5Name134
6Name101
7Name146
8Name146
9Name178
10Name189
11Name1910
12Name1511
13Name101
Sheet2
Cell Formulas
RangeFormula
E2{=TEXTJOIN("/",TRUE,IF((D2=$A$2:$A$13)*(ROW($A$2:$A$13)-ROW($A$2)+1),ROW($A$2:$A$13)-ROW($A$2)+1,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Hi,

will below type of output serve your purpose:

ABCDE
1NameNumberNamesRows
2Name101Name101/5/12
3Name112Name146/7
4Name112
5Name134
6Name101
7Name146
8Name146
9Name178
10Name189
11Name1910
12Name1511
13Name101

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

Array Formulas
CellFormula
E2{=TEXTJOIN("/",TRUE,IF((D2=$A$2:$A$13)*(ROW($A$2:$A$13)-ROW($A$2)+1),ROW($A$2:$A$13)-ROW($A$2)+1,""))}

<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>
I wrote this back on a new sheet exactly how you have it and it says "#NAME?" where the rows should be listed. Also, this somewhat does what I want, I want to be able to apply this to another formula, something like an incrementor of some sorts. Basically if the result can be written back exactly how it is in the B column but with the correct matched rows.
 
Upvote 0
Hi,

will below type of output serve your purpose:

ABCDE
1NameNumberNamesRows
2Name101Name101/5/12
3Name112Name146/7
4Name112
5Name134
6Name101
7Name146
8Name146
9Name178
10Name189
11Name1910
12Name1511
13Name101

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

Array Formulas
CellFormula
E2{=TEXTJOIN("/",TRUE,IF((D2=$A$2:$A$13)*(ROW($A$2:$A$13)-ROW($A$2)+1),ROW($A$2:$A$13)-ROW($A$2)+1,""))}

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

I figured out why I won't get a result, my version of excel does not have "TEXTJOIN" function.
 
Upvote 0
I know this may not be possible but if the actual match result can change to the row it's on that would make my life much easier, well basically what i'm saying is that I don't want it as a text because I have to use these numbers in another formula because which is the index. Reposting the code from the first post:

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

if "
MATCH($AJ29;Table1[Column1];0)" can be replaced by something that will actually give the rows of the duplicates then my job will be done.
 
Upvote 0
How about this:


Book1
ABCDEFG
1NameNumberNamesRows
2Name101Name101512
3Name112Name1467
4Name112
5Name134
6Name101
7Name146
8Name146
9Name178
10Name189
11Name1910
12Name1511
13Name101
Sheet2
Cell Formulas
RangeFormula
E2=IFERROR(AGGREGATE(15,6,(ROW($A$2:$A$13)-ROW($A$2)+1)/($D2=$A$2:$A$13),COLUMNS($E2:E2)),"")


Copy across E2:I3
 
Upvote 0
How about this:

ABCDEFG
1NameNumberNamesRows
2Name101Name101512
3Name112Name1467
4Name112
5Name134
6Name101
7Name146
8Name146
9Name178
10Name189
11Name1910
12Name1511
13Name101

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

Worksheet Formulas
CellFormula
E2=IFERROR(AGGREGATE(15,6,(ROW($A$2:$A$13)-ROW($A$2)+1)/($D2=$A$2:$A$13),COLUMNS($E2:E2)),"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Copy across E2:I3


Thank you very much, it works, now I need to figure out how I can put this information into an index, any ideas?
 
Upvote 0
How about this:

ABCDEFG
1NameNumberNamesRows
2Name101Name101512
3Name112Name1467
4Name112
5Name134
6Name101
7Name146
8Name146
9Name178
10Name189
11Name1910
12Name1511
13Name101

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

Worksheet Formulas
CellFormula
E2=IFERROR(AGGREGATE(15,6,(ROW($A$2:$A$13)-ROW($A$2)+1)/($D2=$A$2:$A$13),COLUMNS($E2:E2)),"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Copy across E2:I3

So I have an idea to make this work but it will require me being able to place the row results on a separate sheet instead, would that be possible?
 
Upvote 0
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:


Book1
ABCDEFG
1NamesRows
2Name101512
3Name1467
Sheet3
Cell Formulas
RangeFormula
B2=IFERROR(AGGREGATE(15,6,(ROW(Sheet2!$A$2:$A$13)-ROW(Sheet2!$A$2)+1)/($A2=Sheet2!$A$2:$A$13),COLUMNS(B2:$B2)),"")
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top