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.
dsingh2255 Can't offer you a formula driven solution, but perhaps Power Query does what you need. Should work in E2016 as well. See attached.
- thnx for your valueable feedback but i can't use power query as well
dsingh2255 Just wondering why not. It's part of E2016 and nothing out of the ordinary for such a task. But, of course, that's your choice.
- if i would be doing it for one time solution only then yeah i could be use anything like go to data tab and sort just like i want and sort and sort by function but here scenario is different ...
i am doing it for nintex software where excel is using as middleware and nintex support only excel 2016. so i just have to put the sort fomula on excel sheet so that once someone will pull the data from nintex it will automatically sort the file as we desire... hope u got my point now
