List the Common Values in A Column

[austinandreikurt]

Hi, I have a list of values, might be numbers or text or group of words, but they will be just considered as value in a cell individually from A2:A300. I want to list the top 5 of the most commonly used values from the list and put them in Cell B1 only. Please make the formula easy to edit if incase I will need the top 4 only. But max will be top 5. Thank you!

 

[sandy666]

something like this with defined Top number

Top		Top	List
5		Top	5811 | 5028 | 6772 | aaa9 | aaa8

Top		Top	List
4		Top	5028 | 5811 | 6772 | aaa9

post representative example with XL2BB or link to the shared excel file via dropbox, googledrive, onedrive or any similar service

 

[Peter_SSs]

What do you want to happen if there are ties in 5th place?
Example below. Clearly you would want b, c, 2 and 3 returned but for the 5th place do you want x and 1 or just pick one of those or what?

austinandreikurt 1.xlsm
	A	B	C	D
1	Value		Value	Count
2	x		x	3
3	1		b	4
4	x		c	4
5	1		1	3
6	x		2	4
7	1		3	4
8	b		4	1
9	b
10	b
11	b
12	c
13	c
14	c
15	c
16	2
17	2
18	2
19	2
20	3
21	3
22	3
23	3
24	4
Sheet1

Cell Formulas
Range	Formula
D2:D8	D2	=COUNTIF(A$2:A$24,C2)

 

[austinandreikurt]

Hi sandy666, Your format for the toplist is what I'm looking for. I do not have the data now but this is a sample:

Book1
	A	B	C	D
1	SAMPLE DATA		TOP	RESULT
2	Shirt		4
3	Shirt
4	Pants
5	Shirt
6	123
7	123
8	Pants
9	Pants
10	Shirt
11	Dress
12	Apple
13	Shirt
14	Pants
15	Pants
16	x
17	x
18	x
19	Shirt
20	123
Sheet1

 

[austinandreikurt]

Hi Peter, will it be possible to do both as it will still depend on the user's decision so I can easily edit it based on their requirement. Also, there should be no helper columns to count and list the unique values of the data as there will be a lot of sheets to process and it be very tedious to create a lot of helper columns.

 

[sandy666]

Raw		Top		List
Shirt		4		Shirt | Pants | x | 123
Shirt
Pants
Shirt
123
123
Pants
Pants
Shirt
Dress
Apple
Shirt
Pants
Pants
x
x
x
Shirt
123

//not optimised
let
    Def = Excel.CurrentWorkbook(){[Name="DefTop"]}[Content]{0}[Top],
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Grp1 = Table.Group(Source, {"Raw"}, {{"Count", each Table.RowCount(_), type number}}),
    SortD = Table.Sort(Grp1,{{"Count", Order.Descending}}),
    Top = Table.FirstN(SortD,Def),
    TopList = Table.AddColumn(Top, "Top", each "Top"),
    Grp2 = Table.Group(TopList, {"Top"}, {{"Top.1", each _, type table}}),
    List = Table.AddColumn(Grp2, "List", each List.Distinct([Top.1][Raw])),
    Extract = Table.TransformColumns(List, {"List", each Text.Combine(List.Transform(_, Text.From), " | "), type text}),
    TSC = Table.SelectColumns(Extract,{"List"})
in
    TSC

 

[austinandreikurt]

Hi sandy, is it possible for a simple formula? I was able to install add-in as I'm using excel at home but the office excel has a lot of restriction like downloading add-ins is not allowed, coding is not allowed, etc.

 

[sandy666]

XL2016 and all above have Power Query (Get&Transform) built-in
Add-in is required for XL2010/2013 only
if you want formula you need to wait for someone else

 

[Peter_SSs]

is it possible for a simple formula?

No.
Certainly not with Excel 2016 & you want all the results in a single cell, including duplicates (where there could be any number of duplicates, not just two) & no helper columns & no vba.
It is a big ask for a simple formula to do a complex job.

With Excel 2016 I would looking to employ a vba user-defined function or sandy666's approach.

Another possible approach is a Pivot Table filtered for top n values, but it still does not put all the results in a single cell.

austinandreikurt 1.xlsm
	A	B	C	D	E	F	G	H
1	SAMPLE DATA		TOP	RESULT			Row Labels	Count of SAMPLE DATA
2	Shirt		4				Shirt	6
3	Shirt						Pants	5
4	Pants						x	3
5	Shirt						123	3
6	123						Grand Total	17
7	123
8	Pants
9	Pants
10	Shirt
11	Dress
12	Apple
13	Shirt
14	Pants
15	Pants
16	x
17	x
18	x
19	Shirt
20	123
Sheet2

 

[austinandreikurt]

Mine is Excel 2016 but office excel is 2013 only. Okay, still thanks for attending the question. I can create the VBA codes myself unfortunately, vba is not an option to us. I'll be waiting then for a working formula just in case. Thank you.