How to extract unique values from multiple columns in Excel?

Supposing you have several columns with multiple values, some values are repeated in same column or different column. And now you want to find the values which are present in either column only once. Are there any quick tricks for you to extract unique values from multiple columns in Excel?

Extract unique values from multiple columns with array formula

Extract unique values from multiple columns with Pivot Table

Extract unique values from multiple columns with VBA code

Extract unique values from one single column with Kutools for Excel

With Kutools for Excel’s Select Duplicate & Unique Cells, you can quickly select or shade the duplicate cells or unique values at once.

Extract unique values from multiple columns with array formula

Here is an array formula also can help you to extract the unique values from multiple columns.

1. Assuming your values in range A2: C9, please enter this formula into cell E2, =INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&"",see screenshot:

Note: In the above formula, A2:C9 indicates the values which you want to extract the unique values, you can change it to your need, E1:E1 is the first cell of the column you want to place the result.

2. Then press Shift + Ctrl + Enter keys together, and then drag the fill handle to extract the unique values until blank cells appear.

Extract unique values from multiple columns with Pivot Table

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!

Save 50% of your time, and reduce thousands of mouse clicks for you every day!

If you are familiar with the pivot table, you can easily extract the unique values form multiple columns with following steps:

1. At first, please insert one new blank column at the left of your data, in this example, I will insert column A beside the original data.

2. Click one cell in your data, and press Alt+D keys, then press P key immediately to open the PivotTable and PivotChart Wizard, choose Multiple consolidation ranges in the wizard step1, see screenshot:

3. Then click Next button, check Create a single page field for me option in wizard step2, see screenshot:

4. Go on clicking Next button, click  button to select the data range which including the left new column of cells, then click Add button to add the data range to the All ranges list box, see screenshot:

5. After selecting the data range, continue click Next, in the wizard step 3, choose where you want to put the PivotTable report as you like.

6. At last, click Finish to complete the wizard, and a pivot table has been created in current worksheet, then uncheck all the fields from the Choose fields to add to report section, see screenshot:

7. Then check the field Value or drag the Value to the Row Labels, now you will get the unique values from the multiple columns as follows:

Extract unique values from multiple columns with VBA code

With the following VBA code, you can also extract the unique values from multiple columns.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA: Extract unique values from multiple columns

Sub Uniquedata()
'Update 20140814
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

3. Then press F5 to run this code, and a prompt box will pop out to remind you select the data range that you want to use. See screenshot:

4. And then click OK, another prompt box will appear to let you choose a place to put the result, see screenshot:

5. Click OK to close this dialog, and all the unique values have been extracted at once.

Extract unique values from one single column with Kutools for Excel

Sometimes, you need to extract the unique values from a single column, the above methods will not help you, here, I can recommend a useful tool-Kutools for Excel, with its Select Duplicate & Unique Cells utility, you can quickly extract the unique values.

After installing Kutools for Excel, please do as follows:（ Free Download Kutools for Excel Now ）

1. Select the data range that you want to extract the unique values.

2. Then click Kutools > Select > Select Duplicate & Unique Cells, see screenshot:

3. In the Select Duplicate & Unique Cells dialog box, choose Unique values only or All unique (Including 1 st duplicates) as your need, then click OK button, and you will get the following results:

		Select only unique values		Select all unique values with first duplicates

4. After selecting the unique values, you just need to hold Ctrl + C to copy the selected values, and then paste them to cells where you want to put the result. All unique values have been extracted as following screenshot shown:

To know more about this Select Duplicate & Unique Cells feature.

Free Download Kutools for Excel Now

Related articles:

How to find unique values between two columns in excel?

How to filter or select unique records from selected range in Excel?

Recommended Productivity Tools

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

•  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
•  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
•  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
•  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
•  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
•  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
•  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
•  Change the way you work now, and give you a better life immediately!

Read More Download Now Purchase

---

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

• Increases your productivity by 50% when viewing and editing multiple documents.
• Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
• Open and create documents in new tabs of same window, rather than in new windows.
• Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!

Read More Download Now Purchase