• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find overlapping numbers among multiple data ranges

Z

zargod

New Member
I have been working to find this solution for about 3 weeks now, I am close but I am stuck on one last piece.


Please refer to this link for a copy of the sheet.


http://dl.dropbox.com/u/2421006/Example%20Sheet.xlsx


The top is the solution (done manually) and the bottom is the starting point.


I have several overlapping ranges of data of which I need to find the highest number of overlaps, going left to right. Once the optimal point has been identified, all other numbers need to be turned into zero other then the point of highest overlap.


If there are to consecutive points that satisfy this criteria, then the left most will be used. For example, M26 and N26 both have a value of 4, but M26 will be used.


I have an idea of how to do this, but now sure exactly how to implement it. For each range, say RED, go along each value in the SUM row at the bottom, find the highest value (if more then one, use left most). Once identified, turn all other values into 0.


This is just my idea of how to do it, is this possible? Are there better and simpler solutions to this? I am a very visual person which is why I have structured it this way, but if pure math can do it that works for me as well.
 
Hi zargod,


Firstly welcome to the forum, I have worked out a file, the first step for the sheet here it is:

http://dl.dropbox.com/u/60644346/Copy%20of%20Example%20Sheet.xlsx


The criteria for calculating 'Best' is still unclear, can you please elaborate taking up an example for the sheet? How are you calculating the over lapping fields? To me any value in the range that totals under each value that is great then zero represents an overlap? What do you mean by the best overlap? If you mean 'best' by greatest no. of fields overlapping then only '4' should have been the best overlap? Any clues over it?


Regards,

Faseeh
 
Thanks for the quick reply Faseeh. The ranges are calculated using the data from the left most two columns, I have placed those ranges as colored bars manually, but will be using a formula once I figure out the solution to my overlapping problem.


Overlapping is defined as the rows having the most 1s, which leads to the biggest number in the sum row below. Starting left to right, M12 is the first greatest overlap area. Once that has been determined, all point values to the right will turn to zero, basically removing the rest of the range from the data.


I am using this to calculate shipping dates within a date range. Each of those bars is a window where an object can be shipped. Once the best(most overlaps aka most objects shipped that date) date has been chosen, M12, the window for that range "closes" and removes those ranges from future consideration, which I am accomplishing by turning the 1s to the right of the chosen overlap to zero. We then move past M12 and look for the next "best" overlap number. The next best is O12, since there are no other overlaps, the first of the range is considered as the ship date. The next best is T12, since there are 2 overlaps, and the last is Y12 which has no overlaps so defaults to the first date in the range.


I have put a formula in the BEST cells that accurately show what is the best point once zeros are added into the ranges.


The main purpose of this sheet is to minimize the number of shipments over a range of dates.
 
Ok Zargod, I have understood the points but will have to work it out on PC... My local times are 2 am so now am going to sleep! Will work it out tomorow morning.... ;)


Regards,

Faseeh
 
Hi zargod,


Have tried hard to understand 'overlapping' issue but am still struggling.. However i have understood your criteria for 'Best' and used it to create this sheet:


http://dl.dropbox.com/u/60644346/Copy%20of%20Example%20Sheet.xlsx


Every thing is OK except the two yellow boxes in your sample sheet that are marked 'Best' in your final table? Really can't understand the logic for these two boxes? Can you explain by taking step by step example, not just 'first best' and the 'second best'.


Regards,

Faseeh
 
You must log in or register to reply here.
Back
Top