Track Max of Matches across 2 columns in 2D Array
 

I have a 2D array with 2 rows and 100 columns (Redim ThisArray(0 to 1, 0 to
99). I have loaded data into the array as follows:

ThisArray(0,0) = A
ThisArray(0,1) = A
ThisArray(0,2) = A
ThisArray(0,3) = B
ThisArray(0,4) = B
ThisArray(0,5) = B
ThisArray(0,6) = B

ThisArray(1,0) = Red
ThisArray(1,1) = Red
ThisArray(1,2) = Blue
ThisArray(1,3) = Red
ThisArray(1,4) = Blue
ThisArray(1,5) = Blue
ThisArray(1,6) = Blue

I want to be able to count ther number of matches that exist across the rows
and record the max. For example A has 2 Red and B has 3 Blue. I wan to be
able to pull this data into a new 2D array called Results(). I would like it
to look as follows:

A B C
2:Red 3:Blue ?

How do I do this?

Thanks

EM

Track Max of Matches across 2 columns in 2D Array
 

On Jun 3, 6:48 pm, ExcelMonkey
wrote:
I have a 2D array with 2 rows and 100 columns (Redim ThisArray(0 to 1, 0 to
99). I have loaded data into the array as follows:

ThisArray(0,0) = A
ThisArray(0,1) = A
ThisArray(0,2) = A
ThisArray(0,3) = B
ThisArray(0,4) = B
ThisArray(0,5) = B
ThisArray(0,6) = B

ThisArray(1,0) = Red
ThisArray(1,1) = Red
ThisArray(1,2) = Blue
ThisArray(1,3) = Red
ThisArray(1,4) = Blue
ThisArray(1,5) = Blue
ThisArray(1,6) = Blue

I want to be able to count ther number of matches that exist across the rows
and record the max. For example A has 2 Red and B has 3 Blue. I wan to be
able to pull this data into a new 2D array called Results(). I would like it
to look as follows:

A B C
2:Red 3:Blue ?

How do I do this?

Thanks

EM

ExcelMonkey,

I usually let Excel do all the hard work. If your original data is an
Excel range, simply add a third row with each cell equal to the
concatenation of rows 1 and 2. Then do simple CountIfs to based on
the concatenated values for each possible pairing and take the max of
those by letter.

SteveM

Track Max of Matches across 2 columns in 2D Array
 

I am actually doing everything in code.

Thanks

EM

" wrote:

On Jun 3, 6:48 pm, ExcelMonkey
wrote:
I have a 2D array with 2 rows and 100 columns (Redim ThisArray(0 to 1, 0 to
99). I have loaded data into the array as follows:

ThisArray(0,0) = A
ThisArray(0,1) = A
ThisArray(0,2) = A
ThisArray(0,3) = B
ThisArray(0,4) = B
ThisArray(0,5) = B
ThisArray(0,6) = B

ThisArray(1,0) = Red
ThisArray(1,1) = Red
ThisArray(1,2) = Blue
ThisArray(1,3) = Red
ThisArray(1,4) = Blue
ThisArray(1,5) = Blue
ThisArray(1,6) = Blue

I want to be able to count ther number of matches that exist across the rows
and record the max. For example A has 2 Red and B has 3 Blue. I wan to be
able to pull this data into a new 2D array called Results(). I would like it
to look as follows:

A B C
2:Red 3:Blue ?

How do I do this?

Thanks

EM

ExcelMonkey,

I usually let Excel do all the hard work. If your original data is an
Excel range, simply add a third row with each cell equal to the
concatenation of rows 1 and 2. Then do simple CountIfs to based on
the concatenated values for each possible pairing and take the max of
those by letter.

SteveM