Map date ranges into Absenteeism grid based by ID number & date range

Dear Excel Expert,

I am looking for a way to map a list of date ranges (Leave History tab: M:N) into a grid (Leave Chart tab).

There are multiple date ranges for each member. Some with gaps, others consecutive.
Some Units (column 'O') are negative (reversals). IF the hours for a date range are NIL, then this can be ignored.

When a date range matches the ID# and the date at the top of the grid, then the code in column 'Z' should be placed in that field in the grid.

Not critical, but if a date range is overlapping, then the letter X should be placed in the date box on the grid and highlight the overlapping date ranges in the .


I have manually entered the first row of how the expected result should/could look.


Not sure if all this could be done with formulas, but I think it may be faster to run a macro as in the full data there are 10,000 rows of data.


Any help or pointers are appreciated.

Thanks in advance for your expertise.

Please Login or Register  to view this content.

Currently no check for ovrlaps but will look at adding this

Can you please add example(s) and show the expected results in "Leave Chart.

Thanks a lot John !

I was expecting a much longer, complicated code. It shows a true expert.

Your code works perfectly for the sample data.

I have added a few more sample records. The first record Anderson Aaron now also shows an overlapping record for 6&7 May 2023. I have updated the 'Leave Chart' to the expected result, showing as X.
I think it will need something like: If the destination cell is not blank and the other criteria match, then use 'X', other wise the reference letter.

I noticed that the 'Leave Chart' is populated from row 6 to row 15 only. There are no mappings below for the rest of the people although their ID# and names list correctly in columns A to C.

The full data has around 10,000 rows in the 'Leave History' sheet and around 800 rows (unique ID#) in the 'Leave Chart'.

Again, thanks for your help with this project.

Try

Please Login or Register  to view this content.

Thanks again John,

The sample data runs fine for a start date of 01/01/2023. I think the 13 Jan 23 for the first record, Anderson Aaron should also show as X. It may be because it is just a single day.
Also Jacobs Jacob has 4 entries for 2023. The chart shows all dates as X from the first date overlapping whereas only some of the dates are overlapping.

When I change the start date to 01/01/2022, I get some runtime errors 1004. First one with;
scol = 25 Jan 22
fcol = 04 Jan 22
On the 'Leave History' sheet, this related to Clarkson Carola. There are 3 entries ending on the 25th Jan (rows 41 to 43).
3 entries for the same person & date range does not seem to be an issue for others in 2023 ie; Hummer Hubert 3-13 Jan 23 & 2-3 Mar 23.

I think the overlapping date bit may be a bit too complex. An alternative would be to just highlight them in the 'Leave Records' sheet as they will need to be looked at individually anyway...

Thanks again for your time helping me with this.

See attached

Thanks again John,

We are almost there.
When running your code on the sample data works perfectly.

I then did some testing in the full data;
Running the data for 2022, the code did run without any runtime errors, but some incorrect mapping occurred.
When running for 2023, it gave some runtime errors.
I found that the errors occurred when there is a single record (row) for a person.

I have added some of these examples to the test data.
Running the code for 01/01/2022, the code executes without errors. "Average August" however has all his leave showing as X (duplicate). As he has only one record, it should not be a duplicate.
Running the code for 01/01/2023, the code hits the first runtime error '13' Type mismatch for "Buther Bruno" who has a single entry. After removing him from the Leave History and re-running the code, the next runtime error is for "Cook Cameron", again he has only one record.

Again, Thanks for your valuable help with this.

Please Login or Register  to view this content.

Thanks John,

No more runtime errors, but the records with just one line entries are ignored, not coming through.
They are highlighted in yellow on the 'Leave History' sheet in the previous file v1.3. Would you mind having a look at this?

The line: If nId = 1 Then GoTo nexti is ignoring the ID# with only one record and moving to the next one. They should still be recorded, just not as overlapping.


I appreciate your time on this.

Please Login or Register  to view this content.

Highlighted moved

Thanks John,

The correction above did bring through the labels, but not the date mapping. I moved it a bit further down to just before the 'Select case n' line.
This did the trick. All is working now as expected.

Thanks not only for your tremendous help with this, but also for the learning it gave me when trying to understand the code!

You're welcome and thank you for the rep.