15 Responses to “Sort column based on frequency”

1. narend says:

   September 19, 2009 at 4:56 am

   hi
   how to find missing numbers range 1-49 from following line

   06 11 17 33 37 47
   05 06 12 14 22 34
   03 20 35 41 48 49
   08 12 17 24 25 45
   03 10 18 22 29 45
   13 16 22 23 39 43
   03 10 17 31 36 45
   02 24 32 37 43 49
   10 16 27 28 29 42
   02 35 38 39 40 48
   08 23 34 36 44 47
   09 16 21 26 28 29
   10 15 32 33 39 41
   04 11 20 33 43 45
   01 08 21 22 27 36
   04 12 25 36 43 49
   09 12 23 26 34 43
   19 23 24 30 34 46
   08 17 20 21 31 45
   04 06 17 27 36 48
   07 08 19 28 32 41
   13 17 26 30 39 47
   10 15 20 29 32 43
   13 19 22 25 27 45
   04 09 19 21 25 42
   01 12 24 37 44 47
   08 19 31 34 38 42
   11 14 31 33 34 47
   19 22 32 33 38 48
   08 13 14 20 37 40
   03 08 10 13 41 47
   01 02 14 16 26 30
   30 34 36 37 41 49
   01 02 14 17 22 37
   03 09 28 34 39 47
   01 07 09 15 35 37
   13 14 19 24 32 33
   01 09 10 24 31 41
   11 14 19 23 35 41
   25 26 27 31 42 49
   14 16 21 26 37 48
   11 22 24 34 35 40
   05 10 12 13 15 17
   05 12 18 22 44 45

   Reply
2. admin says:

   September 20, 2009 at 9:47 pm

   Narend,

   find missing numbers range 1-49 from each row or the whole range?

   See this blog post: https://www.get-digital-help.com/2009/09/20/identify-missing-numbers-in-a-range-in-excel/

   Reply
3. Irina says:

   June 15, 2011 at 2:52 pm

   Hello,
   I just stumbled over this website and it's fascinating.
   Still I hope you can help me with the topic above since I get a #NUM error when using the formula. I want to extrac an unique list of data sorted by their occurance. For me it seems it works only partially.
   Hope you can help. Thanks

   Reply
4. Oscar says:

   June 16, 2011 at 12:29 pm

   Irina,

   I have pretty much changed everyting in this post. I hope it works now.

   Thanks for your comment!

   Reply
5. Sara says:

   April 18, 2012 at 8:17 pm

   How can you use large with multiple criteria?? Example looking for top 5 of a list based on name, state and city..

   Reply
6. Oscar says:

   April 23, 2012 at 9:59 am

   Sara,

   See this post: https://www.get-digital-help.com/2012/04/23/unique-distinct-records-sorted-by-frequency/

   Reply
