Hello all,
I have a complex problem of multiple product set assignment to salesmen in a given period with campaign starting date and ending date.
The data structure is as follows:
Products | Day | Product Set |
Product 1 | Sat | Set 1 |
Product 1 | Sat | Set 2 |
Product 2 | Sun | Set 3 |
Product 4 | Mon | Set 1 |
The very first task is to determine the days between the campaign start date and end date. So if the start date is 10 Sep and end date is 9 Oct 2020, there are 4 Saturdays, 4 Sundays and 4 Mondays. I have to do this manually every single time.
The next task is to determine how many product sets are there on each day, so according to the table above, on Saturdays, there are 2 sets, 2 for Sundays and 1 for Mondays. So the final table will have 8 rows for Saturdays because of 2 sets on Saturdays, 4 rows only for Sundays because of 1 set and 4 Mondays for 1 set. The rows for same date and day can increase due to sets which I have entered in above table.
The table I have to manually create looks like this:
Date | Day | Product Set | Product |
12/09/2020 | Sat | Set 1 | Product 1 |
12/09/2020 | Sat | Set 2 | Product 1 |
13/09/2020 | Sun | Set 3 | Product 2 |
14/09/2020 | Mon | Set 1 | Product 4 |
19/09/2020 | Sat | Set 1 | Product 1 |
19/09/2020 | Sat | Set 2 | Product 1 |
20/09/2020 | Sun | Set 3 | Product 2 |
21/09/2020 | Mon | Set 1 | Product 4 |
26/09/2020 | Sat | Set 1 | Product 1 |
26/09/2020 | Sat | Set 2 | Product 1 |
27/09/2020 | Sun | Set 3 | Product 2 |
28/09/2020 | Mon | Set 1 | Product 4 |
03/10/2020 | Sat | Set 1 | Product 1 |
03/10/2020 | Sat | Set 2 | Product 1 |
04/10/2020 | Sun | Set 3 | Product 2 |
05/10/2020 | Mon | Set 1 | Product 4 |
I am looking for a VBA solution, which can generate those days and the necessary rows for days with multiple sets just like above. I have also attached the excel workbook for this.
I would highly appreciate your help in achieving this difficult task
Many many thanks