Skip to main content

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 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"),)&""
Note: In the above formula, A2:C9 indicates the range of cells which you want to extract the unique values, E1:E1 is the first cell of the column you want to place the result, $2:$9 stands fro the rows contain the cells you want to use, and $A:$C indicates the columns contain the cells you want to use. Please change them to your own.

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:

Explanation of this formula:
  1. $A$2:$C$9: This specifies the data range to be checked, which is the cells from A2 to C9.
  2. IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0), ROW($2:$9)*100+COLUMN($A:$C), 7^8):
    • $A$2:$C$9<>"" checks whether the cells in the range are not empty.
    • COUNTIF($E$1:E1,$A$2:$C$9)=0 determines if the values of these cells have not yet been listed in the range of cells from E1 to E1.
    • If both conditions are met (i.e., the value is not empty and not yet listed in column E), the IF function calculates a unique number based on its row and column (ROW($2:$9)*100+COLUMN($A:$C)).
    • If the conditions are not met, the function returns a large number (7^8), which serves as a placeholder.
  3. MIN(...): Finds the smallest number returned by the IF function above, corresponding to the location of the next unique value.
  4. TEXT(...,"R0C00"): Converts this minimum number into an R1C1 style address. The format code R0C00 indicates the conversion of the number into Excel cell reference format.
  5. INDIRECT(...): Uses the INDIRECT function to convert the R1C1 style address generated in the previous step back to a normal A1 style cell reference. The INDIRECT function allows cell referencing based on the content of a text string.
  6. &"": Appending &"" at the end of the formula ensures the final output is treated as text, so even numbers will be displayed as text.
 
Extract unique values from multiple columns with formula for Excel 365

Excel 365 supports 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.

Note: To use this Kutools AI Aide of Kutools for Excel, please download and install Kutools for Excel first.

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. 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 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:

  • Count The Number Of Unique And Distinct Values From A List
  • Supposing, you have a long list of values with some duplicate items, now, you want to count the number of unique values (the values that appear in the list only once ) or distinct values (all different values in the list, it means unique values +1st duplicate values) in a column as left screenshot shown. This article, I will talk about how to deal with this job in Excel.
  • Extract Unique Values Based On Criteria In Excel
  • Supposing, you have the following data range that you want to list only the unique names of column B based on a specific criterion of column A to get the result as below screenshot shown. How could you deal with this task in Excel quickly and easily?
  • Only Allow Unique Values In Excel
  • If you want to keep only unique values entering in a column of worksheet and prevent the duplicates, this article will introduce some quick tricks for you to deal with this task.
  • Sum Unique Values Based On Criteria In Excel
  • For example, I have a range of data which contains Name and Order columns, now, to sum only unique values in Order column based on the Name column as following screenshot shown. How to solve this task quickly and easily In Excel?

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (31)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Thank you for this solution, however what if the columns are in separate excel sheet? or is in separate columns instead of a table?
This comment was minimized by the moderator on the site
Hello, Jon,
The methods in this article ar only works well for a range of data, if your data in separate columns, you should copy and paste them into one range first, and then apply the formula or VBA code.
Thank you!
This comment was minimized by the moderator on the site
Thank you for this great article.

For people who are using the array formular in non-English Excel there must be taken special care of the text format string: in your example: "R0C00".
For German this would translate to "Z0S00". However, "S" is a special character refering to seconds for time formating. This character needs to be escaped and therefore the correct format string for German Excel is "Z0\S00".

I hope this helps someone in the future :-)
This comment was minimized by the moderator on the site
Hello, Michael H.
Thanks for your kindly explanation.
Hope this can help others in the future.😄
This comment was minimized by the moderator on the site
Thank you for this great article.

For people who are using the array formular in non-English Excel there must be taken special care of the text format string: in your example: "R0C00".
For German this would translate to "Z0S00". However, "S" is a special character refering to seconds for time formating. This character needs to be escaped and therefore the correct format string for German Excel is "Z0\S00".