7. Joe says:

   September 24, 2012 at 6:05 pm

   Hi Oscar, how do you exclude blanks from this? The formula to create the unique distinct list, sorted by occurrences from large to small is almost exactly what I'm looking for...I just can't seem to figure out where to wrap the IF(ISBLANK() to get this thing to exclude blanks.

   Reply
   • Oscar says:

     September 27, 2012 at 12:27 pm

     Joe,

     Array formula in cell D3:

     =INDEX(List, MATCH(LARGE(IF((COUNTIF($D$2:D2, List)=0)*(List<>""), COUNTIF(List, List), ""), 1), COUNTIF(List, List)*(COUNTIF($D$2:D2, List)=0), 0))

     See attached file:
     unique-sorted-by-occurances-blanks.xls

8. Gerald says:

   April 12, 2013 at 9:21 am

   Hi Oscar,
   brilliant formulas here. Helped me out a lot.

   Is it actually possible to sort list elements with an equal number of occurrence in alphabetical order?

   Thanks in advance,
   Gerald.

   Reply
   • Oscar says:

     April 16, 2013 at 8:12 am

     Gerald,

     Great question!

     

     See this file: unique-sorted-by-occurances-sorted-in-alphabetical-order.xls

   • Gerald says:

     April 18, 2013 at 1:44 pm

     Fantastic! Thanks a lot Oscar.

9. anju says:

   March 30, 2014 at 10:20 am

   hi
   i saw your all solutions about unique list but not find my answer. my question is ....i have 4 columns emp.no, name, code and program
   emp no name code prog
   903 a1 p30 find
   903 a1 p40 generate report
   903 a1 p30 find
   904 a2 p40 generate reports
   904 a2 p40 error
   903 a1 p20 error
   903 a1 p30 find
   904 a2 p40 generate reports

   now i want unique row with coount(means how many times row reapeting)

   Reply
   • Oscar says:

     April 1, 2014 at 8:40 am

     anju,

     read this post:
     Filter unique distinct row records in excel 2007

     now i want unique row with coount(means how many times row reapeting)
     Use the countifs function to count rows:
     COUNTIFS function

10. hapooh says:

    June 18, 2014 at 4:22 pm

    Hi,
    How do I create a new unique distinct list from their income?

    Client A -$5
    Client A -$5
    Client B - $1
    Client C - $2
    Client A - $2

    Results will be:

    Client A $12
    Client C $2
    Client B $1

    Reply
11. Leon says:

    October 17, 2015 at 2:26 am

    This is a very good formula. At first, it was hard to understand.
    With the help of debugger key F9, I was able to analyze it to gain
    a better understanding.
    Here is my notes using cell D5 as an example.
    In cell D5, we have:
    =INDEX(List, MATCH(LARGE(IF(COUNTIF($D$2:$D4, List)=0, COUNTIF(List, List),
    ""), 1), COUNTIF(List, List)*(COUNTIF($D$2:$D4, List)=0), 0))

    To make it more readable, we substitute them with array formula:

    =INDEX(List,MATCH(LARGE(occurArrayExLargerB,1),occurArrayExLarger0,0))

    where
    occurArray = COUNTIF(List,List)
    occurArrayExLarger0 = (COUNTIF($D$2:$D4,List)=0)*occurArray
    occurArrayExLargerB =IF(COUNTIF($D$2:$D4,List)=0, occurArray,"")

    To debug, we highlight the individual array formula and press F9 to get their intermediate values:
    COUNTIF($D$2:$D4,List) = {0;1;1;0;1;0;1;1;0;1;0;1}
    COUNTIF($D$2:$D4,List)=0 (exclusion logic)
    {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE}
    occurArray = {2;4;4;2;4;2;3;4;2;3;1;3}
    occurArrayExLarger0 = {2;0;0;2;0;2;0;0;2;0;1;0}
    which means to Exclude Larger occurrences (3 and 4) with 0
    occurArrayExLargerB = {2;"";"";2;"";2;"";"";2;"";1;""}
    which means to Exclude Larger occurrences (3 and 4) with Blank ""

    =INDEX(List, MATCH(2,occurArrayExLarger0,0)))
    =INDEX(List, 1) = "DD"

    This will result in #NUM! for cell beyond D7 because there are only
    5 unique cells. To expand dynamically, we wrap it around with ISERROR:
    =IF(ISERROR(
    INDEX(List,MATCH(LARGE(occurArrayExLargerB,1),occurArrayExLarger0,0))),"",
    INDEX(List,MATCH(LARGE(occurArrayExLargerB,1),occurArrayExLarger0,0)))

    Now we can copy and paste it to any cell in column D.
    This is the reason we need to define occurArrayExLargerB in addition to
    occurArrayExLarger0 because function LARGE(all"",1) will result in #NUM!.

    If it wasn't for #NUM! showing in cells beyond D7, we could simplify it to:

    =INDEX(List,MATCH(LARGE(occurArrayExLarger0,1),occurArrayExLarger0,0))

    without defining a new array formula occurArrayExLargerB.

    Reply

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes &lt; and > becomes &gt;

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form