Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return value of non matches in columns | Excel Discussion (Misc queries) | |||
Count matches within two columns | Excel Discussion (Misc queries) | |||
Counting matches from more columns | Excel Worksheet Functions | |||
Is there any way to use HLOOKUP in an array formula to return multiple matches? | Excel Worksheet Functions | |||
how to find all matches in an array | Excel Discussion (Misc queries) |