How to extract unique values from multiple columns in Excel?

Suppose you have several columns with multiple values, where some values are repeated within the same column or across different columns. Now, you need to identify the values that are unique across all the columns. Are there any quick methods to extract these unique values from multiple columns in Excel? Let’s explore some efficient solutions.

• Extract unique values from multiple columns with formulas
  • Array formula (all Excel versions)
  • Formula (Excel 365, Excel 2021, and newer versions)
• Extract unique values from multiple columns with Kutools AI Aide
• Extract unique values from multiple columns with Pivot Table
• Extract unique values from multiple columns with VBA code

Extract unique values from multiple columns with formulas

This section will cover two formulas: one using an array formula suitable for all Excel versions, and another using a dynamic array formula specifically for Excel 365.

Extract unique values from multiple columns with Array formula for all Excel versions

For users with any version of Excel, array formulas can be a powerful tool for extracting unique values across multiple columns. Here’s how you can do it:

1. Assuming your values in range A2: C9, please enter the following 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"),)&""

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

Extract unique values from multiple columns with formula for Excel 365, Excel 2021, and newer versions

Excel 365, Excel 2021, and newer versions support dynamic arrays, making it much easier to extract unique values from multiple columns:

Please enter or copy the following formula into a blank cell where you want to put the result, and then click Enter key to get all the unique values at once. See screenshot:

=UNIQUE(TOCOL(A2:C9,1))

Extract unique values from multiple columns with Kutools AI Aide

Unleash the power of "Kutools AI Aide" to seamlessly extract unique values from multiple columns in Excel. With just a few clicks, this intelligent tool sifts through your data, identifying and listing unique entries across any selected range. Forget the hassle of complex formulas or vba code;Embrace the efficiency of "Kutools AI Aide" and transform your Excel workflow into a more productive and error-free experience.

After installing Kutools for Excel, please click "Kutools AI" > "AI Aide" to open the "Kutools AI Aide" pane:

1. Type your requirement into the chat box, and click "Send" button or press Enter key to send the question;
   "Extract unique values from the range A2:C9, ignoring blank cells, and place the results starting at E2:"
2. After analyzing, click "Execute" button to run. Kutools AI Aide will process your request using AI and return the results in the specified cell directly in Excel.

Extract unique values from multiple columns with Pivot Table

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 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. Finally, click "Finish" to complete the wizard, and a Pivot Table will be created in the 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 "Rows" label, 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()
'Updateby Extendoffice
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.

More relative articles: