yes, I do... I have a list of merchant ID's and I want to see the occurrence of the to 10. the merchant ID's are alphanumeric.
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]A
[/td][td]B
[/td][td]C
[/td][td]D
[/td][/tr][tr][td]1
[/td][td]Text[/td][td][/td][td]3
[/td][td][/td][/tr]
[tr][td]2
[/td][td]london[/td][td][/td][td]4
[/td][td][/td][/tr]
[tr][td]3
[/td][td]avignon[/td][td][/td][td]Top N[/td][td]Count[/td][/tr]
[tr][td]4
[/td][td]roma[/td][td][/td][td]avignon[/td][td]3
[/td][/tr]
[tr][td]5
[/td][td]avignon[/td][td][/td][td]istanbul[/td][td]3
[/td][/tr]
[tr][td]6
[/td][td]avignon[/td][td][/td][td]london[/td][td]2
[/td][/tr]
[tr][td]7
[/td][td]istanbul[/td][td][/td][td]roma[/td][td]2
[/td][/tr]
[tr][td]8
[/td][td]istanbul[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]9
[/td][td]istanbul[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]10
[/td][td]milano[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]11
[/td][td]roma[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]12
[/td][td]london[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]13
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
C1: 3 (Replace with 10 in your workbook.)
Define A2:A12 as
Text in Formulas | Name Manager.
Define
Ivec in terms of the named range Tex in Formulas | Name Manager as referring to:
=ROW(Text)-ROW(INDEX(Text,1,1))+1
In C2 control+shift+enter, not just enter:
=IF(COUNTIFS(Text,"?*"),SUM(IF(FREQUENCY(IF(Text<>"",MATCH(Text,Text,0)),Ivec)>=LARGE(FREQUENCY(IF(Text<>"",MATCH(Text,Text,0)),Ivec),MIN(COUNTIFS(Text,"?*"),C1)),1)),0)
which is a self-adjusting Top N, based on C1.
In C4 control+shift+enter, not just enter, and copy down:
=IF($D4="","",INDEX(Text,SMALL(IF(FREQUENCY(IF(Text<>"",MATCH(Text,Text,0)),Ivec)=$D4,Ivec),COUNTIFS($D$4:D4,D4))))
In D4 control+shift+enter, not just enter, and copy down:
=IF(ROWS($D$4:D4)<=$C$2,LARGE(FREQUENCY(IF(Text<>"",MATCH(Text,Text,0)),Ivec),ROWS($D$4:D4)),"")
