How can I highlight major occurences in an array?

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. 


0
2/2/2009 3:07:32 PM
excel 39879 articles. 2 followers. Follow

8 Replies
557 Views

Similar Articles

[PageSpeed] 23

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" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in message 
news:%23mvx2fUhJHA.1248@TK2MSFTNGP03.phx.gbl...
>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.
> 


0
2/2/2009 3:59:47 PM
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

"Jeff Boyce" <nonsense@nonsense.com> wrote in message 
news:uJVFRHWhJHA.4408@TK2MSFTNGP06.phx.gbl...
> 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" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in message 
> news:%233RzmHVhJHA.3444@TK2MSFTNGP03.phx.gbl...
>> 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" <nonsense@nonsense.com> wrote in message 
>> news:O2sLI9UhJHA.1252@TK2MSFTNGP03.phx.gbl...
>>> 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" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in 
>>> message news:%23mvx2fUhJHA.1248@TK2MSFTNGP03.phx.gbl...
>>>>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.
>>>>
>>>
>>>
>>
>>
>
> 


0
2/3/2009 6:54:41 AM
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.



-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Chris Mitchell" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in message 
news:uyRhLxchJHA.3456@TK2MSFTNGP03.phx.gbl...
> 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
>
> "Jeff Boyce" <nonsense@nonsense.com> wrote in message 
> news:uJVFRHWhJHA.4408@TK2MSFTNGP06.phx.gbl...
>> 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" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in message 
>> news:%233RzmHVhJHA.3444@TK2MSFTNGP03.phx.gbl...
>>> 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" <nonsense@nonsense.com> wrote in message 
>>> news:O2sLI9UhJHA.1252@TK2MSFTNGP03.phx.gbl...
>>>> 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" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in 
>>>> message news:%23mvx2fUhJHA.1248@TK2MSFTNGP03.phx.gbl...
>>>>>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.
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
2/3/2009 11:28:45 AM
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" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message 
news:eJNoUKfhJHA.4868@TK2MSFTNGP05.phx.gbl...
> 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.
>
>
>
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Chris Mitchell" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in message 
> news:uyRhLxchJHA.3456@TK2MSFTNGP03.phx.gbl...
>> 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
>>
>> "Jeff Boyce" <nonsense@nonsense.com> wrote in message 
>> news:uJVFRHWhJHA.4408@TK2MSFTNGP06.phx.gbl...
>>> 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" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in 
>>> message news:%233RzmHVhJHA.3444@TK2MSFTNGP03.phx.gbl...
>>>> 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" <nonsense@nonsense.com> wrote in message 
>>>> news:O2sLI9UhJHA.1252@TK2MSFTNGP03.phx.gbl...
>>>>> 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" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in 
>>>>> message news:%23mvx2fUhJHA.1248@TK2MSFTNGP03.phx.gbl...
>>>>>>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.
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
2/3/2009 4:49:08 PM
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!)

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Chris Mitchell" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in message 
news:OoryV9hhJHA.5724@TK2MSFTNGP02.phx.gbl...
> 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" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message 
> news:eJNoUKfhJHA.4868@TK2MSFTNGP05.phx.gbl...
>> 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.
>>
>>
>>
>> -- 
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Chris Mitchell" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in message 
>> news:uyRhLxchJHA.3456@TK2MSFTNGP03.phx.gbl...
>>> 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
>>>
>>> "Jeff Boyce" <nonsense@nonsense.com> wrote in message 
>>> news:uJVFRHWhJHA.4408@TK2MSFTNGP06.phx.gbl...
>>>> 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" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in 
>>>> message news:%233RzmHVhJHA.3444@TK2MSFTNGP03.phx.gbl...
>>>>> 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" <nonsense@nonsense.com> wrote in message 
>>>>> news:O2sLI9UhJHA.1252@TK2MSFTNGP03.phx.gbl...
>>>>>> 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" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in 
>>>>>> message news:%23mvx2fUhJHA.1248@TK2MSFTNGP03.phx.gbl...
>>>>>>>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.
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
2/3/2009 7:26:09 PM
Thanks Douglas.

