Count number of Occurrences

[Anonnymouse]

Hello Excel Experts,

I am not sure if this is possible but Im wondering if I can sort a list based on the number of times a value shows up in the column from largest to smallest.

Like on cell G4 it pastes it in order.

Thank you for the help!

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
3		Total	Identifier	Serial Number
4		5057	Red	1599		5057	Red	1599	1232	Green	987	931	Yellow	11110	2962	Blue	1000	805	Brown	9783	892	Orange	5581
5		2962	Blue	1000		457	Red	1599	537	Green	987	383	Yellow	11110	477	Blue	1000	618	Brown	8853	297	Orange	9783
6		1232	Green	987		373	Red	1599	530	Green	987	381	Yellow	11110	374	Blue	1000	296	Brown	8853
7		931	Yellow	11110		369	Red	1599	517	Green	987	322	Yellow	11110	280	Blue	1000
8		892	Orange	5581		339	Red	1599	487	Green	987	300	Yellow	11110
9		805	Brown	9783		294	Red	1599	377	Green	987
10		618	Brown	8853		293	Red	1599
11		537	Green	987		285	Red	1599
12		530	Green	987		283	Red	1599
13		517	Green	987		7750			3680			2317			4093			1719			1189
14		487	Green	987
15		477	Blue	1000
16		457	Red	1599
17		398	Violet	5581
18		383	Yellow	11110
19		381	Yellow	11110
20		377	Green	987
21		374	Blue	1000
22		373	Red	1599
23		369	Red	1599
24		339	Red	1599
25		322	Yellow	11110
26		300	Yellow	11110
27		297	Orange	9783
28		296	Brown	8853
29		294	Red	1599
30		293	Red	1599
31		285	Red	1599
32		283	Red	1599
33		280	Blue	1000

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2
3		Total	Identifier	Serial Number
4		5057	Red	1599		5057	Red	1599	1232	Green	987	931	Yellow	11110	2962	Blue	1000	805	Brown	9783	892	Orange	5581	398	Violet	5581
5		2962	Blue	1000		457	Red	1599	537	Green	987	383	Yellow	11110	477	Blue	1000	618	Brown	8853	297	Orange	9783
6		1232	Green	987		373	Red	1599	530	Green	987	381	Yellow	11110	374	Blue	1000	296	Brown	8853
7		931	Yellow	11110		369	Red	1599	517	Green	987	322	Yellow	11110	280	Blue	1000
8		892	Orange	5581		339	Red	1599	487	Green	987	300	Yellow	11110
9		805	Brown	9783		294	Red	1599	377	Green	987
10		618	Brown	8853		293	Red	1599
11		537	Green	987		285	Red	1599
12		530	Green	987		283	Red	1599
13		517	Green	987		7750			3680			2317			4093			1719			1189			398
14		487	Green	987
15		477	Blue	1000
16		457	Red	1599
17		398	Violet	5581
18		383	Yellow	11110
19		381	Yellow	11110
20		377	Green	987
21		374	Blue	1000
22		373	Red	1599
23		369	Red	1599
24		339	Red	1599
25		322	Yellow	11110
26		300	Yellow	11110
27		297	Orange	9783
28		296	Brown	8853
29		294	Red	1599
30		293	Red	1599
31		285	Red	1599
32		283	Red	1599
33		280	Blue	1000
34
Sheet4

Cell Formulas
Range	Formula
F4:Z13	F4	=LET(u,UNIQUE(C4:C33),co,COUNTIFS(C4:C33,u),DROP(REDUCE("",SORTBY(u,co,-1),LAMBDA(x,y,HSTACK(x,VSTACK(EXPAND(SORT(FILTER(B4:D33,C4:C33=y),,-1),MAX(co),,""),EXPAND(SUMIFS(B:B,C:C,y),,3,""))))),,1))
Dynamic array formulas.

 

[Anonnymouse]

Hey Fluffy thank you for the help! This worked although when i try to use a bigger dataset 1000000 cells it does not calculate? is it because its going horizontal?

 

[Fluff]

Do you really have 1 million rows of data?

 

[Anonnymouse]

Do you really have 1 million rows of data?

Sorry I meant 100k

 

[Fluff]

Do you have that many rows of data?

 

[Anonnymouse]

Do you have that many rows of data?

Hey Fluff,

Yeah I have about 100k not sure if it would help the formula if it just pulls the top 50 colors and show how many times they populate on the list.

 

[Fluff]

Ok, try

=LET(u,UNIQUE(FILTER(C4:C100000,C4:C100000<>"")),co,COUNTIFS(C:C,u),DROP(REDUCE("",TAKE(SORTBY(u,co,-1),50),LAMBDA(x,y,HSTACK(x,VSTACK(EXPAND(SORT(FILTER(B4:D100000,C4:C100000=y),,-1),MAX(co),,""),EXPAND(SUMIFS(B:B,C:C,y),,3,""))))),,1))

 

[Anonnymouse]

Hey Fluff, this worked perfectly thank you very much!

 

[Fluff]

You're welcome & thanks for the feedback.