Forum Discussion
how to sort data with most occurance first with excel 2016 formula only
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.
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.
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
- for now its working fine....Thnx buddy and sorry for asking same question so many times
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),"")