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 Verma | 45 | 5000 |
| Akshay Verma | 30 | 9000 |
| Ashley Adams | 50 | 2000 |
| Beth Firch | 60 | 5000 |
and i want to sort it by Users. most occurance user should be on first like AKshay Verma is 3 times in Users column so i want all Akshay Verma on first the second most occurance in users
like this:-
| User | Sale Qty | Amount |
| Akshay Verma | 20 | 1500 |
| Akshay Verma | 45 | 5000 |
| Akshay Verma | 30 | 9000 |
| Ashley Adams | 50 | 3500 |
| Ashley Adams | 50 | 2000 |
| Beth Firch | 35 | 7000 |
| Beth Firch | 60 | 5000 |
| Betty Beth | 10 | 2000 |
| Barney Beckham | 25 | 6000 |
and please guys i can't use excel 365 functions like sort or sortby.
i cannot even use Sorting tool from Data Tab.
i can only use 2016 formula only .. or function
so please suggest me any way to do this task ..
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.
It is really bad luck to be stuck with Excel 2016. The question now is not so much whether one can keep up with the changes, it is more a case of "Can you spot any similarities between modern Excel and legacy versions?"
= LET( count, COUNTIFS(user, user), SORTBY(table,count,-1,user,1) )- U r absolutely right brother.. its my badluck that im stuck with this 2016 version of excel.. but i cant do anything because nintex supports only upto 2016 excel
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.
dsingh2255And besides, "Barney Beckham" comes before "Beth Firch" and not at the bottom of your list. Doesn't it?
- yes it does
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.
- 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
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..Why can't you use the Sort options on the Ribbon?
