Extract a unique distinct list across multiple columns and rows sorted based on frequency
This article demonstrates a formula that creates a frequency distribution table from a multi-column cell range which is useful in statistics.
The image above shows a cell range B2:E11 that contains values, the formula in cell B15 extracts unique distinct values in B2:E11, ignores blanks, and returns a list sorted based on frequency.
Dynamic array formula in cell B15:
Check out How to create a frequency table based on text values if your data is arranged in a single column.
Explaining formula
Step 1 - Rearrange values to a single column
The TOCOL function rearranges values in a 2D cell range to a single column.
TOCOL(array, [ignore], [scan_by_col])
TOCOL(B2:E11)
returns
Step 2 - Filter out blanks
The FILTER function extracts values/rows based on a condition or criteria.
FILTER(array, include, [if_empty])
FILTER(TOCOL(B2:E11), TOCOL(B2:E11)<>"")
returns
The empty cells are now gone in cell B13 and cells below.
Step 3 - Extract unique values
The UNIQUE function extracts both unique and unique distinct values and also compare columns to columns or rows to rows.
UNIQUE(array,[by_col],[exactly_once])
UNIQUE(FILTER(TOCOL(B2:E11), TOCOL(B2:E11)<>""))
returns
{"Banana"; "Lime"; "Pineapple"; "Strawberry"; "Orange"; "Pear"; "Raspberry"; "Apple"}.
Step 4 - Count values
The COUNTIF function calculates the number of cells that meet a given condition.
COUNTIF(range, criteria)
COUNTIF(B2:E11, UNIQUE(FILTER(TOCOL(B2:E11), TOCOL(B2:E11)<>"")))
becomes
COUNTIF(B2:E11, {"Banana"; "Lime"; "Pineapple"; "Strawberry"; "Orange"; "Pear"; "Raspberry"; "Apple"})
and returns
{6; 3; 6; 4; 4; 3; 3; 5}.
Step 5 - Sort values based on the frequency
The SORTBY function sorts values from a cell range or array based on a corresponding cell range or array.
SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)
SORTBY(UNIQUE(FILTER(TOCOL(B2:E11), TOCOL(B2:E11)<>"")), COUNTIF(B2:E11, UNIQUE(FILTER(TOCOL(B2:E11), TOCOL(B2:E11)<>""))), -1)
becomes
SORTBY({"Banana"; "Lime"; "Pineapple"; "Strawberry"; "Orange"; "Pear"; "Raspberry"; "Apple"}, {6; 3; 6; 4; 4; 3; 3; 5}, -1)
and returns
{"Banana"; "Pineapple"; "Apple"; "Strawberry"; "Orange"; "Lime"; "Pear"; "Raspberry"}.
Step 6 - Shorten the formula
The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.
LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])
SORTBY(UNIQUE(FILTER(TOCOL(B2:E11), TOCOL(B2:E11)<>"")), COUNTIF(B2:E11, UNIQUE(FILTER(TOCOL(B2:E11), TOCOL(B2:E11)<>""))), -1)
x - B2:E11
y - TOCOL(x)
z - UNIQUE(FILTER(y,y<>""))
LET(x,B2:E11,y,TOCOL(x),z,UNIQUE(FILTER(y,y<>"")),SORTBY(z, COUNTIF(x,z),-1))
Useful resources
How to make a frequency distribution table in Excel - pivot table
How to Make a Histogram in Excel (Step-by-Step Guide)
Frequency table category
Excel 2013 allows you to count unique distinct values in a pivot table, this article explains how to count unique […]
What's on this page Unique distinct values sorted based on frequency (single column) Unique distinct values sorted based on frequency […]
In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The […]
Excel categories
4 Responses to “Extract a unique distinct list across multiple columns and rows sorted based on frequency”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
How can we do that in Excel 2010? without VBA or PowerTools or PowerPivot etc. but only using worksheet formulas. I can do it in VBA. I am asking because it's very hard to figure out using only worksheet formulas. Thanks in advance. Since dates were not shown, I would like to record my request as made on 07MAY2023.
T S,
This article will probably show you how: Unique distinct values sorted based on frequency if you can rearrange your values to a single column.
I don't have the luxury of rearranging to a single column.
So, I modified your formula at https://www.get-digital-help.com/unique-distinct-list-sorted-based-on-occurrance-in-a-column-in-excel/ to become like https://imgur.com/jCvNhdR .
Further explanation can be found at https://stackoverflow.com/questions/76405604/excel-formula-to-extract-a-sorted-list-of-topn-unique-distinct-string-text-val/76405605#76405605 .
This reply was posted on 05JUN2023 though I finished my formula 5 days after you replied.
Thank you for sharing your answer in the first place.
Allow me to share your great website with others.
This site contains many helpful articles like a treasure chest in a dungeon!
I do appreciate your kind efforts.
Thanks again.
Tragic Shadow,
thank you.