Forum Discussion
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...
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.
Please explain more clearly how you want to sort the data. I cannot find a consistent pattern in your sample output.
you can see Users Column
there is a name Akshay Verma and its occurance is 3 times in the users column.
so i want to sort it by user so that i can get all Askhay verma first then second most occurance user and so on..
there is a name Akshay Verma and its occurance is 3 times in the users column.
so i want to sort it by user so that i can get all Askhay verma first then second most occurance user and so on..
Why can't you use the Sort options on the Ribbon?
- because i have to put the formula on the sheet and then we are using excel as middleware in nintex software ..when data will populate from the sheet it will automatically sort value by most repeatiting value first