How to Create List of Unique Values from Multiple Sheets in Excel

To demonstrate the different methods to obtain unique values from columns in multiple sheets, we will use 3 Excel sheets as a dataset. Each of them contains the Top Sales Person list for a month. The first sheet contains the Top Sales Person in April list.

Excel Create List of Unique Values from Multiple Sheets

The second sheet contains the Top Sales Person in May list.

And the third sheet contains the Top Sales Person in June list.

Dataset to Create Unique Values from Multiple Sheets in Excel

These sheets have repeating values in them. Let’s create a list of unique values from these 3 sheets.


Method 1 – Creating a List of Unique Values from Multiple Sheets Manually by Using Sort & Filter Group

Steps:

  • Select the data from the first sheet.
  • Press CTRL+C to copy the selected values.

Creating List of Unique Values from Multiple Sheets Manually by Using Sort & Filter Group

  • Go to the sheet where you want to create the unique value list.
  • Select the cell from where you want the list to begin. Here, cell B5.
  • Press CTRL+V to paste the values you copied.

The values from the first sheet are returned.

Pasting Values from Mutiple Sheets to Create List of Unique Values in Excel

  • In the same way, copy the values from the other sheets too.

We now have all the values from the 3 sheets in column B.

We can now create a list of unique values from this list.

  • Go to the Data tab.
  • Select Advanced.

Selecting Advanced Filter to Create List of Unique Values in Excel

A dialog box names Advanced Filter will open.

  • Select Filter the list, in-place.
  • Select the List range.
  • Check the Unique records only option.
  • Click OK.

The list of unique values from multiple sheets is returned.


Method 2 – Using Power Query to Create List of Unique Values from Multiple Sheets in Excel

Steps:

  • Go to the Data tab.
  • Select Get Data.

Use of Power Query to Create List of Unique Values from Multiple Sheets in Excel

A drop-down menu appears.

  • Select From File.
  • Select From Excel Workbook.

A file dialog box will open.

  • Select the file from where you want to import the data.
  • Select Import.

Opening Excel Workbook to Create List of Unique Values from Multiple Sheets

The Navigator will open.

  • Check Select multiple items.
  • Select the sheets from which you want to create your list of unique values.
  • Select Load.

The Power Query Editor opens and our sheets are inserted as tables. As in the following picture, we don’t need the first two rows of the table.

Tables from Multiple Sheets to Create List of Unique Values in Excel

To delete the first two rows from the table:

  • Select Reduce Rows.
  • Select Remove Rows.
  • Select Remove Top Rows.

A dialog box will appear.

  • Select the number of rows you want to remove. Here 2, as we want to remove the first two rows.
  • Click OK.

Deleting Rows from Table to Create List of Unique Values from Multiple Sheets

The first 2 rows are removed from the table.

  • Remove the first two rows from the second table by following the previous steps.

  • Remove the first two rows from the third table by following the previous steps.

  • Right-click anywhere in the Queries section.
  • Select New Query.
  • Select Combine.
  • Select Append Queries as New.

Combining Table from Multiple Sheets to Create List of Unique Values in Excel

A dialog box named Append will appear.

  • Select Three or more tables.
  • Select the table you want to add.
  • Select Add.

The selected table is added to Tables to append.

  • In the same way, add the other tables.
  • Click OK.

Adding Tables from Multiple Sheets to Create List of Unique Values in Excel

The values from the 3 tables are combined into one.

We only need the first column, so we remove the other columns:

  • Select the column.
  • Right-click on the column.
  • Select Remove Other Columns.

Removing Columns From Table to Create List of Unique Values in Excel

The other columns are removed from the table.

Now we can remove the duplicate values to get the list of unique values.

  • Select the column.
  • Right-click on the column.
  • Select Remove Duplicates.

Removing Duplicates from Table to Create LIst of Unique Values from Multiple Sheets in Excel

What remains is a list of unique values.

For clarity, I have changed the header of my table.

  • Select Close & Load.

The list of unique values looks like this:


Method 3 – Using a User-Defined Function to Create List of Unique Values from Multiple Sheets

Steps:

First we need to create a user-defined function using VBA code.

  • Go to the Developer tab.
  • Select Visual Basic.

Employing User-Defined Function to Create List of Unique Values from Multiple Sheets in Excel

The Visual Basic window will open.

  • Go to the Insert tab.
  • Select Module.

Inserting Module to Write a VBA Code to Create List of Unique Values from Multiple Sheets in Excel

A Module window will open.

  • In that Module enter the following code.
