# Return most common number



## JustBEBE (Jan 5, 2023)

Hi, I need some help with a formula as I am stuck and can’t think anymore for a solution…let me explain what I need:

I have a col A1:A200 with 200 numbers from 1-20 that keeps repeating. In cell B1 I will add a number (5 for example). 

I need a formula to give me the 3 most common numbers that are in the row underneath the number 5 out of the 200 numbers in col A1:A200. 


A   B
2    5
1
5
4
9
5
19
7
5
19
16
5
3

In the example above as you can see, number 19 is twice under number 5, so I need excel to give me that number (but top 3 numbers that are most common out of 200 numbers if possible)

Can someone help please? Thanks in advance!


----------



## kevin9999 (Jan 5, 2023)

Book1ABC18Top 3Frequency2413153641441251251561071682910106119124131114101541681710188199208216227231424102520265276282291930143183210332341335123616371338153911401841114274313441845194614471848204975020511952175319541655556957195812591460461136217631864176517661167568769470471472673374147520765775789794802081482138318418859864871588489139089119218935943952961497209829916100610117102131039104310591061910721081210911110511131124113171141115611613117171186119312016121712215123161241312521261112771281512911301131113221331313451352013611375138201391114017141114216143171441314521462014714148141491915020151181521615381543155191561315741581715910160816118162816315164131651816618167516816169117031713172417313174117520176917771781117918180141811518217183318415185118661875188118916190111911819291937194519514196819717198131991620010Sheet1Cell FormulasRangeFormulaB2:B4B2=MODE(IF(ISERROR(MATCH($A$1:$A$200,B$1:B1,0)),$A$1:$A$200))C2:C4C2=COUNTIF($A$1:$A$200,B2)Named RangesNameRefers ToCells_FilterDatabase=Sheet1!$A$1:$A$200B2:C4


----------



## Flashbond (Jan 5, 2023)

kevin9999 said:


> Book1ABC18Top 3Frequency2413153641441251251561071682910106119124131114101541681710188199208216227231424102520265276282291930143183210332341335123616371338153911401841114274313441845194614471848204975020511952175319541655556957195812591460461136217631864176517661167568769470471472673374147520765775789794802081482138318418859864871588489139089119218935943952961497209829916100610117102131039104310591061910721081210911110511131124113171141115611613117171186119312016121712215123161241312521261112771281512911301131113221331313451352013611375138201391114017141114216143171441314521462014714148141491915020151181521615381543155191561315741581715910160816118162816315164131651816618167516816169117031713172417313174117520176917771781117918180141811518217183318415185118661875188118916190111911819291937194519514196819717198131991620010Sheet1Cell FormulasRangeFormulaB2:B4B2=MODE(IF(ISERROR(MATCH($A$1:$A$200,B$1:B1,0)),$A$1:$A$200))C2:C4C2=COUNTIF($A$1:$A$200,B2)Named RangesNameRefers ToCells_FilterDatabase=Sheet1!$A$1:$A$200B2:C4


Hi @kevin9999
I think what OP is asking for is, finding the top e most accuring numbers after a certain value. For ex. 19 occured twice after 5. Maybe other numbers occurred more than 19 did. Which 3 are the most frequent? But the condition is, they must appear right after 5. (Or any other pre-defined value)


----------



## kevin9999 (Jan 5, 2023)

Flashbond said:


> Hi @kevin9999
> I think what OP is asking for is, finding the top e most accuring numbers after a certain value. For ex. 19 occured twice after 5. Maybe other numbers occurred more than 19 did. Which 3 are the most frequent? But the condition is, they must appear right after 5. (Or any other pre-defined value)


Yes, you're right, I read it too quickly. I've switched off for tonight but be my guest 🙂


----------



## Peter_SSs (Jan 5, 2023)

JustBEBE said:


> I need a formula to give me the 3 most common numbers that are in the row underneath the number 5


If you have all the latest functions, see if this does what you want. (Colour was just for my own benefit for checking)

