How can I highlight major occurences in an array?

[Chris Mitchell]

I have a spreadsheet which is an all X all matrix/array that shows where all
occurrences of certain criteria do and don't occur.

Some of the occurrences are more significant than others and these are shown
in a separate 2 column table that could be made into a smaller some X some
array.

How can I overlay the small array over the large array and highlight the
duplicates?

Ideally I would like to use conditional formatting or similar such that the
major occurrences were Green, minor occurrences Amber and no occurrences
Red.

I have created the matrix from a pivot table in MS Access, however I know
even less about Access than I do about Excel and this is my first Pivot
Table, so pretty green all-round.

An Excel and/or Access solution to this would be appreciated.

TIA.

Chris.

 

[Jeff Boyce]

Chris

This newsgroup supports Microsoft Access, the relational database.

You'll probably get more "hits" if you post your question to an Excel
newsgroup.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Chris Mitchell]

Jeff.

I posted to both microsoft.public.excel and microsoft.public.access as I
believe that this can be done in either, but stand to be corrected.

Are you saying it can't be done in Access?

Regards.

Chris.

 

[Jeff Boyce]

Chris

Your original post referred to a spreadsheet. Although Access tables can
look like a spreadsheet, they aren't.

The way you described it, your data would NOT be in a form that would let
you/Access readily use the relationally-oriented features/functions Access
offers.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Chris Mitchell]

Jeff.

Perhaps I did not explain the position well enough.

The data for the Excel spreadsheet starts as 3 Access tables, 2 of which I
manipulate by means of a query to produce the large array; the other
contains the data for the small array.

I have further manipulated the large array in Access Pivot Table view and
exported this to Excel as a half way house and to develop my abilities.

I need to incorporate the data for the small array and if this can be done
in Access I'd welcome guidance on how to achieve this. Alternatively if it
can be done in Excel I'd be happy with that since the final article has to
be in Excel as most users don't have Access.

Hopefully the following illustrates what I'm after.

Large Array,,,,,,,,,,
,C1,C2,C3,C4,C5,C6,C7,C8,C9,C10
R1,1,0,1,1,1,0,1,1,0,1
R2,0,1,1,1,0,1,0,1,0,1
R3,1,1,0,1,0,1,1,0,1,0
R4,0,0,0,1,1,1,0,1,0,1
R5,1,1,0,1,0,1,1,1,1,0
R6,1,0,0,1,0,1,0,0,1,1
R7,0,0,1,0,0,1,0,0,0,0
R8,1,0,0,1,1,0,1,1,1,0
R9,1,0,1,1,0,1,0,1,0,0
R10,1,1,0,1,0,1,0,0,1,0

Small Array,,,,,,,,,,
,C1,C2,C3,C4,C5,C6,C7,C9,C10,
R1,1,0,1,0,0,0,0,0,1,
R2,0,1,1,0,0,0,0,0,0
R3,1,1,0,1,0,1,0,0,0
R4,0,0,0,1,1,1,0,0,1
R5,0,0,0,1,0,1,0,1,0
R6,1,0,0,0,0,0,0,1,0
R7,0,0,1,0,0,1,0,0,0
R8,0,0,0,0,1,0,0,0,0
R10,1,0,0,0,0,1,0,0,0

The above are .csv representations of the arrays but have not been aligned,
I'm sure you get the picture.

Note not all Columns and Rows that are in the large array are necessarily in
the small array.

I could do this in Excel for small array by manually aligning rows and
columns then summing the outputs for corresponding cells such that I would
have either 0, or 1, or 2, in each resultant cell then applying conditional
formatting, 0 = Red, 1 = Amber, 2 = Green.

However the array is large, approx 8000 rows by 200 columns, and not all
rows and columns necessarily appear in the small array, which may change
with successive runs, and aligning rows and columns manually would be
tedious and error prone.

I'm sure there must be an Excel formulaic/functional, or Access query means
of achieving the desired end result, but how?

Is this possible in Access?

TIA.

Chris

 

[Douglas J. Steele]

In Access, you'd probably be best using a query that compares two
subqueries.

Without knowing more about how you're trying to compare them, though, it's
pretty hard to show specifics.

 

[Chris Mitchell]

Douglas.

I do not want to compare them as such, but this might be the technical term
for what I need to do.

