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?
With Kutools for Excel’s Select Duplicate & Unique Cells, you can quickly select or shade the duplicate cells or unique values at once.
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.
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:
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.
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.
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.|
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:
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 1 years agoMuchas gracias por la macro!!! me fue muy util
To post as a guest, your comment is unpublished.· 1 years agoThis array formula is CORRECT. Data in columns A to C, first result formula in cell D2... This one is different from other array formula that the later is copy formula down and Ctrl+Shift+Enter all formula. However, this array formula should be done the Ctrl+Shift+Enter in first cell and copy down.
To post as a guest, your comment is unpublished.· 1 years agodoes anyone know, for the output, how to make it into several lines but not into one line ? (currently one line result is achieved by worksheetfunction.transpose, but what i want to achieve (as result) is that when select for 3 columns, the returned result are also 3 columns, instead one
To post as a guest, your comment is unpublished.· 1 years agoCan you please submit the correct formula... the VBA function works just fine.
Just for my project, I rather use the correct formula.
To post as a guest, your comment is unpublished.· 2 years agoI love
Extract Unique Values From Multiple Columns With Pivot Table
To post as a guest, your comment is unpublished.· 2 years agohi
i have three unequal columns and want to extract unique cells of first column. how can i do it??
thanks in advance
To post as a guest, your comment is unpublished.· 2 years agohi
i want to extract unique cells from first column when i compare it with another columns (i have three unequal columns), how can i do it?
To post as a guest, your comment is unpublished.· 2 years ago=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"),)&""
It doesn't work
To post as a guest, your comment is unpublished.· 2 years agoHey! Does anyone know why this formula seems to lead to an error after row 87? Like, it works perfectly and then at a certain point it just returns me errors for every row.. which is the worst! Because I'm so close to exactly what I need here...
To post as a guest, your comment is unpublished.· 3 years agoI am using your VBA code, but do not want the box to pop up. In stead I want to define exactly what cell range to use every time and exactly what box to put the output in. The input range and output would be on two different sheets. how do I update the VBA to do so? Thank you!!
To post as a guest, your comment is unpublished.· 4 years agoThank you!!! I have been spending hours trying to do this and to figure out what happened to the Pivot Wizard (other article).
To post as a guest, your comment is unpublished.· 5 years agoIs this formula complete?