Extract a unique distinct list across multiple columns and rows sorted based on frequency
Cell range B2:E11 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:
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))
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]
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.