Most Occuring Number

Hi,
I am sure someone asked a similar questions before. But, I cannot seem to find what I am looking for. Seems simple but cannot get anything to work. Basically I want to know which number in Column B occurs the most for each number in Column A. Please see attached example.
Thanks

What happens when there's a tie? What output do you want then?

Your output doesnt make sense.

There are 2 x AE20s with a 6, 6 is the only number that follows AE20 so thats the top occurrence of AE20 - thats fine.
AL10 has a 2 x 9s and an 8 - so 9 is the top occurrence for AL10 - with 8 coming in second place - thats fine.

But AL20 has only one occurrence of being followed by a 1 and only one occurrence of being followed by a 2 - so how come the 2 is in second place?

BA10 with 4 and 5 is ok

Same problem with BA20 3 and 4 as above

BA30 with 9 and 8 is ok

DK40 with 6 and 5 is ok

DK50 with 8 and 9 - again it's a draw - same problem as above.

=COUNTIFS(A$1:A$20,A1,B$1:B$20,B1) provides the number of occurrences of the columns A and B.
You then need to RANK in a group of columns A and B, not my area of expertise I'm afraid but I'll have a play round with it.

Try:

={IFERROR(LARGE(IF($A$1:$A$20=$E3,$B$1:$B$20),COLUMN()-5),"")} (CSE needed)
drag down / right as needed.


But as been said, in the case of draw 1st most frequent = 2nd most frequent or 2nd=3rd etc.
And also generate error if only 1 or 2 possible number appear and you want to display 3rd most frequent (that's why I've add IFERROR)

To return most frequently occurring number, you'd normally use MODE family of functions.
Ex: =MODE(IF(($A$1:$A$21=$E3)*(1-ISNUMBER(MATCH($B$1:$B$21,TRANSPOSE($E3:E3),0))),$B$1:$B$21))
Confirmed as Array (CTRL + SHIFT + ENTER). Copy across and down.

However... you'd require at least 2 or more occurrence of any given number or it will return error (i.e. number cannot be unique based on condition).

With your sample, only AE20, AL10, BA10, BA30, DK40 will return valid result. And each has only one valid result (meaning all numbers that belong to other values in Column A are unique).

0.JPG

See attached for better sample for demonstration.

Please try at F3 with CSE drag across and down

Formula:

Please Login or Register  to view this content.

Another way. Requires Excel 2010 or newer.

Array entered.

Formula:

Please Login or Register  to view this content.

You are welcome. Glad to help. Thank you for the feedback and marking your thread Solved.