Function split_function(split_text As String, Optional delimiter As String = ",") As Variant
split_function = Split(split_text, delimiter)    
End Function

Code Breakdown

  • We create a Function named split_function as Variant.
  • We declare split_text as String and an Optional argument delimiter as string, setting it as “,”.
  • We use the Split function with split_text as expression and delimiter. The function will return a one-dimensional array.
  • We end the Function.
  • Save the code and go back to your worksheet.
  • Select the cell where you want your list of unique values to begin. Here, cell B5.
  • In cell B5 enter the following formula:
=UNIQUE(TRANSPOSE(split_function(TEXTJOIN(",",,'Top Sales Person(April):Top Sales Person(June)'!B6:B10))))

User-defined Function in Formula to Create List of Unique Values from Multiple Sheets in Excel

Formula Breakdown

  • TEXTJOIN(“,”,,’Top Sales Person(April):Top Sales Person(June)’!B6:B10) —-> the TEXTJOIN function will join the texts in range B6:B10 from sheet range ‘Top Sales Person(April):Top Sales Person(June)’! with a delimiter “,”.
    • Output: “Glenn,Toni,Mark,Bruce,Lucy,Mark,Geralt,Toni,Kim,Glenn,Anne,Geralt,Lucy,Mark,Michael”  
  • split_function(TEXTJOIN(“,”,,’Top Sales Person(April):Top Sales Person(June)’!B6:B10)) —-> turns into
    • split_function(“Glenn,Toni,Mark,Bruce,Lucy,Mark,Geralt,Toni,Kim,Glenn,Anne,Geralt,Lucy,Mark,Michael” ) —-> the split_function will return a one-dimensional array containing specific numbers or substring after each delimiter.
      • Output: {“Glenn”,”Toni”,”Mark”,”Bruce”,”Lucy”,”Mark”,”Geralt”,”Toni”,”Kim”,”Glenn”,”Anne”,”Geralt”,”Lucy”,”Mark”,”Michael”}
  • TRANSPOSE(split_function(TEXTJOIN(“,”,,’Top Sales Person(April):Top Sales Person(June)’!B6:B10))) —-> turns into
    • TRANSPOSE({“Glenn”,”Toni”,”Mark”,”Bruce”,”Lucy”,”Mark”,”Geralt”,”Toni”,”Kim”,”Glenn”,”Anne”,”Geralt”,”Lucy”,”Mark”,”Michael”}) —-> the TRANSPOSE function will convert the the horizontal array to a vertical array.
      • Output: {“Glenn”;”Toni”;”Mark”;”Bruce”;”Lucy”;”Mark”;”Geralt”;”Toni”;”Kim”;”Glenn”;”Anne”;”Geralt”;”Lucy”;”Mark”;”Michael”}
  • UNIQUE(TRANSPOSE(split_function(TEXTJOIN(“,”,,’Top Sales Person(April):Top Sales Person(June)’!B6:B10)))) —-> turns into
    • UNIQUE({“Glenn”;”Toni”;”Mark”;”Bruce”;”Lucy”;”Mark”;”Geralt”;”Toni”;”Kim”;”Glenn”;”Anne”;”Geralt”;”Lucy”;”Mark”;”Michael”}) —-> the UNIQUE function will return a list of distinct values.
      • Output: {“Glenn”;”Toni”;”Mark”;”Bruce”;”Lucy”;”Geralt”;”Kim”;”Anne”;”Michael”} 
  • Press ENTER to return our unique values list.

Here is the final list of unique values from multiple sheets in Excel.


Method 4 – Using VBA to Create a List of Unique Values from Multiple Sheets in Excel

Steps:

  • Go to the Developer tab.
  • Select Visual Basic.

Applying VBA to Create a List of Unique Values from Multiple Sheets in Excel

The Visual Basic window will open.

  • Select the drop-down option from UserForm.
  • Select UserForm.

Adding UserForm to Create List of Unique Values from Multiple Sheets in Excel

  • The UserForm will appear.

  • Click on the Label from Toolbox.

  • Click and drag your mouse cursor where you want the label of your UserForm.

The Label is created.

  • Right-click on the Label.
  • Select Properties.

  • Change the Caption if you want. Here, I changed mine to Unique List.

  • Select CommandButton from the toolbox.

  • Click and drag the mouse cursor where you want the CommandButton.

The CommandButton is inserted in the UserForm.

  • Right-click on the CommandButton.
  • Select Properties.

  • Change the properties if you want. Here, I changed my caption to Get Data.

  • Select ListBox.

  • Click and drag your mouse cursor where you want the ListBox.

The list box is inserted into the UserForm.

  • Double-click anywhere in the marked part.

