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.

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

In this part, we will provide four different ways to get unique values in Excel from multiple columns. 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.

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

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.
Next, select OK.

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.

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.

Now, you will see that you have got a 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.

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.

Here, the Visual Basic window will open.

Firstly, go to the Insert tab.
Secondly, select Module.

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

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.

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.

Now, the Visual Basic window will open.

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

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.

After that, delete the first Private Sub and write the following code.

Private Sub UserForm_Initialize()
Me.ListBox1.AddItem "Name"
End Sub

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.

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

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.

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.

Download Practice Workbook

Creating a List of Unique Values.xlsm

Conclusion

In this article, I tried to explain how to create a 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 to you. Lastly, if you have any questions let me know in the comment section below.

<< Go Back to Unique Values | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Lodewijk Dec 14, 2022 at 7:29 PM

Could you give a bit more information on the Add Sheets method to get the unique values from the cells in Excel sheet please.
- Reply
  Arin Dec 18, 2022 at 11:24 AM
  
  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.

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

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

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

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

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

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

Things to Remember

Conclusion

Leave a reply Cancel reply