Excel VBA VLOOKUP, INDEX, dget, IF, Database

I'm managing 24 guards on three guardteams, Team 1, 2 and 3.

There are four locations, locations A, B, C, D, each occupied by a single guard.

Common to the four guards is; That they can not be shared between two employees

Location A starts and ends at 8 am, location B starts at 8am and ends at 2 pm (the day after), location C starts at 10 am and ends at 2 pm (the day after) and location D starts at 12 pm and ends at 4 pm (the day after)

The three teams will thus turn: Day 1 coming on duty, Day 2 coming of duty, Day 3 day off, Day 1 coming on duty, and so on and so forth.

Their schedule is fixed four months at a time with date in rows and their names in column (Sheet1-4, with month's denomination).

Based on date and name, each person can see when he or she is assigned to a location.

issues:

1: I need it possible, in another sheet (Ark5), to enter the employees ID number, thus displaying the chronological order of location duties, that the employee is assigned for one month at a time.

The requirement for the solution is as follows:

- The schedule should appear in rows instead of columns, as in the example in Ark5.

- Date, location and time of the relevant location must appear in each column.

The example shows the desired end result, which I have manually entered.

2: When the locations are to be roasted, weekends, number of duties and type of location must be taken into account equally. Is it possible to set up a function that, on the basis of the four locations, automatically fills in the optimal solution for how the four locations should be allocated to the 8 employees? I am thinking that it is necessary to indicate some variables, such as holiday, times of weekends, number of days a month etc.