|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

UNIQUE

UNIQUE

  • Last updated on

Description

Use this function to extract a list of unique values in a list or range. Values can be text, numbers, dates, times, etc.

Syntax

UNIQUE(array, [by_col], [exactly_once])

Inputs

This function accepts the following argument:

Name Required Description Valid Input
array Yes The range or array to be examined. A cell, a cell range, or a formula which results in either of these.
by_col No How to compare and extract.
By row = FALSE (default); by column = TRUE.		 A cell reference, a logical value (TRUE or FALSE), a number (0 is FALSE, other numbers are TRUE), or a formula which results in any of these.
exactly_once No FALSE (0)= all unique values (default). TRUE (1) = values that occur once. A cell reference, a logical value (TRUE or FALSE), a number (0 is FALSE, other numbers are TRUE), or a formula which results in any of these.

Examples

Sample data

Data set 1

A B C  
1 Grade Name Score  
2 A George 100  
3 B Lina 100  
4 B Ezsther 98  
5 A George 95  
6 B Maria 95  
7 F Garth 92  
8 B LeShawn 91  

Data set 2

A B C D E F G
1 Name Tigers Eagles Cougars Bears Ravens Thunderbirds
2 Score 8 5 7 7 5 6

Data set 3

A B C D  
1 Q1 Data  
2 Name Sales Region Commission  
3 John 45000 North 4500  
4 Alice 52000 South 5200  
5 Q2 Data  
6 Name Sales Region Commission  
7 John 48000 North 4800  
8 Alice 55000 South 5500  

Data set 4

A B C D E  
1 Item Amount Date Price Total  
2 Apples 12 1/8/2023 $0.99 $11.88  
3 Apples 3 10/9/2022 $1.36 $4.08  
4 Apples 1 11/17/2022 $1.55 $1.55  
5 Apples 4 1/1/2023 $1.44 $5.76  
6 Apples 10 11/16/2022 $1.42 $14.20  
7 Pears 32 11/28/2022 $1.41 $45.12  
8 Pears 27 11/24/2022 $1.47 $39.69  
9 Pears 15 11/13/2022 $1.50 $22.50  
10 Peaches 14 11/13/2022 $1.02 $14.28  
11 Peaches 12 10/1/2022 $1.58 $18.96  
12 Peaches 10 12/7/2022 $1.17 $11.70  
13 Peaches 17 10/9/2022 $1.02 $17.34  
14 Kiwis 31 11/23/2022 $0.97 $30.07  
15 Kiwis 28 11/4/2022 $1.49 $41.72  
16 Kiwis 32 10/10/2022 $0.93 $29.76  

Sample formulas

Use case Formula Explanation and Result
Return every value in a range that occurs at least once. =UNIQUE(A1:A8)

This formula returns every value in the range A2:A8 of Table 1 that occurs at least once. For data set 1, this formula would return the following:

Grade

  

A

  

B

  

C

  

F

  
Return every value in a range that occurs only once. =UNIQUE(A2:A8,0,1)

This formula returns every value in the range A2:A8 of Tabl 1 that occurs only once. For data set 1, this formula returns the following:

A
C
F
Return every value in a range that occurs only once. =UNIQUE(B2:G2,TRUE,1)

This formula returns every value in the range B2:G2 of Table 1 that occurs only once. For data set 2, this formula returns the following:

8 6  
Extract unique values from multiple columns =UNIQUE(B2:B8&": "&A2:A8)

This formula returns unique values from multiple columns. For data set 1, this formula would return the following:
(Only 1 instance of "George" that got an "A").

George: A  
Lina: B  
Ezsther: B  
Maria: B  
Garth: F  
LeShawn: B  
Extract an nth-largest value. =LARGE(UNIQUE(C1:C12),3)

This formula returns the third-largest score. UNIQUE creates an array of the scores, and LARGE then selects the 3rd-largest value in that array. For data set 1, this formula would return the following:

95
Combine separate data sets and sum the values for rows that have identical column 1 entries.

This use case requires 2 formulas in adjacent cells, one for the names, and one for the totals.

  1. =UNIQUE(VSTACK(A2:D4, A6:D8))
  2. =SUMIF(A3:A4,UNIQUE(VSTACK(A3:A4,A7:A8)),B3:B4) +SUMIF(A7:A8,UNIQUE(VSTACK(A3:A4,A7:A8)),B7:B8)

Assuming that you want the results in columns E and F, the solution for data set 3 would be as follows:

Formula 1 (in cell E1) returns the UNIQUE names from column A and uses VSTACK to place them in vertically adjacent cells.

Formula 2 (in cell F1) returns the values associated with the unique names. The two SUMIF statements then add those values together, and VSTACK places them in vertically adjacent cells so that they match the names.

The displayed results are as follows:

E F
John 93000
Alice 107000
Extract unique records based on the values in column A while returning the corresponding data from columns A, B, and E.

This use case requires 3 formulas in adjacent cells, one for the item names, one for the count totals and one for the value totals.

  1. =UNIQUE(A2:A16)
  2. =SUMIFS(B2:B16,A2:A16,UNIQUE(A2:A16))
  3. =TEXT(SUMIFS(E2:E16,A2:A16,UNIQUE(A2:A16)),"$#,##0.00")

Assuming that you want the results in columns F, G, and H, the solution for data set 4 would be as follows:
(Title rows have been added for clarity, they are not produced though the formulas.)

Formula 1 (in cell F2) produces the list of unique item names; this returns the UNIQUE names from column A in vertically adjacent cells:

Item
Apples
Pears
Peaches
Kiwis

Formula 2 (in cell G2) uses SUMIFS to produce the total counts associated with each item:

Item Total
Apples 30
Pears 74
Peaches 53
Kiwis 91

Formula 3 (in cell H2) uses SUMIFS to produce the total values associated with each item, and then uses TEXT(&value,"$#,##0.00") to apply appropriate formatting.
Note: This is not the same as applying Currency or Accounting formats.

Item Total Value  
Apples 30 $37.47  
Pears 74 $107.31  
Peaches 53 $62.28  
Kiwis 91 $101.55  

 

Related functions

Here are the other array formula functions supported in Workiva spreadsheets. 

DescriptionUse this function to extract a list of unique values in a list or range. Values can be text, numbers, date...