Forum Discussion

dsingh2255's avatar
dsingh2255
Brass Contributor
Oct 19, 2022
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...
Formulas and Functions
  • dscheikey's avatar
    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.

dscheikey's avatar
dscheikey
Bronze Contributor
Oct 19, 2022

dsingh2255 

Hello, I'm going to try a solution. I have developed a formula that works with LibreOffice Calc. This might be the closest to Excel 2016. I can only test the formula in Excel for the web, so I'm not quite sure how it works for you. You have to make an array formula out of it in any case. So finish with CTRL + SHIFT + ENTER.

=INDEX(A2:C10,MATCH(ROW(1:9),RANK(COUNTIF(A2:A10,"<="&A2:A10)+(ROW(A2:A10)/1000),COUNTIF(A2:A10,"<="&A2:A10)+(ROW(A2:A10)/1000),1),0),{1,2,3})

I can't do that in Excel for the web. So I had to create an auxiliary column (column S). When I try to copy the content into the formula, it doesn't work. See Cell I2. It might work for you if you make an ARRAY FORMULA out of it. I am curious about your feedback.

 

  • dsingh2255's avatar
    dsingh2255
    Brass Contributor
    Oct 19, 2022
    thnks for your valueable reply....
    i have seen your formula and the result as well but if you see "Barney Beckham" is likely to have at the bottom because its only one time in the column and "Beth Firch" should be up ... as i needed most repeated value first.so "beth Firch" is 2 times and "Barney Beckham" is one time

Resources