Common denominator

**vlookup** · 05-02-2008, 12:57 PM

Let's say Mr. A, Mr. B, Mr. C, Mr. D, Mr. E, and Mr. F are trying to plan an offsite meeting. The dates listed below are when they are avialable. The important thing is that Mr. A has to attend. So, the others, need to work around his schedule. How do you write a formula, or use a pivot table, to come up with the common dates between all. Here's the example:

Name Start End
Mr. A 8/1/2008 - 8/31/2008
Mr. A 10/1/2008 - 10/31/2008
Mr. B 7/1/2008 - 12/31/2008
Mr. C 5/1/2008 - 12/31/2008
Mr. D 6/1/2008 - 11/30/2008
Mr. E 5/1/2008 - 12/31/2008
Mr. F 11/1/2008 - 12/31/2008

The answer is 8/1/08 - 8/31/08 & 10/1/08 - 10/31/08 as this the period of time when they're all available; however, Mr. F is not available.

thanks,

**oldchippy** · 05-02-2008, 06:15 PM

Hi,

May be this calendar will help?

Just insert the names and dates in the table and the calendar will turn green when each one can attend.

**vlookup** · 05-05-2008, 11:49 AM

Hi -

Thanks for your help with this. I may be able to use this as a starting point. Here's the basic problem: the example I gave is purely an illustration. What I'm trying to do really has nothing to do with meetings and/or people. I basically need to find common dates between several factors. And there's way to much information to graph it out (there can be hundreds of lines). I think I need something formulaic, and/or something that can either pivot or drill down the info. I hope this makes sense. For example, attached is the type of thing I'm dealing with. The combo of Scenario 1 and Type 1 are the most important. Everything needs to work around these two things (together). So, in the attached example, the anwser for Product 1 and Product 2 is 6/1/08 - 6/30/08, and Scenario 6 Type 2 needs to be flagged as unavailable.

**Brigitte_P** · 05-05-2008, 03:10 PM

You could do it line by line and look at the last line for your final result. By using two more columns, keep track on each line of the earliest possible date and of the latest possible date.

First line: earliest and latest dates are the ones given for the first person.
Second line: earliest date is the later of the previous earliest date and the earliest date given for the second person. Latest date is the earlier of the previous latest date and the latest date given for the second person.
Third line and up: same technique as second line.

You final range will be in the final line. If the final earliest date is a later date than your final latest date (as is the case in your file), you can conclude that you don't have a range that fits everyone. I would put the conditions in order of importance so that you can go up the list to find a range that fits as many conditions as possible.

This solution won't let you grade the conditions and come up with an optimal solution other than by that kind of absolute sorting. I don't think you'd be able to do that without some programming. It won't let you have several ranges for each line, either. This only works if you have exactly one range for each line or condition or person attending the meeting (to go back to your initial illustration).

I added the columns in your example workbook.

If you ever have to deal with several ranges for each condition, you might have to consider programming, or be brutal:
1) transpose your format so that you have one condition per column
2) assign a row to each possible date.
3) For each column, put a 0 in each date that fits the condition and a 1 in each date that doesn't. I'm not sure how to make a formula do it automatically for more than one range, so at this point, you'd have to actually manually type in the 0s and 1s.
3) sum for each date: dates that fit all conditions will have a sum of 0.

I can set it up for you in your example workbook, but I won't for now: I'd rather not spend the time on it if you don't need it. If you do need it, just ask.

Brigitte

**vlookup** · 05-06-2008, 11:30 AM

Thanks Brigitte_P. I took your sheet and made some tweaks to it I change the max and min formulas so they had absolute references to Scenario 1 Type 1, and also included if statements, so they could start and stop and the right place. Maybe a vlookup would be better? What do you think? Is it logical? Also, yes, I do have several ranges for each Product. There can be anything from 2 to 10 or more. So for example, there are be 5 of Product 1, Scenario 1, Type 1, 1 or 2 Product 1 Scenario 1, Type 2, and only 1 for each Scenario 3 Type 1, Scenario 3 Type 2, etc.

As you can see, this is a bit complex to handle, and I agree, programming, is ultimately the way to go. Unfortunately, I don't know how to program at all, and it takes me hours to deal with 500 lines or more of information. I usually end up using filters so I can atleast manage the sorts, and do the calculations manually.

I would love to see the transposed version as you mentioned. My only concern is that these reports can be quite large, and I'm wondering if it can get a bit messy.

thanks again,

**Brigitte_P** · 05-08-2008, 12:49 PM

I've put a basic example of the transposed idea in Sheet 2 of your workbook. Like I said before, it's rather brutal. The columns "Number of Conditions Not Met" tell you how many conditions were not met. It's then up to you to determine which solution is good enough by picking one where all the most important conditions are met. A good start would be to use automatic filters to filter out any row that doesn't have a 0 for the first condition of a product.

The change you made to the formula I gave you compares each range only to the first one: you completely lose any ability to determine how many of the other conditions share a common range between themselves and the first condition. All you're doing is determining, for each condition, a range that is common with the first one. You'd still have to compare those ranges to each other to satisfy as many conditions as possible.

I have modified the formula to ensure that the min date is smaller than the max date or to display a message otherwise. This will make sure that any actual range that comes out is a possible range AND that if fits all previous conditions, including the first one.

Brigitte