A Module will open with a Private Sub Procedure named UserForm_Click().

  • Select the marked drop-down option.
  • Select Initialize.

Another Private Sub Procedure named UserForm_Initilizer() will be created.

Private Sub in VBA to Create List of Unique Values from Multiple Sheets in Excel

  • Delete the first Private Sub and enter the following code:
Private Sub UserForm_Initialize()
Me.ListBox1.AddItem "Name"
End Sub

VBA Code to Add Item on a ListBox to Create List of Unique Values from Multiple Sheets in Excel

Code Breakdown

  • We create a Private Sub procedure named UserFprm_Initializer().
  • We use the Me object, which acts as an implicitly declared variable.
  • We use the AddItem method to make a list of values displayed by ListBox1.
  • We end the Sub Procedure.
  • Double-click on the CommandButton on the UserForm.

Another Private Sub Procedure named CommmandButton1_Click() will be created.

  • Enter the following code in the UserForm1 box:
Private Sub CommandButton1_Click()
Dim unq_val As New Collection
Dim a As Integer
Dim i As Long
For a = 2 To 4
For i = 6 To Sheets(a).Range("B" & Rows.Count).End(xlUp).Row
On Error Resume Next
unq_val.Add Sheets(a).Cells(i, "B"), Sheets(a).Cells(i, "B")
Next i
Next a
For Each itm In unq_val
Me.ListBox1.AddItem itm
Next itm
End Sub
Private Sub UserForm_Initialize()
Me.ListBox1.AddItem "Name"
End Sub

Code Breakdown

  • We create a Private Sub Procedure named CommandButton1_Click().
  • We declare a variable unq_val as New Collection.
  • We declare another variable a as Integer.
  • We declare i as long.
  • We use a For Next loop to take values from multiple worksheets.
  • In the For Next Loop, we nest another For Next loop to get values from different cells.
  • We use the Add Sheets method to get the unique values from the cells in Excel sheet.
  • We use the For Each Next loop to add the unique values in the ListBox.
  • We end the Sub Procedure.
  • Save the code and go back to the worksheet.
  • Go to the Developer tab.
  • Select Insert.
  • Select CommandButton from ActiveX Controls.

Adding Command Button to Show UserForm to Create List of Unique Values from Multiple Sheets in Excel

  • Click and drag your mouse cursor where you want the CommandButton.

  • Right-click on the CommandButton.
  • Select Properties.

  • Change the Properties if you like. I changed my caption to Show UserForm.

The caption of the CommandButton is changed.

A Module with a Private Sub will appear.

  • In that Module enter the following code:
Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

VBA Code to Show the UserForm to Create List of Unique Values from Multiple Sheets in Excel

Code Breakdown

  • We create a Private Sub Procedure named CommandButton1_Click().
  • We use the Show method to show the UserForm1.
  • We end the Sub Procedure.
  • Save the code and go back to your worksheet.
  • Click on Show UserForm.

The UserForm will appear.

  • Click on the CommandButton.

The list of unique values from multiple sheets in Excel is displayed.


Things to Remember

  • Whenever working with Visual Basic, the workbook should be saved as Excel Macro-Enabled Workbook.

Download Practice Workbook


<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mashhura Jahan
Mashhura Jahan

Mashhura Jahan, BSc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology, invested 1.5 years at ExcelDemy. In roles like Excel & VBA Content Developer, Excel Charts, and Dashboard course trainer, she wrote 90+ articles. She was previously part of the forum support team and a junior software analyst on the Excel Add-in project. With interests spanning Excel, VBA, Power Query, Python, Data Science, and Software Development, Mashhura brings a diverse skill set to her... Read Full Bio

2 Comments
  1. Could you give a bit more information on the Add Sheets method to get the unique values from the cells in Excel sheet please.

    • Hello Lodewijk,
      I suppose you want to know more about the Power Query Editor and how it can add sheets to get unique values.
      Power Query Editor is very useful in the case of data preparation.
      To use this editor, you have to use the Get Data feature from the Data tab. You can get data from different kinds of files such as Excel workbooks, PDFs, Text, etc. Then, you can transform those datasets using the operator available. Here, we removed rows from the tables. You can also combine different tables from those files by the Append or Merge operator. The Append operator is used to create a new query having all the rows from the datasets and the Merge operator is used to have a query with all the columns. You have to use the Append operator to get the unique values. Finally, you can load the query in the existing worksheet or a new worksheet.
      If you face any further problems, please share your Excel file with us in the comment section.
      Regards
      Arin Islam,
      Exceldemy.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo