Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

Whenever working with Excel, you may need to work with data from multiple sheets. And there can be a repetition of values in those sheets. If you want to create a list of unique values from multiple sheets, then this article will be helpful for you. The main focus of this article is to explain how to create list of unique values from multiple sheets from Excel.


Download Practice Workbook


4 Suitable Ways to Create a List of Unique Values from Multiple Sheets in Excel

Here, I have taken 3 Excel sheets as my dataset. Each of them contains the Top Sales Person list for a month. In the following picture, you can see the first sheet which 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. I will create a list of unique values in Excel from these 3 sheets.


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

In this method, I will explain how you can manually create a list of unique values from multiple sheets in Excel by using the Sort & Filter Group. Let’s see the steps.

Steps:

  • Firstly, select the data from the first sheet.
  • Secondly, press CTRL+C on your keyboard to copy the selected values.

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

  • Thirdly, go to the sheet where you want to create the unique value list.
  • After that, select the cell from where you want the list to begin. Here, I selected cell B5.
  • Next, press CTRL+V on your keyboard to paste the values you copied.

Here, you will get your values from the first sheet.

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

  • Now, in the same way, copy the values from the other sheets also.

In the following picture, you can see that I have copied all the values from the 3 sheets.

Now, I will create a list of unique values from these values.

  • Firstly, go to the Data tab.
  • Secondly, select Advanced.

Selecting Advanced Filter to Create List of Unique Values in Excel

Now, a dialog box for Advanced Filter will open.

  • Firstly, select Filter the list, in-place.
  • Secondly, select the List range.
  • Thirdly, check the Unique records only.
  • Finally, select OK.

Here, you will get your list of unique values from multiple sheets in Excel.

Read More: How to Find Unique Values from Multiple Columns in Excel


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

In this method, I will explain how to use Power Query to create a list of unique values from multiple sheets in Excel. Let’s see the steps.

Steps:

  • Firstly, go to the Data tab.
  • Secondly, select Get Data.

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

Here, you will see a drop-down menu.

  • Firstly, select From File.
  • Secondly, select From Excel Workbook.

Now, a file dialog box will open.

  • Firstly, select the file from where you want to import the data.
  • Secondly, select Import.

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

Now, the Navigator will open.

  • Firstly, check Select multiple items.
  • Secondly, select the sheets from which you want to create your list of unique values.
  • Thirdly, select Load.

Here, you will see the Power Query Editor has opened and your sheets are inserted as tables. In the following picture, you can see that I won’t need the first two rows of the table.

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

Now, I will delete the first two rows from the table.

  • Firstly, select Reduce Rows.
  • Secondly, select Remove Rows.
  • Thirdly, select Remove Top Rows.

Here, you will see a dialog box will appear.

  • Firstly, select the number of rows you want to remove. Here, I selected 2 as I want to remove the first two rows.
  • Secondly, select OK.

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

Now, you will see the first 2 rows are removed from your table.

  • After that, remove the first two rows from the second table by following the previous steps.

  • Then, remove the first two rows from the third table by following the previous steps.

  • Now, Right-click anywhere in the Queries section.
  • Then, select New Query.
  • Next, select Combine.
  • After that, select Append Queries as New.

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

Here, a dialog box for Append will appear.

  • Firstly, select Three or more tables.
  • Secondly, select the table you want to add.
  • Thirdly, select Add.

Now, you can see the selected table is added to Tables to append.

  • After that, in the same way, add other tables also.
  • Next, select OK.

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

Now, you will see the values from 3 tables combined in one.

Here, I will only need the first column. So, I will remove the other columns.

  • Firstly, select the column.
  • Secondly, Right-click on the column.
  • Thirdly, select Remove Other Columns.

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

Now, you will see the other columns are removed from the table.

Here, I will remove the duplicate values to get the list of unique values.

  • Firstly, select the column.
  • Secondly, Right-click on the column.
  • Thirdly, select Remove Duplicates.

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

  • Now, you will see that you have got the list of unique values.

Here, I have changed the header of my table.

  • Finally, select Close & Load.

Now, you will get the list of unique values from multiple sheets in another sheet in Excel.

Read More: How to Get Unique Values from Range in Excel (8 Methods)


3. Employing User-Defined Function to Create List of Unique Values from Multiple Sheets

Here, I will explain how you can create a list of unique values from multiple sheets in Excel by using a user-defined function. Let’s see the steps.

Steps:

To begin with, I will create the user-defined function.

  • Firstly, go to the Developer tab.
  • Secondly, select Visual Basic.

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

Here, the Visual Basic window will open.

  • Firstly, go to the insert tab.
  • Secondly, select Module.

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

Here, a Module will open.

  • Now, in that Module write 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

  • Here, I created a Function named split_function as Variant.
  • Then, in the function, I declared split_text as String and an Optional argument delimiter as string and set it as “,”.
  • After that, I used the Split function in VBA. In the Split function, I used split_text as expression and delimiter. Now, the function will return a one-dimensional array.
  • Finally, I ended the Function.

After that, save the code and go back to your worksheet.

  • Firstly, select the cell from where you want your list of unique values to begin. Here, I selected cell B5.
  • Secondly, in cell B5 write 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) —-> Here, 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 which is “,”.
    • 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” ) —-> Here, the split_function will return a one-dimentional 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”}) —-> Here, the TRANSPOSE function will conert 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”}) —-> Here, the UNIQUE function will return a list of distinct values.
      • Output: {“Glenn”;”Toni”;”Mark”;”Bruce”;”Lucy”;”Geralt”;”Kim”;”Anne”;”Michael”} 
  • Finally, press ENTER to get your unique values list.

In the following picture, you can see the final list of unique values from multiple sheets in Excel.

Read More: How to Use Excel UNIQUE Function (20 Examples)


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

In this method, I will explain how to create a list of unique values from multiple sheets in Excel using VBA. Let’s see the steps.

Steps:

  • Firstly, go to the Developer tab.
  • Secondly, select Visual Basic.

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

Now, the Visual Basic window will open.

  • After that, select the drop-down option from UserForm.
  • Next, select UserForm.

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

  • Now, the UserForm will appear.

  • After that, click on the Label from Toolbox.

  • Next, click and drag your mouse cursor where you want the label of your UserForm.

Here, you will get the Label.

  • Now, Right-click on the Label.
  • Next, select Properties.

  • After that, change the Caption if you want. Here, I changed mine to Unique List.

  • Now, select CommandButton from the toolbox.

  • Next, click and drag the mouse cursor where you want the CommandButton.

Here, you will see the CommandButton is inserted in your UserForm.

  • Now, Right-click on the CommandButton.
  • Then, select Properties.

Now, you can change the properties if you want. Here, I changed my caption to Get Data.

  • After that, select ListBox.

  • Then, click and drag your mouse cursor where you want your ListBox.

Here, you will see the list box is inserted into your UserForm.

  • Now, double-click on anywhere in the marked part.

At this point, you will see a Module will open with a Private Sub Procedure named UserForm_Click().

  • Firstly, select the marked drop-down option.
  • Secondly, select Initialize.

Now, you will see 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

  • After that, delete the first Private Sub and write 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

  • Here, I created a Private Sub procedure named UserFprm_Initializer().
  • Then, I used the Me object. It acts like an implicitly declared variable.
  • Next, I used the AddItem method to make a list of values displayed by ListBox1.
  • Finally, ended the Sub Procedure.
  • Now, double-click on the CommandButton on the UserForm.

Here, another Private Sub Procedure named CommmandButton1_Click() will be created.

  • After that, write the following code in the UserForm1.
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

  • Here, I created a Private Sub Procedure named CommandButton1_Click().
  • Then, I declared a variable unq_val as New Collection.
  • Next, I declared another variable a as Integer.
  • After that, I declared i as long.
  • Then, I used a For Next loop to take values from multiple worksheets.
  • Next, in the For Next Loop, I used another For Next loop to get values from different cells.
  • After that, I used the Add Sheets method to get the unique values from the cells in Excel sheet.
  • Then, I used the For Each Next loop to add the unique values in the ListBox.
  • Finally, I ended the Sub Procedure.

Now, save the code and go back to the worksheet.

  • Firstly, go to the Developer tab.
  • Secondly, select Insert.
  • Thirdly, select CommandButton from ActiveX Controls.

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

  • After that, click and drag your mouse cursor where you want your CommandButton.

  • Now, Right-click on the CommandButton.
  • Then, select Properties.

Here, you can change the Properties. I changed my caption to Show UserForm.

Now, you can see the caption of the CommandButton is changed.

Here, a Module with a Private Sub will appear.

  • After that, in that Module write 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

  • Here, I created a Private Sub Procedure named CommandButton1_Click().
  • Then, I use the Show method to show the UserForm1.
  • Finally, I ended the Sub Procedure.

Now, save the code and go back to your worksheet.

  • After that, click on the Show UserForm.

Here, you will see the UserForm will appear.

  • Now, click on the CommandButton.

Finally, you will get the list of unique values from multiple sheets in Excel.

Read More: Excel VBA to Get Unique Values from Column (4 Examples)


Things to Remember

  • It should be noted that whenever working with Visual Basic the workbook should be saved as Excel Macro-Enabled Workbook.

Practice Section

Here, I have provided a practice sheet for you to practice how to create a list of unique values from multiple sheets in Excel.

Practice Sheet to Create List of Unique Values from Multiple Sheets in Excel


Conclusion

In this article, I tried to explain how to create list of unique values from multiple sheets in Excel. I explained how to do it in 4 different ways. I hope this article was helpful for you. Lastly, if you have any questions let me know in the comment section below.


Related Articles

Mashhura

Mashhura

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

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

ExcelDemy
Logo