The purpose with this procedure is based on an employee list with daily records of the type of days that the employee is not working to generate a list that for each employee and each type of worktype to generate a list with the following 7 columns:

employeenumber, employeename, socialsecuritynumber, worktype, first day off, last day off, number of consecutive days

This is solved as I copy columns B to E from worksheet "Sheet2" to the worksheet "Ark1", then I remove all duplicate records from Worksheet "Ark1". this is followed by inserting two array formulas in column E and F to find the earliest and latest dates for each combination of employee and worktype.

Then I insert a calculation of number of days and a calculation of the consecutive workdays.

This all works well.

Here is the problem that I have to solve. because once in a while an employee has three days of vacation (ferie in Danish),

but those days are separated from each other. I need to create a new row, that is inserted everytime the number of days are not consecutive (see line 17 and 18 for an example) and then I need to have a start and end date for that second (third and fourth period) as well.

I have tried a bit with the small function instead of the Min function, which might Work, but I am stuck, as I need the VBA to recognize that this is a new record, and therefore a new line.

regards

Jørgen

Experts-Exchange-Repeats.xlsm
It is in Module 2 in the workbook.

Press the button to run the macro.

Open in new window

Experts-Exchange-Repeats-A.xlsm