How to find out the 3 highest occurrence of number from a list?
 

Does anyone have any suggestions on how to find out the first, second, and
third highest occurrence of number from a list?

There is a sorted list of numbers in ascending order under column A,

3,3,3,3,3,4,4,5,5,5,6,6,6,6,6,7,7,9,9,9,10,10,10,1 0,10,11,11,11,11,11,
12,12,12,12,12,12

Since there is 6 occurrence for 12, and there are 5 occurrence for 3,6,10
Then it should return the 12 in cell B1, and 3 in cell C1, and 6 in cell D1,
and ignore 10, because I would like to select the 3 numbers only from a list.
Does anyone have any suggesitons?
Thank in advance for any suggestions
Eric

How to find out the 3 highest occurrence of number from a list?
 

Use the LARGE function after reducing the list to unique terms with the
advanced filter.

"Eric" wrote:

Does anyone have any suggestions on how to find out the first, second, and
third highest occurrence of number from a list?

There is a sorted list of numbers in ascending order under column A,

3,3,3,3,3,4,4,5,5,5,6,6,6,6,6,7,7,9,9,9,10,10,10,1 0,10,11,11,11,11,11,
12,12,12,12,12,12

Since there is 6 occurrence for 12, and there are 5 occurrence for 3,6,10
Then it should return the 12 in cell B1, and 3 in cell C1, and 6 in cell D1,
and ignore 10, because I would like to select the 3 numbers only from a list.
Does anyone have any suggesitons?
Thank in advance for any suggestions
Eric

How to find out the 3 highest occurrence of number from a list?
 

=MAX(A1:A20)

=LARGE(IF(MATCH($A$1:$A$20,$A$1:$A$20,0)=ROW($A$1: $A$20)-CELL("Row",$A$1:$A$20)+1,$A$1:$A$20),2)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

and

=LARGE(IF(MATCH($A$1:$A$20,$A$1:$A$20,0)=ROW($A$1: $A$20)-CELL("Row",$A$1:$A$20)+1,$A$1:$A$20),3)

also an array formula

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Eric" wrote in message
...
Does anyone have any suggestions on how to find out the first, second, and
third highest occurrence of number from a list?

There is a sorted list of numbers in ascending order under column A,

3,3,3,3,3,4,4,5,5,5,6,6,6,6,6,7,7,9,9,9,10,10,10,1 0,10,11,11,11,11,11,
12,12,12,12,12,12

Since there is 6 occurrence for 12, and there are 5 occurrence for 3,6,10
Then it should return the 12 in cell B1, and 3 in cell C1, and 6 in cell
D1,
and ignore 10, because I would like to select the 3 numbers only from a
list.
Does anyone have any suggesitons?
Thank in advance for any suggestions
Eric

How to find out the 3 highest occurrence of number from a list?
 

With your posted data list in A1:A36

Try this ARRAY FORMULA (committed with Ctrl+Shift+Enter, instead of Enter):

C1:
=INDEX($A$1:$A$40,MATCH(LARGE(IF(($A$1:$A$40<""), IF(MATCH($A$1:$A$40,$A$1:$A$40,0)=ROW($A$1:$A$40), COUNTIF($A$1:$A$40,$A$1:$A$40)+1/ROW($A$1:$A$40)/1000)),ROW()),IF(($A$1:$A$40<""),IF(MATCH($A$1:$A $40,$A$1:$A$40,0)=ROW($A$1:$A$40),COUNTIF($A$1:$A$ 40,$A$1:$A$40)+1/ROW($A$1:$A$40)/1000)),0))

Note_1: There are NO spaces in that formula
Note_2: I had that formula references A1:A40 so that it includes blank cells
to ensure that they don't trip up its calculation

Note_3: If that formula returns the values you're looking for....we can see
about making it more elegant.

Does that help?
***********
Regards,
Ron

XL2003, WinXP


"Eric" wrote:

Does anyone have any suggestions on how to find out the first, second, and
third highest occurrence of number from a list?

There is a sorted list of numbers in ascending order under column A,

3,3,3,3,3,4,4,5,5,5,6,6,6,6,6,7,7,9,9,9,10,10,10,1 0,10,11,11,11,11,11,
12,12,12,12,12,12

Since there is 6 occurrence for 12, and there are 5 occurrence for 3,6,10
Then it should return the 12 in cell B1, and 3 in cell C1, and 6 in cell D1,
and ignore 10, because I would like to select the 3 numbers only from a list.
Does anyone have any suggesitons?
Thank in advance for any suggestions
Eric

How to find out the 3 highest occurrence of number from a list
 

Addendum to the instructions:

Copy C1 and paste into C2 and down as far as you need

