Help with 2 dimensional Array of table for range intersect etc

[wotadude]

My understanding of arrays is poor.
In basic terms my requirement is as follows -
I have a table of data (refer Sheet(“Data”) of attached example.
Row 1 of this table is the default values of the Data Template (this row will be hidden to the user inputting the data).
The user will populate data in the data template (via cell validation drop-down selection etc).
The actual data template will have approx 160 columns, by 1 – 5000 rows approx.
The data template will contain hidden columns (fields) that do not relate to the specific task scenario (all shown here, but actual file will have various columns not applicable to the scenario hidden). So In reality approx 6-50 columns will be unhidden for data entry etc.

Each column is assigned a named range to identify it in row 2
eg. Cell A2 (Status) named range is “Status_Col”
Cell D2 (Model) named range is “Data_Col_3”

Each named range (from Col D onwards) is referenced with an “Owner”.
Refer Sheet(“Assignment”). Where each column title (Col A) has a named range listed in Col B, with the related Owner in Col C.

Each Owner is assigned an email address (refer Sheet(“Email Addr”)
The plan is to have these owner/email addresses declared as Constants in VBA
Eg. strEmailEngineering = [email protected]
Approx 10 owners will exist (5 only exampled here)

What I require is to loop through all cells within named range = “Dataset” (D$:J9 in this example) and in an array store the range for a given owner of all cells where cell value in given (visible) column differs from the default value for that column where “Status” column value = “Include”.
So this example the array would identify the following –
Owner (1) = Human Resources Range (1) = E4, E6
Owner (2) = Engineering Range (2) = F4, J4, F6
Owner (3) = Health & Safety Range (3) = H4, H5

Owner = Laboratory would be omitted from the array as no cells values for that owner column (“HP”) differ from the default value (“Select”)

With the Array I then want to loop through each Owner captured and display only columns and rows applicable (that intersect with the Owner range (as per array for that owner).
So displaying only the applicable rows with Status = “Include” with columns for that Owner where at least 1 row value differs from the default.
And repeat for all owners captured.
I am looking for a fast solution so, if it is quicker to read the dataset into an array, then process as above to achieve the same result all the better.

I will then email the workbook to that Owner (as per email address for that Owner)
There is a lot more to it than stated here, but I think I can manage the rest.
Your help is much appreciated.

[DonkeyOte]

First off, an excellent, thoroughly detailed and coherent question. Often the greatest challenge of all is to get a complex scenario across in the first instance - I feel you achieved that here...

The below will I *think* get you close to what you want but it's highly unlikely to do everything given this was a dummy file and the real version is likely to be far more complex... anyway I hope it helps to some extent.

Please Login or Register  to view this content.

[wotadude]

That gets me well down the path & plenty of ideas.
Some of which i dont yet fully understand, but will as I adapt it for my working file.
Thanks Donkeyote.