On Fri, 6 Nov 2009 08:27:03 -0800 (PST), Ray wrote:

Again, thanks very much for your time to respond -- both suggestions
appear quite involved, and I appreciate your efforts!

Jan Karel - your revised solution works well, esp when pared with
CountIf (in another cell) to reflect actual frequency ...

Ron, from the description, your solution sounds intriguing and quite
useful but I'm not sure how to implement/call the procedure. The
coding looks like a UDF, but this part of the description
*******************
The routine creates a list of unique entries, and the frequency of
each entry.
It then computes the nth most frequent entry and returns all of the
strings
that match. (So if different strings have the same frequency, it will
return
all of them)
*******************
seems to indicate that the coding would return a range of items (to
multiple cells), based on the user-provided index number.

Could you please clarify the intended implementation of your coding?

Thanks again ...

ray

The function returns an array of values. For example, if you look at the
LINEST worksheet function in Excel, you can see that it returns an array of
values.

In order to display the results, you would "array-enter" the formula over
multiple cells (i.e. with <ctrl-shift-enter).

Or you could use the INDEX function to return each element of the array.

For example, given these entries in various cells in the range A1:I18

================================================== ==========
rugs

shampoo

shampoo potatotes end

#N/A
string beans potatoes
potatoes
potatoes string
beans
potatoes

holes
holes

================================================== ================

If you enter the formula:

=LargeStrings(A1:I18,2)

you would see displayed "shampoo"

However, it is really returning a horizontal array:

{"shampoo","string beans","holes"}

since all three have the 2nd largest frequency.

To "see" these you could either enter this as an array formula in at least
three cells (and if you wanted to see them in a vertical array you could
transpose the formula); or you could use the INDEX function, e.g.:

So if you wanted to display the results in a vertical array, you could enter a
formula like:

=IF(COUNTA(LargeStrings($A$1:$I$18,2))=ROWS($1:1) ,INDEX(LargeStrings($A$1:$I$18,2),ROWS($1:1)),"")

in some cell and fill down until you were returning blanks.

In the above example, if that formula were entered in A20 and you filled down,
you would see:

A20: shampoo
A21: string beans
A22: holes
--ron