find MIN and MAX dates based on rate criteria in another cell

[mdeshazo]

First - I have been using ther board for 6 years. And this online community is SO AWESOME at what ya'll do I have not had to ask a single question of my own - until now.

I have a spreadsheet in which I must keep the VBA to a minimum, so most of solutions thus far have been formulas. And while I am partial to formulaic solutions, I am quite open to a VBA solution as well.

So here's the general set-up:
Using the wonderful formulas from this message board I have developed a summary "table" of data from two other tables. The two other tables are esssentially identical, just the way the data comes in is different (and would be difficult to change.)

Here's a simple version of the two tables (both on one worksheet) that start the mess:
TBL 1 (comes in through a database query)
| I | J | K | L |
R# |rate | st date | end date | amt |
100 |.55 | 8/16/08 | 8/15/09 | 150 |
101 |.26 | 8/16/07 | 8/15/08 | 300 |
102 |.26 | 8/16/06 | 8/15/07 | 500 |

TBL 2 (manually entered by user)
| I | J | K | L
R# |rate | st date | end date | amt |
108 |.26 | 9/1/06 | 8/30/07 | 100 |
109 |.26 | 9/1/07 | 8/30/08 | 250 |
110 |.55 | 9/1/08 | 8/30/09 | 600 |

I have managed to develop a summary table that gives me the following
rate | Total |
.26 | 450 |
.55 | 500 |
.26 | 350 |
.55 | 600 |

What I'd like to add to the table above is a formula that calculates the max to min date range from the data provided based on the rate; thus showing a final result that looks like this:

rate | Total | st date | end date |
.26 | 450 | 8/16/06 | 8/15/08 |
.55 | 500 | 8/16/08 | 8/15/09 |
.26 | 350 | 9/1/06 | 8/30/08 |
.55 | 600 | 9/1/08 | 8/30/09 |

Some notes:
- the two start tables can each have addtional rows (sometimnes up to 6 rows each)
- dates of rate changes can happen at any time
- dates of rate changes usually do not overlap, one rate for one period, a new rate for a new period.

Hopefully this is enought to go on, but if not, please feel to contact me back.

Looking forward to another elegant solution,
Michael D.

 

[Special-K99]

How do you get 450 on the .26 summary based on the tables you've supplied?
I can see how you might get 350.
Similarly how did you get 500 on the .55 summary table?

 

[mdeshazo]

That's just a silly math error on my part. Looking at so many numbers you eyes cross.
The summary tables first two lines (in both examples) should be:
rate | Total |
.26 | 800 |
.55 | 150 |
.26 | 350 |
.55 | 600 |

However, the critical piece on this is to get it to provide me with the dates related to the ranges.