Does anyone have an Excel solution?

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message 
news:epKOGVjhJHA.500@TK2MSFTNGP06.phx.gbl...
> 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!)
>
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Chris Mitchell" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in message 
> news:OoryV9hhJHA.5724@TK2MSFTNGP02.phx.gbl...
>> 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" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message 
>> news:eJNoUKfhJHA.4868@TK2MSFTNGP05.phx.gbl...
>>> 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.
>>>
>>>
>>>
>>> -- 
>>> Doug Steele, Microsoft Access MVP
>>> http://I.Am/DougSteele
>>> (no e-mails, please!)
>>>
>>>
>>> "Chris Mitchell" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in 
>>> message news:uyRhLxchJHA.3456@TK2MSFTNGP03.phx.gbl...
>>>> 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
>>>>
>>>> "Jeff Boyce" <nonsense@nonsense.com> wrote in message 
>>>> news:uJVFRHWhJHA.4408@TK2MSFTNGP06.phx.gbl...
>>>>> 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" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in 
>>>>> message news:%233RzmHVhJHA.3444@TK2MSFTNGP03.phx.gbl...
>>>>>> 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" <nonsense@nonsense.com> wrote in message 
>>>>>> news:O2sLI9UhJHA.1252@TK2MSFTNGP03.phx.gbl...
>>>>>>> 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" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in 
>>>>>>> message news:%23mvx2fUhJHA.1248@TK2MSFTNGP03.phx.gbl...
>>>>>>>>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.
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
2/4/2009 5:16:33 AM
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?

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Chris Mitchell" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in message 
news:%2324l$eohJHA.4028@TK2MSFTNGP03.phx.gbl...
> Thanks Douglas.
>
> Does anyone have an Excel solution?
>
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message 
> news:epKOGVjhJHA.500@TK2MSFTNGP06.phx.gbl...
>> 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!)
>>
>> -- 
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Chris Mitchell" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in message 
>> news:OoryV9hhJHA.5724@TK2MSFTNGP02.phx.gbl...
>>> 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" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message 
>>> news:eJNoUKfhJHA.4868@TK2MSFTNGP05.phx.gbl...
>>>> 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.
>>>>
>>>>
>>>>
>>>> -- 
>>>> Doug Steele, Microsoft Access MVP
>>>> http://I.Am/DougSteele
>>>> (no e-mails, please!)
>>>>
>>>>
>>>> "Chris Mitchell" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in 
>>>> message news:uyRhLxchJHA.3456@TK2MSFTNGP03.phx.gbl...
>>>>> 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
>>>>>
>>>>> "Jeff Boyce" <nonsense@nonsense.com> wrote in message 
>>>>> news:uJVFRHWhJHA.4408@TK2MSFTNGP06.phx.gbl...
>>>>>> 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" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in 
>>>>>> message news:%233RzmHVhJHA.3444@TK2MSFTNGP03.phx.gbl...
>>>>>>> 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" <nonsense@nonsense.com> wrote in message 
>>>>>>> news:O2sLI9UhJHA.1252@TK2MSFTNGP03.phx.gbl...
>>>>>>>> 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" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in 
>>>>>>>> message news:%23mvx2fUhJHA.1248@TK2MSFTNGP03.phx.gbl...
>>>>>>>>>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.
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
2/4/2009 12:19:45 PM
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.



"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message 
news:u6E3eLshJHA.448@TK2MSFTNGP05.phx.gbl...
> 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?
>
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Chris Mitchell" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in message 
> news:%2324l$eohJHA.4028@TK2MSFTNGP03.phx.gbl...
>> Thanks Douglas.
>>
>> Does anyone have an Excel solution?
>>
>> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message 
>> news:epKOGVjhJHA.500@TK2MSFTNGP06.phx.gbl...
>>> 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!)
>>>
>>> -- 
>>> Doug Steele, Microsoft Access MVP
>>> http://I.Am/DougSteele
>>> (no e-mails, please!)
>>>
>>>
>>> "Chris Mitchell" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in 
>>> message news:OoryV9hhJHA.5724@TK2MSFTNGP02.phx.gbl...
>>>> 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" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message 
>>>> news:eJNoUKfhJHA.4868@TK2MSFTNGP05.phx.gbl...
>>>>> 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.
>>>>>
>>>>>
>>>>>
>>>>> -- 
>>>>> Doug Steele, Microsoft Access MVP
>>>>> http://I.Am/DougSteele
>>>>> (no e-mails, please!)
>>>>>
>>>>>
>>>>> "Chris Mitchell" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in 
>>>>> message news:uyRhLxchJHA.3456@TK2MSFTNGP03.phx.gbl...
>>>>>> 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
>>>>>>
>>>>>> "Jeff Boyce" <nonsense@nonsense.com> wrote in message 
>>>>>> news:uJVFRHWhJHA.4408@TK2MSFTNGP06.phx.gbl...
>>>>>>> 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" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in 
>>>>>>> message news:%233RzmHVhJHA.3444@TK2MSFTNGP03.phx.gbl...
>>>>>>>> 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" <nonsense@nonsense.com> wrote in message 
>>>>>>>> news:O2sLI9UhJHA.1252@TK2MSFTNGP03.phx.gbl...
>>>>>>>>> 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" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in 
>>>>>>>>> message news:%23mvx2fUhJHA.1248@TK2MSFTNGP03.phx.gbl...
>>>>>>>>>>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.
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
2/4/2009 3:21:30 PM
Reply:

