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
Precaution: UNIQUE function is only available in Office 365.
Syntax of UNIQUE Function:
- 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.
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.
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.
Or, you can use this-
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:
- 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
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-
=UNIQUE(FILTER(CONCATENATE(C5:C16," ",D5:D16),B5:B16>150,"no result"),FALSE,TRUE)
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.
- 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
- 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.
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.
- 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.
- 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.
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.