# How to Find Unique Values from Multiple Columns in Excel Today I will be showing how you can extract unique values from multiple columns in Microsoft Excel.

## How to Find Unique Values from Multiple Columns in Excel

### Find Unique Values From Multiple Columns with Different Types of Data

Let us have a look at this Data set. We have the students record of a school named Glory Kindergarten. We have the Student IDs, First Names and Last Names of the students in column B, C and D respectively.

Now we want to sort out the unique names from the students.

#### 1. Getting Unique Values in Multiple Cells Using UNIQUE() Function

Precaution: UNIQUE() function is only available in office 365.

Syntax of UNIQUE() Function

`=UNIQUE(array,[by_col],[exactly_once])`
• Takes three arguments, one range of cells called array, and two Boolean values called by_col and exactly_once.
• Returns the unique values from the array.
• If by_col is set to TRUE, it searches for the unique values by the columns of the array. This argument is optional. Default is TRUE.
• If exactly_once is set to TRUE, returns the values which appear only once in the array. This argument is optional. Default is FALSE.

If you want to know more about UNIQUE() Function, visit this link.

Now we want to extract the unique values from both the First Names (Column C) and the Last Names(Column D).

So, select a cell an insert this formula there

`=UNIQUE(C4:D15,FALSE,TRUE)`

I select cell E4 and enter it there. See we have got the Unique Names in two different columns.

• Here we have inserted by_col as FALSE, so it did not search along the columns
• Here we have inserted exactly_once as TRUE, so it did return the values which appear only once.

Of course, if you want, you can change those boolean values called by_col and exactly_once and see what happens.

#### 2. Getting Unique Values in One Cell Using CONCATENATE() and UNIQUE() Functions

Earlier, we got the First Name in one cell, and the Last Name in the adjacent cell.

But if one asks for the complete name is one cell, for example, Jack Morris. Then?

Use this formula

`=UNIQUE(CONCATENATE(C4:C15," ",D4:D15))`

Or you can use this

`=UNIQUE(C4:C15&""&D4:D15)` See, we have extracted the full unique names in one column separated by space( ).

#### 3. Getting Unique Values Maintaining a Specific Criteria Using UNIQUE() and FILTER() Functions

Now assume for a moment, one wants to extract the unique names of the students whose IDs are greater than 150.

How to do that?

We will do that using UNIQUE() and FILTER() functions.

Precaution: FILTER() function is only available in office 365.

Syntax of FILTER() Function

`=FILTER(array,include,[if_empty])`
• Takes three arguments. One range of cells called array, one boolean condition called include, and one value called if_empty.
• Returns the values from the array which meets the condition specified by the include.
• If any value of the array does not fulfil the condition specified by the include, it returns the value if_empty for it. Setting if_empty is optional. It is “no result” by default.

If you want to know more about the FILTER() function, visit this link.

And to know about the UNIQUE() function, go to section 1.

Now we want to extract the unique names of the students whose IDs are greater than 150.

So, our formula will be

`=UNIQUE(FILTER(C4:D15,B4:B15.150,"no result"),FALSE,TRUE)` See we have extracted out the first and last names of the unique names.

And if you want to extract out the full unique names in one cell, use this formula

`=``UNIQUE(FILTER(CONCATENATE(C4:C15," ",``D4:D15``),``B4:B15``>150,"no result"),FALSE,TRUE)` ### Find Unique Values From Multiple Columns with Same Type of Data

Let us have a look at this new data set. We have three columns, but all with the same type of data. We have the nicknames of some students of the Glory Kindergarten School.

Now we want to find out the unique names among these students.

How can we do that?

Here are the methods you can follow.

#### Highlighting Duplicate Values using Conditional Formatting

Before starting any method, you can highlight the duplicate values using Conditional Formatting, for convenience.

First select the range of the cells.

Then go to Home>Conditional Formatting>Highlight Cells Rules>Duplicate Values. You will get a small box called Duplicate Values. Select any color from there to highlight the duplicate values. I am selecting green. #### 1. Using Formula

Select any cell. Then insert this formula

`=IFERROR(IFERROR(LOOKUP(2, 1/(COUNTIF(\$G\$2:G2,\$B\$3:\$B\$9)=0), \$B\$3:\$B\$9), LOOKUP(2, 1/(COUNTIF(\$G\$2:G2, \$C\$3:\$C\$7)=0), \$C\$3:\$C\$7)),LOOKUP(2, 1/(COUNTIF(\$G\$2:G2, \$D\$3:\$D\$10)=0), \$D\$3:\$D\$10))`

Here I insert it in cell F3.

Then drag the Fill Handle and you will find out the unique names. Note: Here, in lieu of column B, C and D, you can use your preferred ones.

#### 2. Using Pivot Table

Press Alt + D. Then press P immediately. You will get the PivotTable and PivotChart Wizard opened. Put check on Multiple consolidation ranges and Pivot table. Then click Next. You will move to step 2a of 3. Put a check on Create a single page field for me. Then click Next. You will go to step 2b. In the Range box, select the range of your cells with an empty column in the left.

Here I have selected cells A3 to D10.

Then click Add. Your selected cells will be added in the All ranges box. Then click Next. You will move to step 3. In the Existing worksheet box, write down the cell where you want the Pivot Table. I write \$G\$3. Then click Finish. You will get a Pivot Table created. In the Choose fields to add to report part, uncheck Row, Column, Value, Page 1. Then put a check on Value. You will get the unique names in the Pivot Table. #### 4. Using VBA Code

You can use a VBA code to extract unique names from the data set.

Press Alt + F11 on your workbook to open the VBA window.

Then go to Insert option in the VBA toolbar. Click it. From the four options, choose Module. You will a get new Module window. Write the following code there.

``````Sub Uniquedata()
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
If rng.Value <> "" Then
dt(rng.Value) = ""
End If
Next
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub`````` This site helped us understand and develop the code.

Save it as Excel Macros Enabled Workbook.

Then come back to your original worksheet. Press Alt + F8.

You will get the Macro box opened. Select the name of the Macro and then click on Run. Here the name of this Macro is Uniquedata. Now you will have an input box called KutoolsforExcel. Enter the range of your data in the Range box. Click on OK. You will get another input box. Enter the first cell where you want the unique names. I enter cell F3. Then click OK. You will get the unique names from your data set. ## Conclusion

Using these methods, you can extract unique values from multiple columns having both the same or different types of data.  