Top 3 names in a list

[Tonyd789]

Hi all

I have been stuck on this for some time now and would appreciate some expert advice...

I have column A and B which both have a very simple formula in all the way down to row 500 which simply pulls in the data from another worksheet within the book in this case it is a name.
The potential for the same name to appear in both columns is very likely.

What i'm after is the top 3 names that appear from both list combined.

The list of names needs to remain separate and occasionally there is no name in certain rows too.

Any help in this would be greatly appreciated.

Regards Tony

 

[James_Latimer]

Re: Top 3 names in a list? Please help

Maybe this could help https://exceljet.net/formula/list-most-frequently-occuring-numbers

 

[Tonyd789]

Re: Top 3 names in a list? Please help

Will this work with the fact that its numbers and i have 2 lists of names?

 

[James_Latimer]

Re: Top 3 names in a list? Please help

Bottoms, you're right, it won't. Try this instead: https://www.mrexcel.com/forum/excel-questions/483773-formula-show-most-common-text-range.html

 

[Tonyd789]

Re: Top 3 names in a list? Please help

Think I'll have to study that one at a later date a bit closer with less distractions around me.

Thanks for your help

 

[Eric W]

Re: Top 3 names in a list? Please help

Maybe:

	A	B	C	D
List1	List2	Most common
Al	Al
Ed	Amy
Bob	Bob
Amy	Amy	Jane
Amy
Jane
Jane	Al
Bob	Al
Mary

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=IFERROR(INDIRECT(TEXT(MODE(IF($A$2:$B$20<>"",IF(COUNTIF($D$1:$D1,$A$2:$B$20)=0,IFERROR((MATCH($A$2:$B$20,$A$2:$A$20,0)+ROW($A$2)-1)*1000+COLUMN($A1),(MATCH($A$2:$B$20,$B$2:$B$20,0)+ROW($B$2)-1)*1000+COLUMN($B1))*{1,1}))),"R000C000"),0),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Tonyd789]

Re: Top 3 names in a list? Please help

Thank you very much, thats incredible!! How that works is way beyond me but it does! Very very much appreciated sir

 

[Eric W]

Re: Top 3 names in a list? Please help

Glad to help!

That was kinda fun to figure out.