Match More than one
Posted by Sameer Bhide on November 06, 2001 6:30 PM
Consider a table below
value Description
25 A
34 B
32 C
13 D
34 G
32 H
34 K
The objective is to write a formula that looks for a maximum in column - "value" and displays the dats in column "Description" next to it.
In the above example the formula should display the following in one cell.
B G K (- in a single cell with space in b/w each character)
The formula below does that but displays the result only when you evaluate it using F9
(IF(ISERROR(SEARCH(MAX($A$2:$A$8), $A$2:$A$8)), "", $B$2:$B$8))
Is there a workaround or an alternate way of achieving this.
TIA
Sam
Excel 97
Posted by Aladin Akyurek on November 06, 2001 8:58 PM
Besides the need for F9, it doesn't do at all what you want. What you get is
{"";"B";"";"";"G";"";"K"}
which is way off of
B G K
I'll assume your data to be in A1:B8 including the labels.
In C2 enter: =IF(COUNTIF($A$1:$A$8, MAX($A$1:$A$8))>0,INDEX($B$1:$B$8, MATCH(MAX($A$1:$A$8), $A$1:$A$8,0)),"")
In C3 enter: =IF(COUNTIF($A$1:$A$8, MAX($A$1:$A$8))>COUNTA($C$2:C2), C2 & " " & INDEX($B$1:$B$8, SUMPRODUCT(MATCH(MAX($A$1:$A$8), INDIRECT(ADDRESS(MATCH(MAX($A$1:$A$8) & "-" & RIGHT(C2), $A$1:$A$8 & "-" & $B$1:$B$8,0)+1, 1) & ":" & "$A$8"), 0)) + SUMPRODUCT(MATCH(MAX($A$1:$A$8) & "-" & RIGHT(C2), $A$1:$A$8 & "-" & $B$1:$B$8, 0))),"")
Copy down this mega-formula till it does not produce another result. The last one is what you are looking for.
Regards,
Aladin
Posted by Sameer Bhide on November 07, 2001 1:38 AM
Aladin
Thanks for the reply.Your formula works fine for the above data.
One Problems though.
If the Data in the "Description" Column contains text with more than one character (next to the max values) then the formula returns a #NA
Posted by Aladin Akyurek on November 07, 2001 3:51 AM
I should have known that that one letter descriptions are not structural. :)
In C1 enter: =MAX(A2:A8)
In C2 enter: =COUNTIF(A2:A8,C1)
In C3 enter: =IF(C2,INDEX($B$1:$B$8,MATCH(C1,$A$1:$A$8,0)),"")
In C4 enter: =IF(COUNTA($C$3:C3) & LT;C2, C3 & " " & INDEX($B$1:$B$8, SUMPRODUCT(MATCH(C1, INDIRECT(ADDRESS(MATCH(C1 & "-" & C3, $A$1:$A$8 & "-" & $B$1:$B$8, 0)+1, 1) & ":" & "$A$8"), 0)) + SUMPRODUCT(MATCH(C1 & "-" & C3, $A$1:$A$8 & "-" & $B$1:$B$8, 0))),"")
In C5 enter: =IF(COUNTA($C$3:C4) <$C$2,C4 &" " & INDEX($B$1:$B$8, SUMPRODUCT(MATCH($C$1, INDIRECT(ADDRESS(MATCH($C$1 & "-" & SUBSTITUTE(C4, C3 & " ", ""), $A$1:$A$8 & "-" & $B$1:$B$8, 0)+1, 1) & ":" & "$A$8"), 0)) + SUMPRODUCT(MATCH($C$1 & "-" & SUBSTITUTE(C4, C3 & " ", ""), $A$1:$A$8 & "-" & $B$1:$B$8, 0))), "")
Copy the last formula down till no additional result is produced.
Aladin
-------------------------
Posted by Aladin Akyurek on November 07, 2001 3:55 AM
I should have known that that one letter descriptions are not structural. :)
In C1 enter: =MAX(A2:A8)
In C2 enter: =COUNTIF(A2:A8,C1)
In C3 enter: =IF(C2,INDEX($B$1:$B$8,MATCH(C1,$A$1:$A$8,0)),"")
In C4 enter: =IF(COUNTA($C$3:C3) < C2, C3 & " " & INDEX($B$1:$B$8, SUMPRODUCT(MATCH(C1, INDIRECT(ADDRESS(MATCH(C1 & "-" & C3, $A$1:$A$8 & "-" & $B$1:$B$8, 0)+1, 1) & ":" & "$A$8"), 0)) + SUMPRODUCT(MATCH(C1 & "-" & C3, $A$1:$A$8 & "-" & $B$1:$B$8, 0))), "")
In C5 enter: =IF(COUNTA($C$3:C4) < $C$2, C4 & " " & INDEX($B$1:$B$8, SUMPRODUCT(MATCH($C$1, INDIRECT(ADDRESS(MATCH($C$1 & "-" & SUBSTITUTE(C4,C3 & " ", ""), $A$1:$A$8 & "-" & $B$1:$B$8, 0) + 1, 1) & ":" & "$A$8"), 0)) + SUMPRODUCT(MATCH($C$1 & "-" & SUBSTITUTE(C4, C3 & " ", ""), $A$1:$A$8 & "-" & $B$1:$B$8, 0))), "") Copy the last formula down till no additional result is produced. Aladin -------------------------
Posted by Sameer bhide on November 07, 2001 4:36 AM
In C1 enter: =MAX(A2:A8)
Aladin
Absolutely Brilliant!!.Thanks a lot. I wanted a solution without restoring to VBA and you have provided just that.
Thanks again
Sam
