I come again to ask for help for the following problem:
- I have a set of tasks that have a start date for the task and an end date for the task;
- For this set of tasks I needed to know, by VBA, in an orderly way, which periods (date range) where there are more overlapping tasks;
- In a first stage, the ordering of the data will be made, for the largest number of overlaps and within those of the one with the greatest overlapping days, that is, for periods with the same number of overlaps, the first is what has more days.
I say that it is in a first phase since in fact the ordering will be done by calculating a new "WEIGHT" that will be equal to WEIGHT = A1 * D1 * A1 / 3, and the ordering will be from the highest to the lowest WEIGHT, as you can see in the data on the right in the “OUTPUT” tab.
In the file I attach, I have a set of data, tasks and dates, and the expected results, number of overlapping tasks, date range and number of days.
All help will be welcome
Could you explain how the various tasks are identified ...
What is the relationship between Tasks # and Nº of Tasks ...?
There is no relationship between the different tasks.
They can be names, numbers, etc., without any relation.
There is no relationship between Task # and Nº of Tasks. It was just a way of presenting the data.
Any help would be most welcome.
Any tip on this?
Very sorry ... but read again your initial message several times ...
and, to me, at least, it is totally ... incompreensível ...
May be with an illustration of one single instance of what you are calling Overlapping between :
1) the reference Start Date and End Date
2) a specific task and its respective Start Date and End Date
See if the example that I attach enlightens you about what I want?
Thanks for your sample file
What is better than one example ...?
Two examples ...
Attached is your test file
Hope this will help
Once you have tested the UDF ... feel free to share your comments ...
By the way, did you get an answer in your CROSS POST ...?
Thanks for your Like
The answer to your question is no, I did not receive an answer on my identified cross post!
As for your solution, I would say that it almost answers what I need.
It just doesn't answer because what I need, as I say in the post, is that the final result should be prioritized not by the number of overlapping tasks, but by the calculation that I refer to and then the sort will have to be sorted by the largest number as shown in the column "N ".
It turns out that in the end I just need to show between the first three to five results of the calculation, so my question is whether this is possible to do and how, with arrays, because that way I stayed with all the calculations but only output the data to, the spreadsheets, with the values I want?
Thank you very much for your help
Thanks for your Thanks ...AND for your Like
Not sure to understand what is preventing you from adding the Weight column and formula ... as well as a ranking formula
see attached Version 3
Hope this will help
Thank you very much for your solution.
I will try to pass it on to VBA.
You are welcome.
Should you face a difficulty for your macro ... do not hesitate to come back to the Forum
I will certainly have many difficulties, as you can see under my Avatar I am a Beginner, the most certain thing is to drop this topic in my project.
Thank you so much again.
In you real-life workbook:
1. What is the actual structure of your Data .... ???
2. How many final results do your need ...??? ("show between the first three to five results")
Ideally, posting a sample of the expected final result would help ...
Hi again Carim
This is the layout of my real-life workbook, I just changed the names of the tasks. As you can see, this is a Gantt layout in which I have several tasks and the Start (Column C) and End (Column E) Dates for each task.
The data in this layout is not fixed, I repeat is not fixed and depends on the Department (Column L), that is, when I choose the department all values from Column A to Column K change., None of this is happening now, because I simplified this file as much as possible.
The main columns for the case in question - task overlap periods - are columns N, O and P.
Thus, 1, 2, 3 means:
1 - first period with the highest concentration of tasks
2 - second period of greatest concentration of tasks;
3 - third period of greatest concentration of tasks.
If task1 is in period 1 then column N shows a "1" if neither period 2 nor 3 appear in columns O and P then "0" appears - this has to do with conditional formatting.
It is enough that one day of the task1 duration period, falls within any period 1, 2 or 3 to appear "1" in column N, O or P, that is, it is not necessary that the start and end date of a task is all contained in either period 1,2 or 3, one day is enough.
This was what I needed .... not easy, I know!
The cherry on top of the cake, was if it were possible, instead of having "1" and "0" in columns N, O and P, it was having the number of days for the duration of each task, which are in each period 1 , 2 and 3.
I don't know if this explanation of mine is understandable - "compreensível", if you have any doubts tell me.
Thank you so much again.
Since is not possible edit my last post, here is an update.
I was seeing your solution better (initial file - TestPMC_Original-V3.xlsm), and I notice that it is not calculating the periods with the highest concentration of tasks, but only the number of tasks.
Note that the available data is only the start and end date of the tasks. What was in the OUTPUT tab was all calculated manually.
The data are in the DATA tab, columns A, B and C, what I want is the calculations that are in OUTPUT tab, columns A to F.