Similar Artilces:

Can Turfed messages be delivered?
Running Exch 5.5SP4. Set up message filtering to stop some unwanted emails. But this also caught some legimate messages. Is there a way to cause or allow those selected emails to be delivered? Can this be done manually? If so, how? Thanks! ...

i am receiving every emai twice?? can anyone help with this issue
I would appreciate any advice cheers, See http://www.howto-outlook.com/faq/duplicates.htm -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----- "ETA" <ETA@discussions.microsoft.com> wrote in message news:5939A41A-2CBC-415A-BA84-C14C2FD19084@microsoft.com... > I would appreciate any advice > > cheers, ...

Can't send a link from IE 6
Installed Outlook 2003 recently, and now I cannot send a link to a page from IE6. I ran Tweakol2003 so I can receive links, etc. Something really simple I'll bet, but I have looked thru the help files and have not hit on it yet. Thanks for any guidance ... John Patch <fred@hotmail.com> wrote: > Installed Outlook 2003 recently, and now I cannot send a link to a > page from IE6. I ran Tweakol2003 so I can receive links, etc. How do you know you can't send it? -- Brian Tillman Usually when the you try and send a link, a Outlook form comes up over the IE screen jus...

Can't send via Outlook 2000
Hi folks - I'd appreciate your help. All of a sudden, with no changes on my part, I cannot send via Outlook 2000. I can receive (I use netaddress.com for incoming), but I can't send out (I use outgoing.verizon.net). I have a DSL connection. Verizon says all is fine on their end and with my set up. My anti-virus is current and has no issues. The emails I send stay in my outbox, and it keeps attempting to send them. Error messages vary, but they are usually of the gist that the TCP/IP connection was unexpectedly terminated by the server (outgoing.verizon.net). Any insigh...

how can I get the total hrs of a delay when it is greater than 24
I work for an airline and sometimes I need to know the total hours of a delay, and when the delay is greater than 24 hours. In my report I hav something like this: arrival time in colum A departure time colum B result in colum C 10/23/09 21:00 10/24/09 23:00 2:00 and really the delay was of 26 hours. How can I obtain this? I already set up my Tools-Options-Canculation to 1904 date system Format Custom as [h]:mm No need to use 1904 date system -- Kind regards, Niek Otten Microsoft MVP - Excel "El Ixmahana" <ElIxmahana...

Can not send message nor send an attachment from the web
This is what I get when I try to send a message and an attachment from the web. Can you please help me out. The host 'smtp' could not be found. Please verify that you have entered the server name correctly. Subject ' Check out this article Half a Million Plastic Coffins?', Account: 'pop3', Server: 'smtp', Protocol: SMTP, Port: 25, Secure(SSL): No, Socket Error: 11001, Error Number: 0x800CCC0D "adrian" <atheop@att.net> wrote in message news:ecvAdUTaKHA.4920@TK2MSFTNGP04.phx.gbl... > This is what I get when I try to send a...

Can't save over share
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have had Office 2008 for awhile now and I haven't come across this unusual bug until recently. The problem occurs when trying to save an xlsx file over a samba share. All other formats save fine i.e xls, doc, docx, pptx, etc. What will happen is it will try to save as an .xlsx file then a message box appears saying, &quot;Microsoft Excel cannot access the file 'share: [string of random numbers and letters]. The odd thing that I have stumbled upon is that this doesn't occur if I set an open pass...

