Returning Multiple Rows based on Date Range

[danomite]

New to the forum, hoping you guys can help me out with a formula I can can't wrap my head around.

What I need is a formula that, based on a date range (Jan.1/09-Feb.28/09) will return any AND all results (multiple rows and columns) from the array, which will be massive with data sets having the same date. (Jan.3 Location1; Jan.3 Lcation2; Jan.3 Location4; Jan5 Location3; etc...)

This worksheet will be used for the purpose of tracking shipments made for a specific date range. Many shipments occur on the same day. I cannot use another form of data retrieval as I don't want to discriminate against any other data, except for date. vlookups don't cut it for my needs as they only return the first instance of the value it finds, disregarding much of the data I need it to return. The goal is to track the purchases made within a specific data range with the sales of a specific data range between different locations in order to determine the profit of a particular 'round'.

I had a thought to use if statements in both arrays to identify which sets of data are in the array listing the results. Along with vlookups that use these if statements to determine whether or not the data is in the array or not. I haven't even started on doing that as it will be quite the lengthy process do to many different variables and the circular references I'm sure to run into.

Hopefully someone here can help me out. If you need a worksheet to better understand what I mean just say the word.

[danomite]

I guess the closest thing I can think that produces the same results is a pivot table but it's not quite flexible enough for what I need. A pivot table will list all the sales and I can filter out the dates I don't want. The thing is I need the data in a specific order and I need to put in more formulas once I have the information from the pivot table, which is not included..

[DonkeyOte]

Post a sample file with a tab outlining expected results.

[danomite]

Here is a sample of the data I wish to manipulate. My goal is to have the 'barn #' and 'date range' fields determine which data will be retrieved from the array. When I need a different set of data I would just be able to change the barn and date range and the new information would be listed, however many rows it would be.

[DonkeyOte]

To avoid array's this is how I would approach things myself... given you're only checking one date range at a time you can set the criteria and let the data sheet create an index key for you.

[danomite]

Wow if that isn't the greatest thing ever. Thank you so much. Any chance you could put some comments in the worksheet? I really would like to understand how your formula works. I would appreciate it so much.

[DonkeyOte]

It's relatively straightforward I think given the aim is to avoid arrays.

You're criteria are set on 'Expected Results' such that:

Please Login or Register  to view this content.

The data that needs to be filtered is on sheet 'Data Set', the key data is listed as follows:

Please Login or Register  to view this content.

So in Column I on 'Data Set' (the first blank column) we add an incrementing INDEX...

Please Login or Register  to view this content.

So the value generated by the above will increment only when the conditions specified on 'Expected Results' (B3:B5) are met by the values for a given row in Col A & C on 'Data Set' sheet.

The end result of the above formula when applied across all rows on the 'Data Set' sheet is that you have an INDEX key... each step in value denotes a new record which should appear on your 'Expected Results' table.
I opt for an incrementing flag approach as opposed to a 0 for those records that don't match criteria such that you can avoid the need to conduct say an additional MAX formula over the preceding range as and when you stumble across a valid record... the fewer calcs the better


So given you know have an INDEX key on 'Data Set' you can now populate the 'Expected Results' table without need for array, the table is setup such that:

Please Login or Register  to view this content.

Given the above you can setup one formula and apply across the entire matrix using a combination of Absolute & Relative References, so the formula for the first cell in the results table:

Please Login or Register  to view this content.

So essentially where a record is required an INDEX / MATCH approach is used... where INDEX works along the lines of:

Please Login or Register  to view this content.

so for example:

Please Login or Register  to view this content.

In your case the Index range is variable for each column given in Column A you want to return a value from Column A on 'Data Set' sheet, in B you want to return Column B on 'Data Set' etc... so although the rows remain constant for each formula the column will vary as the formulae is copied across, hence:

Please Login or Register  to view this content.

You can see that $2 and $1000 ensure the rows are fixed but as the formula is copied to the right A will change to B and B to C etc...

To establish the ROW to return from the INDEX range we can (given our INDEX key method on 'Data Set') use the count of ROWS we've used thus far the 'Expected Results' Table thus far in a MATCH function against our Index key...

Please Login or Register  to view this content.

Will return 1... as the formula is copied down from A10 to A11 the return will change to 2 and say when we reach A20 the value will be 10 given A10:A20 equates to 10 rows.

So:

Please Login or Register  to view this content.

Will return the row position of "1" in our INDEX Key range on 'Data Set' sheet... this we use in the INDEX:

Please Login or Register  to view this content.

The column value for our INDEX is set to 1 given our INDEX range is only 1 column (A2:A1000) -- in this instance (1 column) the column value could be omitted altogether or set to 0, however, I always prefer to specify the value regardless.

The only other thing to add is that the 'Expected Results' table may be setup to return more rows than a given set of criteria will generate... so to avoid returning errors and to avoid conducting unnecessary calculations we hold in one cell the MAX Index key value as this tells us how many records we should be returning in the table:

Please Login or Register  to view this content.

Given you have the above value you can tweak the Table formulae such that they only fire if ROWS(A$10:A10) returns a value <= the MAX value... so if you have 5 records (as was the case in the sample file) then when you reach A16:

Please Login or Register  to view this content.

So:

Please Login or Register  to view this content.

The above says - if ROWS > MAX return a null value... only if a record will exist should you conduct the INDEX formula.

I hope the above helps some.

None of the formulae used here are particularly advanced so you will find plenty of examples of each on-line and XL Help is obviously a good resource. The way they are used in conjunction is perhaps a little more complex, as such it's always better to break down formulae into component parts and evaluate each component part separately.