How to Find Unique Values from Multiple Columns in Excel

In this article, I will be showing how you can find unique values from multiple columns in Microsoft Excel.

Download Practice Workbook

Download the following Excel file so that you can practice while reading this article.

5 Methods to Find Unique Values from Multiple Columns in Excel

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 columns B, C, and D respectively.

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

Method 1: Extract Unique Values from Multiple Columns with Array Formula

i. Using UNIQUE Function

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

• First, select a cell and insert this formula there. I select cell E5 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.

Read More: Excel VBA to Get Unique Values from Column (4 Examples)

ii. Combining 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 any of these formulas. They are made of UNIQUE and CONCATENATE functions.

First Formula:

Alternative Formula:

Or, you can use this-

See, we have extracted the full unique names in one column separated by space( ).

Read More: Find Unique Values in a Column in Excel (6 Methods)

iii. Using UNIQUE, CONCATENATE, and FILTER Functions to Extract Unique Values Based on Criteria

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 the UNIQUE and FILTER functions.

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

• So, our formula will be

See we have extracted 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-

Read More: How to Extract Unique Values Based on Criteria in Excel

Method 2: Highlight Duplicate Values Using Conditional Formatting

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 of these students.

How can we do that?

We can highlight the duplicate values using Conditional Formatting, for convenience.

📌 Steps:

• 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.

Method 3: Extract Unique Values from Excel Column Using a Formula Without Array

To use a non-array formula, you have to combine IFERROR, LOOKUP, and COUNTIF functions. To apply the formula, apply the following steps.

📌 Steps:

• Select any cell.
• Then insert the following formula-

• Here I insert it in Cell F5.
• Then drag the Fill Handle and you will find out the unique names.

Note:

Here, in lieu of columns B, C, and D, you can use your preferred ones.

Method 4: Extract a Unique Distinct List from Two or More Columns Using Pivot Table

You can also create a unique list from two or more columns using pivot table tool. Apply the following steps to do that.

📌 Steps:

• Press Alt + D.
• Then press P immediately. You will get the PivotTable and PivotChart Wizard opened.
• Select Multiple consolidation ranges and Pivot table buttons.

• Then click Next. You will move to Step 2a of 3.
• Select Create a single page field for me button.

• Then click Next. You will go to Step 2b.
• In the Range box, select the range of your cells with an empty column on the left.
• Here I have selected cells B5 to D12.
• Then click Add. Your selected cells will be added to 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 $F$4.

• Then click Finish. You will get a Pivot Table created.
• In the Choose fields to add to report part, unmark Row, Column, Value, Page 1.

• Then put a check on Value. You will get the unique names in the Pivot Table.

Method 5: Use VBA Code to Find Unique Values

Lastly, you can also use a VBA code to extract unique names from the data set. Do the following.

📌 Steps:

• Press Alt + F11 on your workbook to open the VBA window.
• Then go to the Insert tab in the VBA toolbar. Click on 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 = "Select Range"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Output 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.
• 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 F5.

• Then click OK. You will get unique names from your data set.

Read More: How to Get Unique Values from Range in Excel (8 Methods)

Conclusion

Using these methods, you can find unique values in Excel from multiple columns having both the same or different types of data. If you have any further queries, leave us a comment. You can also visit our blog to learn more about various MS Excel topics.

Further Readings

• How to Extract Unique Items from a List in Excel (10 Methods)
• VBA to Get Unique Values from Column into Array in Excel (3 Criteria)