I hope this helps someone in the future :-)
This comment was minimized by the moderator on the site
Hello Sir! The VBA worked wonders, thank you very much for that! I was wondering, If I change the original data, is it possible to refresh the column with the unique values automatically?
Rated 5 out of 5
This comment was minimized by the moderator on the site
Hello Ioannis,

Glad to help. After you change the original data, the VBA can not refresh the result automatically. And the easiest way I can think of is to press Ctrl + Alt + F9 to refresh all results in worksheets in all open workbooks. Have a nice day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
The array formula at the top is working great when used with data in the same sheet, however when I try to use it to reference the same exact data from another sheet the formula returns nothing. I'm unable to figure out why. Is there a limitation with array functions that prevents you from referencing ranges in a different sheet?

Thanks for any insight you can provide.
This comment was minimized by the moderator on the site
Hello Erin,

Glad to help. The INDIRECT function in this formula is more complicated to use when referencing data in other worksheets. It is not recommended to use this feature when referencing ranges in different worksheets.

For example: Now the data is in Sheet1, I want to reference the content of cell C2 of Sheet1 in Sheet2. First, in any two cells in Sheet2, such as D1 and D2, enter Sheet1 and C2, respectively. At this point, enter the formula in the empty cell of Sheet2:
=INDIRECT("'"&D1&"'!"&D2), then the content of cell C2 in Sheet1 can be returned.

As you can see, it make things way more complex. Hope my explanation can help. Have a nice day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
Czy to żart?
This comment was minimized by the moderator on the site
can we create uniqdata function instead of macro?
This comment was minimized by the moderator on the site
Hi, İlhan,If you like a User Defined Function to create a formula for solving this problem, the below code may help you:After inserting the code, select a list of cells where you want to put the results. Then type this formula:=Uniques(A1:C4)  in the formula bar.Press Ctrl+Shift+Enter keys together. 


Function Uniques(ByVal inputRange As Range)
Dim inputArray As Variant
Dim myColl As New Collection
Dim xVal As Variant
Dim outArray() As Variant
On Error Resume Next
With inputRange
inputArray = Application.Intersect(.Cells, .Parent.UsedRange).Value
End With
On Error GoTo 0
On Error Resume Next
For Each xVal In inputArray
myColl.*** Item:=xVal, Key:=(CStr(xVal) & TypeName(xVal))
Next xVal
myColl.Remove "String"
On Error GoTo 0
ReDim outArray(1 To Application.Max(myColl.Count, Application.Caller.Cells.Count))
For xVal = 1 To UBound(outArray)
outArray(xVal) = vbNullString
Next xVal
For xVal = 1 To Application.Min(myColl.Count, Application.Caller.Cells.Count)
outArray(xVal) = myColl(xVal)
Next xVal
If Application.Caller.Columns.Count = 1 Then
Uniques = Application.Transpose(outArray)
Else
Uniques = outArray
End If
End Function
This comment was minimized by the moderator on the site
Thanks for the code. I'm using the VBA code of this page. Is there a way to add a sorting code after the unique values are extracted so it sorts it automatically?
This comment was minimized by the moderator on the site
Regarding the formula version, could you explain in more detail what this portion is doing? *100+COLUMN($A:$C),7^8)),"R0C00") Specifically, what are the *100, 7^8, and "R0C000" doing? I'm understanding everything else, but I can't figure out what these are for.
This comment was minimized by the moderator on the site
Little late for my response here but...
ROW($2:$9)*100 - this is multiplying the row number *100, so if it's in row 5, now the number is 500
COLUMN($A:$C) - this gets added to the row*100 number, so if it's row 5 col 2, then the number is 502.
7^8)), - this (I think) is to have a max value for the min statement from earlier.
"R0C00") - this formats the text based on the number. In the example, we had 502 so this gives R5C02 (row 5, col 02).

If you have a lot of columns but not many rows, then you could change it to ROW($2:$9)*1000+COLUMN($A:$C),7^8)),"R0C000")
This comment was minimized by the moderator on the site
i've adjusted to my sheet but am only returning the first value in the defined array... what am i missing?
This comment was minimized by the moderator on the site
Hello, Cody,
The above formula works well in my worksheet, could you give a screenshot of your data problem here?
Thank you!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations