The UNIQUE function in Excel is used to extract unique values from a range or array. Below are 10 examples of how to use the UNIQUE function, including data and formulas:
Example 1: Basic Unique Values from a Column
Data:
A 1 Apple 2 Banana 3 Apple 4 Orange 5 Banana
Formula:
=UNIQUE(A1:A5)
Result:
Apple Banana Orange
Example 2: Unique Values from a Row
Data:
A B C D 1 Apple Banana Apple Orange
Formula:
=UNIQUE(A1:D1)
Result:
Apple Banana Orange
Example 3: Unique Values from a 2D Range
Data:
A B 1 Apple Red 2 Banana Yellow 3 Apple Green 4 Orange Orange
Formula:
=UNIQUE(A1:B4)
Result:
Apple Red Banana Yellow Apple Green Orange Orange
Example 4: Unique Values Based on a Single Column
Data:
A B 1 Apple Red 2 Banana Yellow 3 Apple Green 4 Orange Orange
Formula:
=UNIQUE(A1:A4)
Result:
Apple Banana Orange
Example 5: Unique Values with Multiple Columns
Data:
A B 1 Apple Red 2 Banana Yellow 3 Apple Green 4 Orange Orange
Formula:
=UNIQUE(A1:B4, TRUE)
Result:
Apple Red Banana Yellow Apple Green Orange Orange
Example 6: Unique Values with a Filter
Data:
A B 1 Apple 10 2 Banana 20 3 Apple 30 4 Orange 40
Formula:
=UNIQUE(FILTER(A1:A4, B1:B4>15))
Result:
Banana Orange
Example 7: Unique Values with SORT
Data:
A 1 Apple 2 Banana 3 Apple 4 Orange 5 Banana
Formula:
=SORT(UNIQUE(A1:A5))
Result:
Apple Banana Orange
Example 8: Unique Values with COUNTIF
Data:
A 1 Apple 2 Banana 3 Apple 4 Orange 5 Banana
Formula:
=UNIQUE(A1:A5)&" ("&COUNTIF(A1:A5, UNIQUE(A1:A5))&")"Result:
Apple (2) Banana (2) Orange (1)
Example 9: Unique Values with CONCATENATE
Data:
A B 1 Apple Red 2 Banana Yellow 3 Apple Green 4 Orange Orange
Formula:
=UNIQUE(A1:A4 & " - " & B1:B4)
Result:
Apple - Red Banana - Yellow Apple - Green Orange - Orange
Example 10: Unique Values with Dynamic Arrays
Data:
A B 1 Apple Red 2 Banana Yellow 3 Apple Green 4 Orange Orange
Formula:
=UNIQUE(FILTER(A1:B4, B1:B4="Red"))
Result:
Apple Red
These examples demonstrate the versatility of the UNIQUE function in Excel. You can combine it with other functions like FILTER, SORT, and COUNTIF to achieve more advanced results.
No comments:
Post a Comment