Return array based on multiple criteria

**kmacd** · 01-05-2011, 05:52 PM

Hi all,

I'm hoping someone can point me in the right direction on this, I feel as though the answer should be obvious...

I have a worksheet with data in multiple columns (flat-file database structure, each column is a field and each row is a record):

Name | Location | Date | Data1 | Data2 | Data3

(I'm using | to indicate a column break in this example.)

For example:

domain.com | All Canada | Nov-10 | 11123 | 22123 | 33123
example.com | All Canada | Nov-10 | 10321 | 57321 | 29819
another.com | All Canada | Nov-10 | 47123 | 81723 | 19283
onemore.com | Ontario | Nov-10 | 12823 | 123945 | 12362
again.com | Ontario | Nov-10 | 72839 | 81937 | 9135
lastone.com | Ontario | Nov-10 | 92834 | 93241 | 56321

All columns are unsorted. The Name and Date columns have values that repeat. The Location column has two possible values, 'All Canada' or 'Ontario'. There are no blank cells - each row has values in every column.

The 'unique key' is a combination of Name, Location & Date columns: there is only one row in the worksheet containing 'domain.com | All Canada | Nov-10'. (In this example, there may also be a row for 'domain.com | Ontario | Nov-10', which is also a unique record/row on the worksheet.)

I have data going back a couple of years and the number of rows in the worksheet grows each month as I add in the data set (records) for that month. Also, the number of 'names' in the set (number of records/rows) for each month varies month to month, for example: 10 rows of data for Oct-10, 12 rows for Nov-10, 13 rows for Dec-10 etc.

I've set up dynamic named ranges for the data (one for each column, which automatically includes as many rows as have data in that column): Date Location Media Data1 Data2 Data3.

What I'm trying to do is return an array of all the Names available for a given month and location. For example, if 'All Canada' and 'Nov-10' are the criteria, the returned array should be {domain.com, example.com, another.com}. If 'Ontario' and 'Nov-10' are the criteria, the returned array should be {onemore.com, again.com, lastone.com}.

I'm already using SUMPRODUCT in the workbook to return specific data values from the data worksheet based on multiple criteria:

=SUMPRODUCT(--(Media="domain.com"),--(Location="All Canada"),--(Date="Nov-10"),--(Data3))

This example returns 33123.

Great for returning a single value, however I need to return an array of values. I just can't figure out how to build an array including all the records available for a month and a location. I don't want to use VBA and I intend the formula to be a named range itself: AvailNames={array returned by formula}.

I've tried a few things like:

=SUMPRODUCT(--(Location="All Canada"),--(Date="Nov-10"),--(Media))

but that throws a #VALUE error.

I hope someone can point me in the right direction. I'm stumped!

Thanks,
Keith

**MarvinP** · 01-05-2011, 06:11 PM

Hi Keith,

Because you have your data in a table, have you tried AutoFilter? Also the "Advanced Filter" function of Excel may also do what you're looking for. I'm a pivot table advocate and believe they would also do what you want.

Posting an example file is the best way for us to help if the suggestion doesn't make sense.
AND - welcome to the forum!!

**kmacd** · 01-05-2011, 08:23 PM

Hi Marvin,

Thanks for the welcome and thanks for your reply! I'm a big fan of Pivot Tables too but filtering doesn't meet my needs in this case. I should have also said that I have the raw data in a data sheet, and need the array to return a sub-set of the raw data to another sheet (for doing further analysis, although I know that Pivot Tables can handle pulling data across sheets).

This is a monthly report I produce and ideally I'm looking for a solution where I dump in the new data and Excel does the rest (in effect I'm building a sort-of custom Pivot Table I guess!).

The part that's stumping me is that I don't want to operate (count, sum, average etc.) on the values that meet my conditions, I want to return the values of those cells. That and trying to plug in an array formula as the reference for a named range (I'm not sure if that's possible or not).

I can dummy up a sample workbook if need be, I wanted to give a try first and see what others came up with.

Thanks again!
Keith

**MarvinP** · 01-05-2011, 09:05 PM

Keith,

It still looks like an Advanced Filter problem to me. No calculation is needed, like in Pivot Tables. If you post a sample I'd understand the requirements a little better.