• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

List Top 10 with Duplicate Values

T

thelms79

New Member
Hello,

I am trying to find the appropriate formula for listing the Top 10 to include duplicates. All I've been able to find are formulas that ignore duplicates but I need a formula that includes them. Can you help me?
 
Good day thelms79


I am using 2010 and use CF to highlight duplicate cells, just choose rows/columns and in CF selected duplicates then format as to colour ect.
 
Hi,


Can you please refer the below link.


http://www.cpearson.com/excel/Duplicates.aspx


Thanks,

Suresh Kumar S
 
Hi @thelms79,

The most straightforward approach would be to use a helper column, and then use the LARGE function as Krishnakumar suggests.


Here is a slight adaptation of a technique described by Kyle McGhee in response to a recent formula forensics article: formula-forensics-no-030

(I am not able to put a link because of the "spam prevention" mechanism.)


Assuming that your source list is named "List", and is referring to A1:A13

Create a helper column in column B, using the following formula:

=VALUE(COUNTIF(List,A1) & "." & TEXT(COUNTIF(List, ">" & A1) + 1, "00000"))

(Put the above formula in cell B1, and copy down through B13)


Then in cell C1, put the following formula, to return the most frequently occuring items in list, sorted, but retaining all duplicates:

=INDEX(List, MATCH(LARGE(B$1:B$13,ROW(1:1)), B$1:B$13, 0))

Copy it down to additional rows in column C.


The above formula would give you the full list. If you need to restrict to just the top 10, add an IF condition such as ROW(1:1)< 10 in the formula in cell C1.


Please feel free to add any error checking desired.


Cheers,

Sajan.
 
Hi Apple! In the future, you should start a new thread rather than tacking on to someone elses (called thread hijacking). The answer to your question is to use:

=INT(A2)

which strips off the decimals(time) portion of a number.
 
Hello thelms79,


Assuming you are looking to return TOP n numbers, NOT texts.


D1 enter n number to return eg: 5


C1: leave it blank or enter a heading. This shouldn't be a real number. If it is a real number remove +1 from the below formula.


In C2,


=IF(SUM(SIGN(FREQUENCY(C$1:C1,C$1:C1)))+1<=MIN(COUNT(A:A),D$1),LARGE(A:A,ROWS(C$2:C2)),"")


then copy down.


SUM(SIGN..) part is counting how many UNIQUE numbers appeared in the above cells, If the count is <=n or MIN count A:A which ever is MIN, return LARGE numbers.


Hope this helps,

Haseeb
 
You must log in or register to reply here.
Back
Top