Can excell notify me of due dates?
If I have due dates on my worksheet can I set something up so that I am notified on these dates? I've used this to help me. http://www.contextures.com/xlCondFormat01.html "Deion007" wrote: > If I have due dates on my worksheet can I set something up so that I am > notified on these dates? ...

Can there be variable size columns in one report?
I want to create a report that has 3 sub-reports of different column widths. Is this possible? -The 1st sub-report has 1 column that occupies the entire width of the page -The 2nd sub-report can fit 2 columns in the page width -The 3rd sub-report can fit 3 columns in the page width Subreports can have any number of columns that don't have to be the same from one to another. Typically your columns should display across then down in order to render properly as a subreport. -- Duane Hookom Microsoft Access MVP "SheldonHinds" wrote: > I want to create a r...

How can I convert an exported UNIX timestamp in excel?
When I export a table using phpMyAdmin I get a date string of: 1064272434 Which equates to: 09/22/2003 07:13:54 PM Is there a formula for excel that would make this conversion for me, stripping the time of course? Result: 09/22/2003 "prod sorter" <prodsorter@discussions.microsoft.com> wrote in message news:9DD4A2BA-7A0F-4B83-A1DA-4E98674CAE6D@microsoft.com... > When I export a table using phpMyAdmin I get a date string of: > > 1064272434 > > Which equates to: > > 09/22/2003 07:13:54 PM > > Is there a formula for excel that would make ...

Can't Send Messages Using Outlook Web Access & XP
Our users with Windows XP cannot send messages using OWA. I've read a few KB articles about some incompatabilities between XP and OWA, but none of the proposed fixes (using the "basic" rather than "premium" client, or changing the security settings in IE) seem to solve this last problem. When they click on "send", it generates an unspecified "error on page". The problem occurs using IE and Firefox, but just on XP machines. My Windows 2000 machine works fine using IE and even my Macintosh using Safari (gasp!) works. ...

can not receive emails with web links attached
Could someone tell me how to change controls to allow for emails with website links attached to come through? I can not receive emails with website links attached. ...

Windows Live Mail Error ID: 0x800ccc0dThis would not be a major pr
Dell inspirion 1545 Windows Vista HB MSG: The host 'http://get.live.com could not be found' Protocol: SMTP Port: 25 Secure(SSL): No Socket Error: 11001 I have uninstalled/Installed Windows Live Mail and get the same error. Email cannot be accessed via the Windows Live interface, only via hotmail. This would not be a major problem except that I cannot send photos. -- Thanks for the help. JohnnyT http://get.live.com is not used for email Is this problem occurring with a Hotmail account ? If using the pop3/smtp protocol for sending/receiving with a Hotmail account...

Can Money store bill balances?
Can money store my bill balances? For instance if I only partially pay a bill, I'd like to see what my balance is. Only if you create a liability account, record the bill as a liability, then pay all or part. The balance of the liability account is the balance of the bill. -- Chris Cowles Gainesville, FL "BDK" <BDK@discussions.microsoft.com> wrote in message news:298D90B7-A9CC-4F60-B598-196D52517C16@microsoft.com... > Can money store my bill balances? For instance if I only partially pay a > bill, I'd like to see what my balance is. See also http://u...

