FORMULA TO ARRANGE VALUES IN DECENDING ORDER ACORDING TO THE FREQUENCY OF OCCURANCES

[Analytic]

Hi I would like to request for help,

The problem is that , there are some values in coloumn A , whereas the soluton is to find a folrmula to arrange those values in decending order according to their frequency in coloumn B.

Please note there are some blank cells in coloumn A.

A B
1 4
1 1
1 2
2 5
2
4
4
4
4
5

In the above example , 4 has occured four times in coloumn A so it is in the first row in Coloumn B and also 1 has occured 3 times in Coloumn A so it is in the second row in coloumnB and so on.

Thanks

 

[SanjayGMusafir]

Please update your current Excel version in your profile at the RIGHT TOP and here too

Your solution is totally dependent on the version of Excel you are currently using.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Analytic]

Hi I would like to request for help,

The problem is that , there are some values in coloumn A , whereas the soluton is to find a folrmula to arrange those values in decending order according to their frequency in coloumn B.

Please note there are some blank cells in coloumn A.

A B
1 4
1 1
1 2
2 5
2
4
4
4
4
5

In the above example , 4 has occured four times in coloumn A so it is in the first row in Coloumn B and also 1 has occured 3 times in Coloumn A so it is in the second row in coloumnB and so on.

Thanks

I'am using windows 10 with office 2019 professional plus

 

[SanjayGMusafir]

I'am using windows 10 with office 2019 professional plus

Not sure if there is some sorting method using formula in Excel 2019

 

[lrobbo314]

You could use Power Query.

Analytic.xlsb
	I	J
1	A	Table6
2	1	4
3	1	1
4	1	2
5	2	5
6	2
7	4
8	4
9	4
10	4
11	5
Sheet7

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"A"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    Sort = Table.Sort(Group,{{"Count", Order.Descending}})[A]
in
    Sort

 

[Analytic]

You could use Power Query.

Analytic.xlsb
	I	J
1	A	Table6
2	1	4
3	1	1
4	1	2
5	2	5
6	2
7	4
8	4
9	4
10	4
11	5
Sheet7

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"A"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    Sort = Table.Sort(Group,{{"Count", Order.Descending}})[A]
in
    Sort

Hi, i am kind of a new to power query , so can u please bit elaborate how to insert this code and how to run it in excel, thanks

 

[Fluff]

Whilst I can easily extract the distinct values, I struggling to sort them correctly.

 

[lrobbo314]

First step would be to load the data in Column A into the Power Query Editor. Data Tab -> Get & Transform Data -> Other Sources -> From Table/Range.

Once the table is loaded in you can go to the Home tab and click on Advanced Editor and paste the code I posted.

Then click Close and Load.

 

[Eric W]

How about:

Book1
	A	B
1	List	By Frequency
2	1	4
3	1	1
4	1	2
5	2	9
6	2	5
7	4
8	4
9	4
10	4
11	5
12
13	9
14
15	9
16
17
18
19
20
Sheet3

Cell Formulas
Range	Formula
B2:B8	B2	=IFERROR(MODE(IF(($A$2:$A$20<>"")*(COUNTIF($B$1:$B1,$A$2:$A$20)=0),$A$2:$A$20*{1,1},"")),"")
Press CTRL+SHIFT+ENTER to enter array formulas.