Formulae for: 4 most repeats,4 least repeats in a series of numbers

Hello folks, Looking for some help. I have single column of 200 to 2000 numbers. I want to find the 4 least showing numbers with number of times not shown from row 40 onwards, The result to be displayed row by row.
And also the 4 highest repeating numbers in the last 200 rows, the result also showing row by row.

example highest showing
1
2
3
3 3 (2 shows)
4 3 (2 shows)
3 3 (3 shows)
5 3 (3 shows)
36 3 (3 shows)
34 3 (3 shows)
2 3 (3 shows) 2 (2 shows)
26 3 (3 shows) 2 (2 shows)
12 3 (3 shows) 2 (2 shows) and so on up to 4 numbers.

etc etc no shows 6 (13 no shows)
etc. max of 4 numbers
This is roulette based 37 numbers 0-36.
There must be a decider of relevancy if there are two or three numbers with the same amount of shows - this will be the most recent events. And the oldest for the non showing.

Thank you.

Hi,

Below is the method proposed by Aladin Akyurek :


Consider the following sample in A1:A6.

{2;5;2;4;4;3}

In B1 enter:

=COUNTIF($A$1:$A$6,A1)+COUNTIF($A$1:A1,A1)-1

In B2 enter:

=IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),"",COUNTIF($A$1:$A$6,A2)+COUNTIF($A$1:A2,A2)-1)

Copy down the last formula to the last row of data in A.

In C1 enter:

=IF(ISNUMBER(B1),RANK(B1,$B$1:$B$6,1)+COUNTIF($B$1:B1,B1)-1,"")

Copy down this to the last row of data in A.

In D1 enter:

=INDEX($A$1:$A$6,MATCH(LARGE($C$1:$C$6,ROW()),$C$1:$C$6,0))

Note. ROW() gives 1 in D1, 2 in D2 etc.

Copy down this e.g. to the 4th row of data, which will give you the first, second, third, and fourth most frequently occurring numbers.


HTH

Thanks for the speedy response. Not quite what I am after...
Let me say differently what I wish to do. I will have a column (A) of say 1000 random numbers ranging from 0 to 36. These are roulette spin results input one at a time. After each spin is entered into the column I need an update on the status of the following:- the four most frequent numbers in the last 200 range and the four least appearing numbers over all. I would expect the results to occupy 8 columns (B,C,D,E,F,G,H,I) in every row, to be readily visable as the new spin result is entered.I hope this explaination is clearer. Thanks again :-)