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
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.
Sort Excel 2016.xlsx294 KB

dscheikey

Bronze Contributor

Oct 19, 2022

dsingh2255And besides, "Barney Beckham" comes before "Beth Firch" and not at the bottom of your list. Doesn't it?

dsingh2255
Brass Contributor
Oct 19, 2022
yes it does
- dscheikey
  Bronze Contributor
  Oct 19, 2022
  dsingh2255
  would the following order also be OK?
  - dsingh2255
    Brass Contributor
    Oct 19, 2022
    no brother,....i need same name should be together like both "Beth Finch" should be up down
- dscheikey
  Bronze Contributor
  Oct 19, 2022
  dsingh2255
  So sorting according to the order of occurrence? I had not understood that. Sorry.
  I thought it was about simple sorting. Let's see if I can do that too.

Forum Discussion

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

Resources