Hi,
In case anyone else is interested, here is a brief explanation of the formula... We can write up something more if there is interest.
The following formula generates a pattern for the matches, and returns the pattern with the most number of matches. (The original problem was to check if a given range had matching values in another range, and then identify the maximum number of matches found. The formula below goes one step beyond by providing the actual match pattern, instead of just a max count of the matches.)
For example, a result of "1011" would indicate that a match was found in fields one, three and four.
The formula is:
=LOOKUP("1111",DEC2BIN(MMULT((patientdata=$A8:$D8)*{8,4,2,1},{1;1;1;1}),4))
The formula searches for the pattern where A8:D8 is found in the range called "patientdata".
Reviewing the formula from the middle...
"patientdata=$A8:$D8" returns an array consisting of TRUEs for every match, and FALSEs for every non-match.
(If TRUE is represented by a 1, and FALSE by a 0, you would get a binary pattern of the matches and non-matches.)
The next step is to convert the binary pattern into a decimal number. You might recall that to convert a binary sequence into a decimal, you need to multiply each 1 with the power of 2 for that position, and then sum the values.
e.g.
[pre]
Code:
Binary sequence: 1 0 1 1
Position in sequence: 3 2 1 0
Power of 2 for the position: 8 4 2 1
Decimal value for binary sequence: 8 0 2 1
Adding them up, you get the decimal value: 8+0+2+1=11
So 1011 in binary is the same as 11 in decimal.
[/pre]
"(patientdata=$A8:$D8)*{8,4,2,1}" converts the TRUEs and FALSEs into values like {8,0,2,1}.
Now that you have a value like {8,0,2,1}, we need to add them up. That is where the MMULT function comes in handy. MMULT performs matrix multiplication.
MMULT((patientdata=$A8:$D8)*{8,4,2,1},{1;1;1;1}) returns a single column array, with the sum of the individual values. (In this example, {8,0,2,1} would become {11})
As you may recall regarding matrix multiplication,
multiplying {8,0,2,1} with {1;1;1;1} results in 8*1 + 0*1 + 2*1 + 1*1, to result in {11}
Now that we have the decimal value (11 in our example), we will convert it back to binary.
DEC2BIN() takes a decimal number, and the number of positions and returns a binary number.
In our example, DEC2BIN(11,4) returns 1011.
You might ask what the point of all that was, to convert from a binary sequence back into the binary number. Well, as you might have observed, the original values were an array, and we effectively concatenated the individual 0s and 1s.
So... {1,0,1,1} became "1011".
Depending on how many rows exist in the range "patientdata" we would have one or more rows of values in the array.
For example, if patientdata referred to a range of 3 rows, then the following formula segment returns an array with 3 values.
DEC2BIN(MMULT((patientdata=$A8:$D8)*{8,4,2,1},{1;1;1;1}),4) would return something like
{"1001";"0000";"0111"}
Now, we need to identify the value with the most 1s. That is where the LOOKUP function comes in handy.
The LOOKUP function will return the closest alphabetical match to the value being sought. In the formula, we search for "1111" which is the binary representation of decimal 15. So, in the above example, it would return "0111".
(In reality, you could search for any value that is alphabetically greater than "1111", including something like "A". I used "1111" because it was more representative of the max value.)
The net result from the formula is something like "0111" indicating that the range A8:D8 matched according to that pattern. (i.e. A8 did not match, but B8, C8 and D8 did.)
We can now use the pattern for additional processing, if desired, such as to conditionally format the range A8:D8 to highlight just the fields that had a match.
(One approach would be to compare the column number of the field to the corresponding digit in the result, and highlight the field if the digit is a 1.)
(The formula was based on the original posted dataset, where the "patientdata" had four columns. That is the reason for the segments like {8,4,2,1} and {1;1;1;1}. The formula can easily be extended to support more columns. I left out the generic version from the post so as to make it easier to read the formula. I can post the generic version if anyone is interested.)
Since I don't have a way to post a sample worksheet demonstrating the formula in action, I hope the above explanation is clear. (If anyone would be willing to receive an e-mail from me, I can send a sample file. That person can then, perhaps, post the sample file to this site.)
Cheers,
Sajan.