23 01 05.xlsmABC18Top 3Frequency24943516341220251565716829101061151241311141015516917101881992052192272314241025202652762822919301431832103323413351236163713381539114018411142743134418451946144718482049750205119521753195416555569571958125914604611362176318641765176611675687694704714726733741475207677757897948020814821383184188598648715884891390891192189359416952961497209829916100610117102131039104310591061910721081210911110511131124113171141115611613117171186119312016121712215123161241312521261112771281512911301131113221331313451352013611375138201391114017141114216143171441314521462014714148141491915020151181521615381543155191561315741581715910160816118162816315164131651816618167516816169117031713172417313174117520176917771781117918180141811518217183318415185118661875188118916190111911819291937194519514196819717198131991620010Sheet2 (3)Cell FormulasRangeFormulaB2:C4B2=LET(u,UNIQUE(FILTER(A2:A200,A1:A199=5)),TAKE(SORT(HSTACK(u,COUNTIFS(A2:A200,u,A1:A199,5)),2,-1),3))Dynamic array formulas.


----------



## Flashbond (Jan 5, 2023)

Paste this formula to D2:

```
=MODE(IF(ISERROR(MATCH(IF($A$1:$A$200=$B$1,$A$2:$A$201),D$1:D1;0)),IF($A$1:$A$200=$B$1,$A$2:$A$201)))
```
Unfortunately, this formula counts the values only occurring more than once.


----------



## JustBEBE (Jan 5, 2023)

Flashbond said:


> Paste this formula to D2:
> 
> ```
> =MODE(IF(ISERROR(MATCH(IF($A$1:$A$200=$B$1,$A$2:$A$201),D$1:D1;0)),IF($A$1:$A$200=$B$1,$A$2:$A$201)))
> ...


First of all I want to thank all of you for helping in this matter! You guys are great skilled!! @Flashbond  this was working great! Exactly what I was looking for! Thanks a million.

I also tried @Peter_SSs yours but is giving me some error and I didn’t identified it yet, I will check and if not sorting I will ask you to help :D. Thanks @kevin9999 for trying, maybe I wasn’t the best at explaining after whole night trying to make it work with no success…buy anyway, thanks again you all for getting into this and for help!! Is working and is great!


----------



## JustBEBE (Jan 5, 2023)

Peter_SSs said:


> If you have all the latest functions, see if this does what you want. (Colour was just for my own benefit for checking)
> 
> 23 01 05.xlsmABC18Top 3Frequency24943516341220251565716829101061151241311141015516917101881992052192272314241025202652762822919301431832103323413351236163713381539114018411142743134418451946144718482049750205119521753195416555569571958125914604611362176318641765176611675687694704714726733741475207677757897948020814821383184188598648715884891390891192189359416952961497209829916100610117102131039104310591061910721081210911110511131124113171141115611613117171186119312016121712215123161241312521261112771281512911301131113221331313451352013611375138201391114017141114216143171441314521462014714148141491915020151181521615381543155191561315741581715910160816118162816315164131651816618167516816169117031713172417313174117520176917771781117918180141811518217183318415185118661875188118916190111911819291937194519514196819717198131991620010Sheet2 (3)Cell FormulasRangeFormulaB2:C4B2=LET(u,UNIQUE(FILTER(A2:A200,A1:A199=5)),TAKE(SORT(HSTACK(u,COUNTIFS(A2:A200,u,A1:A199,5)),2,-1),3))Dynamic array formulas.


I’ve just seen now, you said if I have all the latest functions, so I think that’s the reason is not working for me, I don’t think I have them…but thanks for your time! I appreciate


----------



## Flashbond (Jan 5, 2023)

JustBEBE said:


> First of all I want to thank all of you for helping in this matter! You guys are great skilled!! @Flashbond  this was working great! Exactly what I was looking for! Thanks a million.
> 
> I also tried @Peter_SSs yours but is giving me some error and I didn’t identified it yet, I will check and if not sorting I will ask you to help :D. Thanks @kevin9999 for trying, maybe I wasn’t the best at explaining after whole night trying to make it work with no success…buy anyway, thanks again you all for getting into this and for help!! Is working and is great!


Ahh sorry, I forgot a semicolon there because of my regional settings but I guess you've figured it out already  Happy for it did help


----------

