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

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),"")

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 

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.

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.

dscheikey Hi huddy need a help from you

you have given me this sorting formula

IFNA(COUNTIFS(H2:H20001,H2:H20001,H2:H20001,"<> ") +(1-(MATCH(H2:H20001,H2:H20001,0)/10^5))+(1-(ROW(H2:H20001)/10^10)),"")

here im facing a issue ..when im fetching data from system then an unseen text is coming from the system ..the cell is showing blank but when we are checking with isblank then its showing false.

i have shared a file in which im sorting by Industry column ..and i want if there is data that should be on top.  but again we the blank is showing on the top.. because there is unseen text coming from the system which is not required..

can you please check the excel file and let me know the problem....

dsingh2255 

I also have questions: Will the other sorting criteria remain as before? Only the date at No. 1?

You still haven't told me which solution works. With the auxiliary column in column S or with the array formula in I2?

dscheikey  Hi Buddy..

Need a little more help from you

This time i want to sort the data by most recent DATE first...Like the most recent date should comes at first then second recent and so on..

The data is same as above pitcure only one more DATE column has been added. and i want sort by most recent date first now

Pls reply if you get it ..!

dsingh2255 

Tell me, what are these empty cells? If they are completely empty, they shouldn't be a problem. They do not appear. If they contain one space, for example, then you can filter them out by replacing part of the formula:

COUNTIF(A2:A20001,A2:A20001)

Replace with:

COUNTIFS(A2:A20001,A2:A20001,A2:A20001,"<> ")

If there are different numbers of spaces in the column, it becomes much more complicated.

The easiest way would be to delete the supposed empty cells completely.

By the way: You still haven't told me which solution works. With the auxiliary column in column S or with the array formula in I2?

dscheikey  hey buddy...need a little help from you

the solution you gave me is working fine but when maximum number of users is blank then blank is coming first and then the rest of the users...

i want that when blank cells down of the data..

can you please help...