Top 5 Strings?

[DekeDirt]

Hi,

Is there an array formula for eliciting the top five strings by frequency of occurence, not unlike what the LARGE array function does for integers?

For instance, I might have a list of thousands of alphanumeric codes under column A: how do I figure out what are the top five most frequently occurring codes? (I'm dealing with ISIN codes, if it matters.)

I realize a similar question has probably been posed here before but I found it difficult-impossible to search...

*(Also, not necessary and I hate to be a pain, but if you could explain the formula, it might be of great benefit. For instance, this one just confuses me to no end: why do you need the ROW and INDIRECT elements? I've tried it without and it spits out an error:

Please Login or Register  to view this content.

)

Hope you can help!

Thanks,



Derek

[HSV]

Something like:
Arrayformula.

Please Login or Register  to view this content.

[WHER]

Derek,
Have a look in the attachment to see if this does what you're looking for.

p.s.
Hi Harry!

[daddylonglegs]

why do you need the ROW and INDIRECT elements?

Hello Derek, looks like WHER has a good solution for you but specifically to answer your question above.......

..... this part

ROW(INDIRECT("1:10"))

simply generates an "array" of numbers 1 to 10 like

={1;2;3;4;5;6;7;8;9;10}

INDIRECT isn't strictly required, this will also work

=SUM(LARGE(Data,ROW(1:10)))

....but the 1:10 in that formula can change if you delete/insert rows in the worksheet so INDIRECT is used to make the formula more robust, i.e. to "fix" the values 1 to 10

If 10 is constant then it's possibly simpler to put that array directly in the formula, i.e. to sum the top 10 values in data

=SUM(LARGE(Data,{1;2;3;4;5;6;7;8;9;10}))

That has an added benefit - formula doesn't need to be "array entered"