Can't use address book
I am not able to add contacts in my address book. I receive the error: You cannot create entries for this address book when I try to add a new entry. I use Office 2000, with full Outlook. I would like to have an address book again... I have uninstalled and reinstalled office 2000 - no change in the problem. You get this error in trying to create a new item in your contacts folder? (sounds like you are select tools | address book | highlight "outlook address book" and trying to create a new item. you can't create items in the "outlook address book". this f...

can i see the date the last time a cell was changed?
I am trying to figure out a formulla to make the date appear in one cell everytime anouther cell's data is chaged. Use a worksheet_change event to copy the cell address and put in a date stamp. -- Don Guillett SalesAid Software dguillett1@austin.rr.com "JohnNuTek" <JohnNuTek@discussions.microsoft.com> wrote in message news:5085A544-CBF4-4B81-A244-B03DE9A0E9E6@microsoft.com... >I am trying to figure out a formulla to make the date appear in one cell > everytime anouther cell's data is chaged. There really isn't a worksheet formula to do that. Typicall...

How can i null out a field going through CRM Update object
I have a custom field that i want to set back to null. Any ideas how i can do this through the object.update function? thanks ...

can't delete inbox on Outlook 6
I need help in deleting 368 messages in the in basket on my boss' computer. Trying all the obvious methods of deleting, the program does not react to anything. (delete key on keyboard, command on menu, etc.) I know there must be a way to remove these messages (one is increidbly long and perhaps the villian...been in the inbox since early October!). Since this is my boss' computer, is there anyone that can make me look like a hero? thanks, debe Hi - This is a group to support Outlook from the Office group of programs. Outlook Express is a part of Internet Explorer and is a q...

can cells apply conditional formatting using the internal clock?
I am using excell to keep track of my production schedule and I wanted to know if there was a way to tie the cells in a worksheet to the internal date and time in the computer,so that the cells will update automatically. Example: Row A10 would be my production start date, Row A1 would be my projected finish date, I would like the cells in between to go from green to red as I near the finish date without manually inputting the date in each cell. Can you help me? Thyanks Set the normal format as desired (I selected a Pattern of Green). Select A1:A10, then select Format | Conditional Fo...

Can I Export Emails ?
Hello, Is there anyway to Export emails and use them on another computer?? or is the easiest way to just forward them via email? Doesn't seem right...I must be missing something.. Any info would be helpful.. thx Mike just copy your pst and import that on the other machine. <mkalavitz@hotmail.com> wrote in message news:3ff101c3aac0$69e6c500$a601280a@phx.gbl... > Hello, > > Is there anyway to Export emails and use them on another > computer?? or is the easiest way to just forward them via > email? Doesn't seem right...I must be missing something.. > Any i...

BCC
I put the adresses on th BCC field but when the message is received the receiver can see the addresses instead of undisclosed-recipient. (Windows XP Professional, Outlook from Microsoft OfficeXP Suite) Does the recipient see more than just his/own address? Does it also happen when you supply at least one "To" address? Can you reproduce it over and over again? Does the same happen when you test it with Outlook Express using the same account? If so; contact your mail administrator/ISP to verify the mailserver settings -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tip...

Help!!!Very Important. others can see your emails!!!
I just noticed that if you are in an organization, using outlook,when you click on Addressbook button and type in someones name from Global Addres list, then hit the Properties button, then hit the personal Address Book, then hit the Activities tab, you can see that persons' entire emails. How can I stop that, so no one can see my mails? What you're looking at are the items in **your** Outlook folders related to that person. That's perfectly normal. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and...

Outlook 2003 can not connect to Exchange 2003 server through VPN
Hi, We have a Exchange 2003 server running on windows 2000 server. Everything is working fine (Outlook client, OWA) for LAN. The server has two NIC, one connect to LAN, another one connect to firewall with different subnet. I can ping both NIC after I established VPN connection from outside internet, also can open OWA using both NIC ip address. I updated "hosts" file on remote computer for the exchange server name and ip address translation. But, When I setup exchange server in outlook 2003 on the remote computer, type in the exchange server name and user name(User account is al...

saving a array to disk
i am a old bloke trying to teach myself to program. i sish to save a 3 demensional aray (structur and data) to disk and return it at a later date from a diferent module usingvba in access 2003. i have read of openfile,put and get but i am having truble puting them together can sombody give me a example On Thu, 7 Jan 2010 04:54:01 -0800, cliff <cliff@discussions.microsoft.com> wrote: >i am a old bloke trying to teach myself to program. >i sish to save a 3 demensional aray (structur and data) to disk and return >it at a later date from a diferent module usingvba in...

Can't send email with attachments
Windows XP Pro SP3; Office 2007. When attempting to send email with attachment,document gets stuck in outbox and caption at bottom of screen says "preparing to send and receive". This goes on indefinitely.Size of attachment file does not matter. Small and large attachment files get stuck in similar manner. Email without attachment can be sent successfully. Turning off Trend Micro firewall and other internet security does not help. ibexx This is caused by your AV scanning in and outgoing emails. Uninstall your AV and re-install it without the email integration (scanni...