How to Find Unique Values from Multiple Columns in Excel (5 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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


Find Unique Values from Multiple Columns in Excel (5 Easy Ways)

In this part, we will provide five different ways to get unique values in Excel from multiple columns. 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

Precaution: The 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 an 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 This argument is optional. The default is TRUE.
  • If exactly_once is set to TRUE, returns the values which appear only once in the array. This argument is optional. The default is FALSE.

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.

=UNIQUE(C5:D16,FALSE,TRUE)

Apply UNIQUE function to find unique values in Excel

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 that appear only once.

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


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:

=UNIQUE(CONCATENATE(C5:C16," ",D5:D16),FALSE,TRUE)

Alternative Formula:

Or, you can use this-

=UNIQUE(C5:C16&" "&D5:D16,FALSE,TRUE)

Combination of UNIQUE & CONCATENATE functions to find unique values in Excel

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


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.

Precaution: The 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 an array, one boolean condition called include, and one value called
  • Returns the values from the array which meet the condition specified by the
  • If any value of the array does not fulfill 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.

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

  • So, our formula will be

=UNIQUE(FILTER(C5:D16,B5:B16>150,"no result"),FALSE,TRUE)

Combination of UNIQUE & FILTER functions to find unique values in Excel

See we have extracted the first and last names of the unique names.

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

=UNIQUE(FILTER(CONCATENATE(C5:C16," ",D5:D16),B5:B16>150,"no result"),FALSE,TRUE)

Combination of UNIQUE, FILTER & CONCATENATE functions to find unique values 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.

Apply Conditional Formatting to find unique values in Excel

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

=IFERROR(IFERROR(LOOKUP(2, 1/(COUNTIF($F$4:F4,$B$5:$B$11)=0), $B$5:$B$11), LOOKUP(2, 1/(COUNTIF($F$4:F4, $C$5:$C$9)=0), $C$5:$C$9)),LOOKUP(2, 1/(COUNTIF($F$4:F4, $D$5:$D$12)=0), $D$5:$D$12))

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

Combination of IFERROR, COUNTIF & LOOKUP functions to find unique values in Excel

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

Apply Pivot Table to find unique values in Excel

  • 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

Apply VBA Macro to find unique values in Excel

This site helped us understand and develop the code.

  • Save it as an 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.


Download Practice Workbook

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


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.


<< Go Back to Unique Values | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo