How to Find Unique Values from Multiple Columns in Excel

UNIQUE(), FILTER() and CONCATENATE() function in Excel

Today I will be showing how you can extract unique values from multiple columns in Microsoft Excel.


Download Practice Workbook


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.

A dataset in Excel

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.

UNIQUE() Function 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 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)

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)

UNIQUE() and CONCATENATE() function in Excel

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)

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)

UNIQUE() and CONCATENATE() function in Excel

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)

UNIQUE(), FILTER() and CONCATENATE() functions in Excel

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


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.

Another data set in Excel

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.

Conditional Formatting 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.

Highlighting Duplicate Values in Excel

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.

A Complex Formula in Excel

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.

Pivot Table and Pivot Chart Wizard in Excel

Then click Next. You will move to step 2a of 3. Put a check on Create a single page field for me.

PivotTable and Pivot Chart Wizard in Excel

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.

PivotTable and PivotChart Wizard 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 $G$3.

PivotTable and PivotChart Wizard

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.

Pivot Table in Excel

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

Unique values using Pivot Table in Excel

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.

VBA Toolbar in Excel

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

Code Written in Module in Excel

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.

Macro Box in Excel

Now you will have an input box called KutoolsforExcel. Enter the range of your data in the Range box.

Input Box for VBA code

Click on OK. You will get another input box. Enter the first cell where you want the unique names. I enter cell F3.

Another Input Box for VBA code

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

Unique values extracted by VBA code.

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


Conclusion

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


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo