Forum Discussion

dsingh2255's avatar
dsingh2255
Brass Contributor
Oct 19, 2022
Solved

how to sort data with most occurance first with excel 2016 formula only

Hi All  My data is like this:- User Sale Qty Amount Betty Beth 10 2000 Akshay Verma 20 1500 Ashley Adams 50 3500 Barney Beckham 25 6000 Beth Firch 35 7000 Akshay Ve...
Formulas and Functions
  • dscheikey's avatar
    dscheikey
    Oct 20, 2022

    dsingh2255Here is the formula for 20,000 rows.

    =IFNA(INDEX(A2:C20001,MATCH(ROW(1:20000),RANK(COUNTIF(A2:A20001,A2:A20001)+(1-(MATCH(A2:A20001,A2:A20001,0)/10^5))+(1-(ROW(A2:A20001)/10^10)),COUNTIF(A2:A20001,A2:A20001)+(1-(MATCH(A2:A20001,A2:A20001,0)/10^5))+(1-(ROW(A2:A20001)/10^10)),0),0),{1,2,3}),"")

    I have added IFNA() so that you can prepare the formula whether the data is available or not. I think it is easy to understand. If you have more than 20k rows then you have to change the 20001 in the formula. If you have more than 99,999 rows you have to change the part 10^5 to 10^6 and the part 10^10 to 10^12. Good luck.

dscheikey's avatar
dscheikey
Bronze Contributor
Nov 17, 2022

dsingh2255 

The formula for the auxiliary column must now be as follows:

=IFERROR(COUNTIF(H2:H20001,H2:H20001)/--(TRIM(H2:H20001)<>"")+(1-(MATCH(H2:H20001,H2:H20001,0)/10^5))+(1-(ROW(H2:H20001)/10^10)),"")

 That was very interesting.

=H2=""         > TRUE
=ISBLANK(H2)   > FALSE
=COUNTA(H2)    > 1
=LEN(H2)       > 0
=CODE(H2)      > #VALUE!

The non-printable character could only be queried with ="". I did this outside of COUNTIF() and caught it with IFERROR(). With TRIM() I then also excluded the other empty cells with one or more spaces from the filter.

dsingh2255's avatar
dsingh2255
Brass Contributor
Nov 18, 2022
Hey Buddy.... in this solution data is sorting properly as i want but only one problem is there..
we can't skip the blank cell data ... the blank cell data should be at the bottom of the data..but in this solution have skipped the blank data...
so pls keep the formula same but modify it by add blank cell data at the bottom of all data

I'm waiting for your reply ...thanks
  • dsingh2255's avatar
    dsingh2255
    Brass Contributor
    Nov 18, 2022
    for now its working fine....Thnx buddy and sorry for asking same question so many times
  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Nov 18, 2022

    dsingh2255 

    The final Formula is:

    =IFERROR(COUNTIF(H2:H20001,H2:H20001)-((--(NOT(ISBLANK(H2:H20001)))+(H2:H20001="")-1)*20000)+(1-(MATCH(H2:H20001,H2:H20001,0)/10^5))+(1-(ROW(H2:H20001)/10^10)-0.99999),"")

     

  • dsingh2255's avatar
    dsingh2255
    Brass Contributor
    Nov 18, 2022

    dscheikey 

    I have attached my File..pls check it

     

    in this there are 3 sheets- Raw Data, Sorted Data After Formula, and My Requirement

    Raw Data- from where we are doing sorting by Industry Column.. here your sorting formula is in J Column (HELPER COLUMN) 

    Sorted Data After Formula - here we are fetching the sort data as per Industry Column highest value first.

    My Requirement-  This is the data looks like i want in Sorted Data Sheet. 

    here you can see sorted data are up in the order and blank cell data at the bottom of all data.

    so i want the sorted data in this sequence..

     

    i hope you got my point now....pls see the attachment 

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Nov 18, 2022

    dsingh2255

    How to deal with spaces in the cells. I don't mean the non-printable space, but one or more normal spaces. Does this occur at all? In the past, you had the requirement that spaces should not be taken into account. But now the non-printable space is. At the end means what? At position 20000, 19999 ... or after the last "correct" entry or after those with spaces?

    I need exact details or I'll have to change it three times.

Resources