Rank/Large Help
 

Hello
I have a list of randomly repeating values in A1:A100. I would like a
formula in B1:B10 that will return the top 10 used entries in the list in
order of use highest to lowest. It is possible that there may be less than
10 used entries and in that case I would like a 0.
Thanks!

Rank/Large Help
 

Should B1 contain the value that appears most frequently in column A or the
largest value in column A??
--
Gary''s Student - gsnu200753


"Sandy" wrote:

Hello
I have a list of randomly repeating values in A1:A100. I would like a
formula in B1:B10 that will return the top 10 used entries in the list in
order of use highest to lowest. It is possible that there may be less than
10 used entries and in that case I would like a 0.
Thanks!

Rank/Large Help
 

The value that appears most frequently
Thanks

"Gary''s Student" wrote:

Should B1 contain the value that appears most frequently in column A or the
largest value in column A??
--
Gary''s Student - gsnu200753


"Sandy" wrote:

Hello
I have a list of randomly repeating values in A1:A100. I would like a
formula in B1:B10 that will return the top 10 used entries in the list in
order of use highest to lowest. It is possible that there may be less than
10 used entries and in that case I would like a 0.
Thanks!

Rank/Large Help
 

Let's start with B2 rather than B1

In B2 enter:
=MODE(A1:A100)

In B3 enter:
=MODE(IF(ISNA(MATCH(A$1:A$100,B$2:B2,0)),A$1:A$100 ))
this is an array formula inserted with CNTRL-SHFT-ENTER rather than ENTER.

Copy B3 downwards.
--
Gary''s Student - gsnu200753


"Sandy" wrote:

The value that appears most frequently
Thanks

"Gary''s Student" wrote:

Should B1 contain the value that appears most frequently in column A or the
largest value in column A??
--
Gary''s Student - gsnu200753


"Sandy" wrote:

Hello
I have a list of randomly repeating values in A1:A100. I would like a
formula in B1:B10 that will return the top 10 used entries in the list in
order of use highest to lowest. It is possible that there may be less than
10 used entries and in that case I would like a 0.
Thanks!

Rank/Large Help
 

I am sorry I may have left out something important. The values are text and
I am getting N/A with text but if I use numbers it appears to work.
Thanks

"Gary''s Student" wrote:

Let's start with B2 rather than B1

In B2 enter:
=MODE(A1:A100)

In B3 enter:
=MODE(IF(ISNA(MATCH(A$1:A$100,B$2:B2,0)),A$1:A$100 ))
this is an array formula inserted with CNTRL-SHFT-ENTER rather than ENTER.

Copy B3 downwards.
--
Gary''s Student - gsnu200753


"Sandy" wrote:

The value that appears most frequently
Thanks

"Gary''s Student" wrote:

Should B1 contain the value that appears most frequently in column A or the
largest value in column A??
--
Gary''s Student - gsnu200753


"Sandy" wrote:

Hello
I have a list of randomly repeating values in A1:A100. I would like a
formula in B1:B10 that will return the top 10 used entries in the list in
order of use highest to lowest. It is possible that there may be less than
10 used entries and in that case I would like a 0.
Thanks!

Rank/Large Help
 

I found A solution. I used ahelpr column with countif and then applied your
formula to that column and then a vlookup with the results of your formula.
Is there a better way to accomplish this?
Thanks!!!!

"Gary''s Student" wrote:

Let's start with B2 rather than B1

In B2 enter:
=MODE(A1:A100)

In B3 enter:
=MODE(IF(ISNA(MATCH(A$1:A$100,B$2:B2,0)),A$1:A$100 ))
this is an array formula inserted with CNTRL-SHFT-ENTER rather than ENTER.

Copy B3 downwards.
--
Gary''s Student - gsnu200753


"Sandy" wrote:

The value that appears most frequently
Thanks

"Gary''s Student" wrote:

Should B1 contain the value that appears most frequently in column A or the
largest value in column A??
--
Gary''s Student - gsnu200753


"Sandy" wrote:

Hello
I have a list of randomly repeating values in A1:A100. I would like a
formula in B1:B10 that will return the top 10 used entries in the list in
order of use highest to lowest. It is possible that there may be less than
10 used entries and in that case I would like a 0.
Thanks!

Rank/Large Help
 

Is there a better way to accomplish this?

Depends on ones definition of better!

rng = A1:A100 = text entries (no empty cells within the range!)

Enter this array formula** in D1:

=IF(COUNTA(rng)<10,0,INDEX(rng,MODE(MATCH(rng,rng, 0)+{0,0})))

Enter this array formula** D2 and copy down as needed:

=IF(COUNTA(rng)<10,0,INDEX(rng,MODE(IF(COUNTIF(D$1 :D1,rng)=0,MATCH(rng,rng,0)+{0,0}))))

Both formulas will return non-modal entries. That is, unique entries. For
example:

brown
blue
brown
red
red

The formulas will return this array in order:

brown
red
blue

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Sandy" wrote in message
...
I found A solution. I used ahelpr column with countif and then applied
your
formula to that column and then a vlookup with the results of your
formula.
Is there a better way to accomplish this?
Thanks!!!!

"Gary''s Student" wrote:

Let's start with B2 rather than B1

In B2 enter:
=MODE(A1:A100)

In B3 enter:
=MODE(IF(ISNA(MATCH(A$1:A$100,B$2:B2,0)),A$1:A$100 ))
this is an array formula inserted with CNTRL-SHFT-ENTER rather than
ENTER.

Copy B3 downwards.
--
Gary''s Student - gsnu200753


"Sandy" wrote:

The value that appears most frequently
Thanks

"Gary''s Student" wrote:

Should B1 contain the value that appears most frequently in column A
or the
largest value in column A??
--
Gary''s Student - gsnu200753


"Sandy" wrote:

Hello
I have a list of randomly repeating values in A1:A100. I would
like a
formula in B1:B10 that will return the top 10 used entries in the
list in
order of use highest to lowest. It is possible that there may be
less than
10 used entries and in that case I would like a 0.
Thanks!