***********
Regards,
Ron

XL2003, WinXP


"Ron Coderre" wrote:

With your posted data list in A1:A36

Try this ARRAY FORMULA (committed with Ctrl+Shift+Enter, instead of Enter):

C1:
=INDEX($A$1:$A$40,MATCH(LARGE(IF(($A$1:$A$40<""), IF(MATCH($A$1:$A$40,$A$1:$A$40,0)=ROW($A$1:$A$40), COUNTIF($A$1:$A$40,$A$1:$A$40)+1/ROW($A$1:$A$40)/1000)),ROW()),IF(($A$1:$A$40<""),IF(MATCH($A$1:$A $40,$A$1:$A$40,0)=ROW($A$1:$A$40),COUNTIF($A$1:$A$ 40,$A$1:$A$40)+1/ROW($A$1:$A$40)/1000)),0))

Note_1: There are NO spaces in that formula
Note_2: I had that formula references A1:A40 so that it includes blank cells
to ensure that they don't trip up its calculation

Note_3: If that formula returns the values you're looking for....we can see
about making it more elegant.

Does that help?
***********
Regards,
Ron

XL2003, WinXP


"Eric" wrote:

Does anyone have any suggestions on how to find out the first, second, and
third highest occurrence of number from a list?

There is a sorted list of numbers in ascending order under column A,

3,3,3,3,3,4,4,5,5,5,6,6,6,6,6,7,7,9,9,9,10,10,10,1 0,10,11,11,11,11,11,
12,12,12,12,12,12

Since there is 6 occurrence for 12, and there are 5 occurrence for 3,6,10
Then it should return the 12 in cell B1, and 3 in cell C1, and 6 in cell D1,
and ignore 10, because I would like to select the 3 numbers only from a list.
Does anyone have any suggesitons?
Thank in advance for any suggestions
Eric

How to find out the 3 highest occurrence of number from a list
 

Another "one more thing" : \

I just noticed that you wanted the values to list ACROSS....not Down.

C1:
=INDEX($A$1:$A$40,MATCH(LARGE(IF(($A$1:$A$40<""), IF(MATCH($A$1:$A$40,$A$1:$A$40,0)=ROW($A$1:$A$40), COUNTIF($A$1:$A$40,$A$1:$A$40)+1/ROW($A$1:$A$40)/1000)),COLUMNS($B:B)),IF(($A$1:$A$40<""),IF(MATCH ($A$1:$A$40,$A$1:$A$40,0)=ROW($A$1:$A$40),COUNTIF( $A$1:$A$40,$A$1:$A$40)+1/ROW($A$1:$A$40)/1000)),0))

Remember to commit with Ctrl+Shift+Enter

Copy C1 in to D1 and E1

Using your posted data....
C1 returns 12
D1 returns 3
E1 returns 6

I hope that helps.
***********
Regards,
Ron

XL2003, WinXP


"Ron Coderre" wrote:

With your posted data list in A1:A36

Try this ARRAY FORMULA (committed with Ctrl+Shift+Enter, instead of Enter):

C1:
=INDEX($A$1:$A$40,MATCH(LARGE(IF(($A$1:$A$40<""), IF(MATCH($A$1:$A$40,$A$1:$A$40,0)=ROW($A$1:$A$40), COUNTIF($A$1:$A$40,$A$1:$A$40)+1/ROW($A$1:$A$40)/1000)),ROW()),IF(($A$1:$A$40<""),IF(MATCH($A$1:$A $40,$A$1:$A$40,0)=ROW($A$1:$A$40),COUNTIF($A$1:$A$ 40,$A$1:$A$40)+1/ROW($A$1:$A$40)/1000)),0))

Note_1: There are NO spaces in that formula
Note_2: I had that formula references A1:A40 so that it includes blank cells
to ensure that they don't trip up its calculation

Note_3: If that formula returns the values you're looking for....we can see
about making it more elegant.

Does that help?
***********
Regards,
Ron

XL2003, WinXP


"Eric" wrote:

Does anyone have any suggestions on how to find out the first, second, and
third highest occurrence of number from a list?

There is a sorted list of numbers in ascending order under column A,

3,3,3,3,3,4,4,5,5,5,6,6,6,6,6,7,7,9,9,9,10,10,10,1 0,10,11,11,11,11,11,
12,12,12,12,12,12

Since there is 6 occurrence for 12, and there are 5 occurrence for 3,6,10
Then it should return the 12 in cell B1, and 3 in cell C1, and 6 in cell D1,
and ignore 10, because I would like to select the 3 numbers only from a list.
Does anyone have any suggesitons?
Thank in advance for any suggestions
Eric