2 Ways To Get Unique values From a Range In Excel - Excel Tips and Tricks
Discover 2 ways to get unique values from a range in Excel.
In Excel there is no UNIQUE() function call, however, Excel offers various advanced features to manipulate data, including the powerful "Advanced Filter" function that allows users to filter data by unique values or multiple criteria. Another popular function is the "INDEX-MATCH" formula, which is used to look up and retrieve data from a table, providing more flexibility than the traditional "VLOOKUP" function. By mastering these functions, users can efficiently manage and extract valuable insights from their data in Excel.
1) Advanced Filter
2) Index-Match (Array Formula)
Unique Values Using Advanced Filter
1) Select Dataset
2) Data -- Sort and Filter -- Advanced
3) Copy to another location
4) Copy to is $B$1
5) Unique records only
6) OK
You can easily get unique values from your dataset in Microsoft 365 using a unique function like this.
Likewise, in Google sheet, you can use the unique function like this as well.
However, in the Excel standalone application, unique function is not available.
These are the 2 ways to get UNQIUE values from your dataset in the excel standalone application.
Unique Value Using Index-Match (array formula)
1) Select B2
2) Enter formula
=IFERROR(INDEX($A$2:$A$18, MATCH(0,COUNTIF($B$1:B1, $A$2:$A$18), 0)),"")
3) Ctrl + Shift + Enter
Note the curly braces in your formula which denotes array output.
4) Copy and paste the formula
Count number of occurrence
Use this formula
=COUNTIF(A2:A18,B2)
#shorts #short #shortvideo #fyp #excel #microsoft #tiktok #fypシ