The large array contains all of the data.
The small array contains a subset of all of the data.
I want to be able to show/highlight the small array data within the large
array data.

If I could align the rows and columns of the small array with the
corresponding rows and columns of the large array and overlay it on the
large array then drill down I would have 2 x "1"s where the two both contain
data and 1 x "1" where only the large array contained data and "0"s where
neither contained data. I could sum the drilled down data cell by cell and
apply conditional formatting such that "0"s were Red, "1"s were Amber and
"2"s were Green. Hope this makes sense.

Thanks for your help so far.

Chris.

 

[Douglas J. Steele]

It might be possible to use Conditional Formatting if you can easily match
the rows and columns.

If not, then I don't think you'll be able to use Access (although, to be
honest, I don't see how you'd be able to do it in Excel if you can't match
them!)

 

[Chris Mitchell]

Thanks Douglas.

Does anyone have an Excel solution?

 

[Douglas J. Steele]

You're going to have to provide more details than you have.

What are the rules for matching rows and columns between the large array and
the smaller array?

 

[Chris Mitchell]

Douglas.

Thanks for your continued interest.

In both arrays Rows will be identified by a unique 6 digit number commencing
with a "5", there won't be anything else. Columns will be identified by a
unique alpha up to 9 characters long including at least one and sometimes
two "/"s.

A common set of Row and Column labels will be used by both arrays.

The small array will not contain all rows or columns found in the large
array but it will not contain any that are not found in the large array.

Individual cells will either be blank, ("0" in the examples below), or
contain a "1".

The situation where a cell in the small array will contain a "1" but the
corresponding cell in the large array is blank will not arise.

I envisage creating the arrays by creating pivot tables from separate 2
column tables, each in a separate file.

I need to align the rows and columns in the small array with their
counterparts in the large array, presumably by the insertion of blank rows
and columns as necessary taking labels from the large array.

Then, assuming that a valid intersection in both is indicated with a "1" sum
the corresponding cells in each array. The resultant will be a copy of the
large array except that where corresponding cells in both arrays contained a
"1" the resultant would now contain a "2".

I've tried to show the above in the diagrams below.

Large Array
C01 C02 C03 C04 C05 C06 C07 C08 C09 C10
R01 1 0 1 1 1 0 1 1 0 1
R02 0 1 1 1 0 1 0 1 0 1
R03 1 1 0 1 0 1 1 0 1 0
R04 0 0 0 1 1 1 0 1 0 1
R05 1 1 0 1 0 1 1 1 1 0
R06 1 0 0 1 0 1 0 0 1 1
R07 0 0 1 0 0 1 0 0 0 0
R08 1 0 0 1 1 0 1 1 1 0
R09 1 0 1 1 0 1 0 1 0 0
R10 1 1 0 1 0 1 0 0 1 0

Small Array
C02 C04 C05 C06 C08 C09 C10
R01 0 1 0 0 1 0 0
R02 1 0 0 1 1 0 1
R04 0 0 0 0 1 0 1
R05 1 1 0 0 0 0 0
R06 0 0 0 0 0 1 1
R07 0 0 0 1 0 0 0
R08 0 0 1 0 1 0 0
R09 0 1 0 0 1 0 0

Need to 'stretch' the small array to make it fit over and align with
the large array before computing the Resultant array.

Resultant Array
C01 C02 C03 C04 C05 C06 C07 C08 C09 C10
R01 1 0 1 2 1 0 1 2 0 1
R02 0 2 1 1 0 2 0 2 0 2
R03 1 1 0 1 0 1 1 0 1 0
R04 0 0 0 1 1 1 0 2 0 2
R05 1 2 0 2 0 1 1 1 1 0
R06 1 0 0 1 0 1 0 0 2 2
R07 0 0 1 0 0 2 0 0 0 0
R08 1 0 0 1 2 0 1 2 1 0
R09 1 0 1 2 0 1 0 2 0 0
R10 1 1 0 1 0 1 0 0 1 0


I've done the above manually so there might be the odd error.

The large array and resultant will be approx 8000 rows X 200 columns, so
however this is done it needs to be clever otherwise my machine will grind
to a halt.

Ideally I'd like to able to drop new files containing new versions of the
large and small arrays in place click a button and get the new result.

Hope this makes sense.

Regards.

Chris.