How to Create a List of Unique Values from Multiple Worksheets in Excel

You can frequently find yourself in a position where you need to combine and analyse data from other worksheets if you work with large Excel workbooks with numerous pages. One frequent task is to remove any duplicate values from numerous sheets and generate a list of the unique values. This article will give you step-by-step instructions and helpful hints to help you combine data and extract special values from various worksheets, regardless of your level of familiarity with Excel. By the conclusion, you will have the skills and resources necessary to complete this activity successfully, saving you time and effort while completing your data analysis tasks. So let's get started and unleash Excel's potential to generate a thorough list of distinct values from many worksheets!

Create a List of Unique Values from Multiple Worksheets

Here we will first create a VAB module and then run it to complete the task. So let us see a simple process to learn how you can create a list of unique values from multiple worksheets in Excel.

step 1

Consider any Excel workbook where you have data in column A on multiple sheets.

First, right-click on the sheet name and select View Code to open the VBA application.

Step 2

Then click on Insert and select Module, then copy the below code into the text box.

Insert > Module > Copy.

Example

Sub SheelsUniqueValues()
Dim xObjNewWS As Worksheet
Dim xObjWS As Worksheet
Dim xStrAddress As String
Dim xIntRox As Long
Dim xIntN As Long
Dim xFNum As Integer
Dim xMaxC, xColumn As Integer
Dim xR As Range
xStrName = "Unique value"
Application.ScreenUpdating = False
xMaxC = 0
Application.DisplayAlerts = False
For Each xObjWS In Sheets
   If xObjWS.Name = xStrName Then
      xObjWS.Delete
      Exit For
   End If
Next
Application.DisplayAlerts = True
For xFNum = 1 To Sheets.Count
   xColumn = Sheets(xFNum).Cells.Find(What:="*", after:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
   If xMaxC < xColumn Then
      xMaxC = xColumn
   End If
Next xFNum
Application.DisplayAlerts = True
Set xObjNewWS = Sheets.Add(after:=Sheets(Sheets.Count))
xObjNewWS.Name = xStrName
For xColumn = 1 To xMaxC
   xIntN = 1
   For xFNum = 1 To Sheets.Count - 1
      Set xR = Sheets(xFNum).Columns(xColumn)
      If TypeName(Sheets(xFNum).Columns(xColumn).Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)) <> "Nothing" Then
         xIntRox = xR.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
         Sheets(xFNum).Range(Cells(1, xColumn).Address & ":" & Cells(xIntRox, xColumn).Address).Copy
         Cells(xIntN, xColumn).PasteSpecial xlValues
         xIntN = xIntRox + xIntN + 1
      End If
   Next xFNum
   If xIntRox - 1 > 0 Then
   xIntRox = xIntN - 1
   xStrAddress = Cells(1, xColumn).Address & ":" & Cells(xIntRox, xColumn).Address
   Range(xStrAddress).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
   Range(xStrAddress).Copy
   Cells(1, xColumn + 1).PasteSpecial xlValues
   Range(xStrAddress).AdvancedFilter Action:=xlFilterInPlace, Unique:=False
   Columns(xColumn).Delete
   Range(xStrAddress).Sort key1:=Cells(1, xColumn), Header:=xlNo
   End If
Next xColumn
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
    

step 3

Then click F5 to complete the task. Then you can see that all the unique values in column A will be listed.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can create a list of unique values from multiple worksheets in Excel to highlight a particular set of data.