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

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


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.


Extract unique values from one single column with an amazing feature

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 Extract cells with unique values (include the first duplicate) utility, you can quickly extract the unique values.

Note:To apply this Extract cells with unique values (include the first duplicate), firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

1. Click a cell where you want to output the result. (Note:Don't click a cell in the first row.)

2. Then click Kutools > Formula Helper > Formula Helper, see screenshot:

3. In the Formulas Helper dialog box, please do the following operations:

  • Select Text option from the Formula Type drop down list;
  • Then choose Extract cells with unique values (include the first duplicate) from the Choose a fromula list box;
  • In the right Arguments input section, select a list of cells that you want to extract unique values.

4. Then click Ok button, and drag the fill handle over to the cells that you want to list all the unique values until blank cells are displayed, see screenshot:

Free Download Kutools for Excel Now!


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?

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Michael · 1 months ago
    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.
  • To post as a guest, your comment is unpublished.
    Cody · 3 months ago
    i've adjusted to my sheet but am only returning the first value in the defined array... what am i missing?
    • To post as a guest, your comment is unpublished.
      skyyang · 3 months ago
      Hello, Cody,
      The above formula works well in my worksheet, could you give a screenshot of your data problem here?
      Thank you!
  • To post as a guest, your comment is unpublished.
    Fernando · 1 years ago
    Muchas gracias por la macro!!! me fue muy util
  • To post as a guest, your comment is unpublished.
    Hannah Li · 1 years ago
    This 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.
    mingji · 1 years ago
    does 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.
    stephan · 2 years ago
    Can you please submit the correct formula... the VBA function works just fine.
    Just for my project, I rather use the correct formula.


    Thanks
  • To post as a guest, your comment is unpublished.
    cena.30000@gmail.com · 2 years ago
    I love

    Extract Unique Values From Multiple Columns With Pivot Table
  • To post as a guest, your comment is unpublished.
    reza · 2 years ago
    hi
    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.
    reza · 2 years ago
    hi
    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.
    VRRNOE · 3 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.
    Stewart · 3 years ago
    Hey! 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.
    Claire · 4 years ago
    I 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.
    Anders · 5 years ago
    Thank 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.
    Visitor · 5 years ago
    Is this formula complete?

    =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"),)&"",
    • To post as a guest, your comment is unpublished.
      gg · 2 years ago
      what a waste of time..... formula does NOT work
    • To post as a guest, your comment is unpublished.
      RaakaReiska · 5 years ago
      This is not yet fixed :sad: