Retrieving Data from a Complex Range

[Skojster]

Hi,

What I'm trying to do is extracting data from a complex range based on several conditions. To bastardize the concept, I'll use a grocery store inventory.

My first column (A) has the type (e.g. 'FRU' for Fruits, 'VEG' for Vegetables, etc). These are repeated consecutively for every product falling into that category (for instance, B4 'Banana' with A4 'FRU', B5 'Apple' with A5 'FRU', B6 'Broccoli' with A6' VEG', etc). My first row (3) has my area (e.g. 'F' for Front, 'C' for Center, 'CA' for Cash', etc). These are repeated for every store (for instance, the New York shop is a header formed of merged cells in Row 2, above every Area for that store, followed by the Los Angeles shop with the same areas, etc). The cells in the tracker located in the intersections (basically from C4 to AZ999) contain a Data Validation list (e.g. Full, Half, Restock, Discontinued; represented in Tables by their first letter in caps) which is also going to be pulled in the reports. Several sheets are laid out the same way, each pertaining to a different Country. Example of a Page:

___|______||__New York__||___Boston___||_Los Angeles_||____Miami____||
___|______|| F | H | R | D || F | H | R | D || F | H | R | D || F | H | R | D ||
___|Fruits__________________________________________________________
FRU|Apple _||__| __|__| __||__| __|__| __||__| __|__| __||__| __|__| __||
FRU|Banana||__| __|__| __||__| __|__| __||__| __|__| __||__| __|__| __||
FRU|Orange||__| __|__| __||__| __|__| __||__| __|__| __||__| __|__| __||
___|Vegetables_____________________________________________________
VEG|Carrot ||__| __|__| __||__| __|__| __||__| __|__| __||__| __|__| __||
VEG|Pepper||__| __|__| __||__| __|__| __||__| __|__| __||__| __|__| __||
___ |Meat_________________________________________________________
MEA|Beef__||__| __|__| __||__| __|__| __||__| __|__| __||__| __|__| __||
MEA|Pork__||__| __|__| __||__| __|__| __||__| __|__| __||__| __|__| __||

The report tables are divided in the following way. I've got one table per location (e.g. Front, Center, etc), with the Row header identifying Product Types from Column A, and repeated Column headers representing all validations types per Sheet found across the tables, with merges headers representing Sheets (Countries). Basically:

__________||____USA_____||___Canada___||___Mexico___||
|___TYPE__|| F | H | R | D || F | H | R | D || F | H |R | D ||__TOTAL__|
|Fruits_____||__|__ |__| __||__|__ |__| __||__|__|__| __||__________|
|Vegetables ||__|__ |__| __||__|__ |__| __||__|__|__| __||__________|
|Meat _____||__|__ |__| __||__|__ |__| __||__|__|__| __||__________|
|TOTAL____||__|__ |__| __||__|__ |__| __||__|__|__| __||__________|
|%________||__|__ |__| __||__|__ |__| __||__|__|__| __||_____F_____

As you can see in this draft, at the lower-right there's an 'F' value - this is to represent that it's looking for all 'Front' areas (as there is one table per area). So what I need to have this table do is the following: A) Find the page based on the Country name, B) Find the Rows (consecutive) that apply to the Type, C) Find all the Columns (spread) corresponding to the Area and D) compare the values in these zones with the statuses (in the table, the letters under the countries).

It felt easy before I realized that Sumproduct doesn't like the combination of lookups. I've made complex searches before, but I'm trying to see if there's a better solution here than to find for every result (there are going to be 4 tables, at least 7 Countries and a growing number of Types, currently about dozen) based on set areas (would require so much lookup that it would probably have the tracker lag).

If anyone has a solution for this it would be extremely appreciated as these tables have given me many headaches at work since my trackers became complicated (values have been changed to represent a grocery store but are actually about important client and result data). Also, usually my solutions eat up a lot of memory, so if someone can provide a lighter one it would definitely be helpful.

Cheers!

 

[sijpie]

I guess at some point it becomes more efficient to start doing this in a real database....

I still don't understand what you mean with 'Front' Center' 'Cash' as this does not seem to appear in the example you have drawn up.


I also don't understand what is to come in the report fields. How is this status comparison done and what then is the result?

 

[Skojster]

I'm aware; unfortunately what we have to work with at the moment is Excel =[

These areas (Front, Center, etc) are reported as 1 table each and are reported as their first refer in caps for lookup purposes in the lower-right corner of the report tables.

Basically, each results table will lookup the type in the DB (e.g. FRU), then every column with the matching Area (the letter in the bottom-right corner, in this case F for Front) from the page (Country) indicated in the merged header, and will count the instances of results (F, H, R, D) in each of these complex fields. The results are the amount of entries representing these letters.

 

[Skojster]

I see what really doesn't make sense. When transposing values into new ones I messed up with the headers. It should look like this:

___|______||__New York__||___Boston___||_Los Angeles_||____Miami____||
___|______|| F | C | S | B || F | C | S | B || F | C | S | B || F | C | S | B ||
___|Fruits__________________________________________________________
FRU|Apple _||__| __|__| __||__| __|__| __||__| __|__| __||__| __|__| __||
FRU|Banana||__| __|__| __||__| __|__| __||__| __|__| __||__| __|__| __||
FRU|Orange||__| __|__| __||__| __|__| __||__| __|__| __||__| __|__| __||
___|Vegetables_____________________________________________________
VEG|Carrot ||__| __|__| __||__| __|__| __||__| __|__| __||__| __|__| __||
VEG|Pepper||__| __|__| __||__| __|__| __||__| __|__| __||__| __|__| __||
___ |Meat_________________________________________________________
MEA|Beef__||__| __|__| __||__| __|__| __||__| __|__| __||__| __|__| __||
MEA|Pork__||__| __|__| __||__| __|__| __||__| __|__| __||__| __|__| __||

Basically, the headers below Cities should be the areas (F for Front, C for Center, S for Cash, B for Back) and every |__| cell can be filled with data validation of their status (F for Full, H for Half, R for Restock, D for Discontinued).

 

[sijpie]

Having thought some more about this, I think the simplest solution is to add four columns before the city name columns start. Also with FHRD. But these cells contain a formula to count the number of FHRDs in that row.
Then the cells behiind the main headers (Fruit, Veg, Meat etc) contain the sum of the rows below.

Now in your summary sheet you only need to add these last numbers.

 

[sijpie]

I forgot to say that these extra four columns can then be hidden

 

[Skojster]

Hm, that adds calculations, but is probably the best quick way to sort this problem efficiently. Thanks a lot for the pointer =]