How to create a list of unique values across multiple worksheets in Excel?
When working with data spread across multiple worksheets in Excel, you might need to consolidate the information and generate a list of unique values. While Excel doesn’t provide a direct built-in feature for this task, there are several methods to achieve it. This article will walk you through three practical approaches to create a list of unique values from multiple worksheets.
Create a list of unique values from multiple worksheets
Create a list of unique values from multiple worksheets with VBA code
To list all unique values from all worksheets, the following VBA code may do you a favor, please do as this:
1. Hold down the "ALT + F11" keys to open the "Microsoft Visual Basic for Applications" window.
2. Click "Insert" > "Module", and paste the following macro in the Module Window.
VBA code: Create a list of unique values from multiple worksheets:
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
3. After pasting above code, then press F5 to run this code, and a new worksheet named Unique value is created and unique names in column A from all sheets are listed as following screenshot shown:
Create a list of unique values from multiple worksheets with Kutools AI Aide
In this section, we’ll show you how to effortlessly create a list of unique values from multiple worksheets using Kutools AI Aide.
Click "Kutools" > "AI Aide" to open the "Kutools AI Aide" pane, in the pane, please do the following operations:
- Type the requirement in the chat box: such as:
Extract all unique values in Column A from all worksheets of this workbook and list the unique values in a new sheet - And then, press Enter key or click the "Send" button. Kutools AI will analyze the question, after finishing, please click "Execute" button, all unique values from multiple sheets are displayed in a new sheet, see the demo:
Create a list of unique values from multiple worksheets with formula (Excel 365)
If you want to create a list of unique values from multiple worksheets in Excel using formula, you can achieve this by combining dynamic array functions such as UNIQUE and VSTACK in Excel 365.
Please enter the following formula into a blank cell, and then press Enter key to extract unique values into a single column from multiple sheets. See screenshot:
=UNIQUE(VSTACK(Sheet1!A1:A11, Sheet2!A1:A11, Sheet3!A1:A11, Sheet4!A1:A11))
- VSTACK: Combines multiple ranges (e.g., Sheet1!A1:A11, Sheet2!A1:A11, Sheet3!A1:A11, Sheet3!A1:A11) into a single vertical array.
- UNIQUE: Filters out duplicates and returns only unique values from the combined list.
- A1:A11: Adjust the range to include your actual data range.
In this article, we explored three effective methods to create a list of unique values from multiple worksheets in Excel. Each method has its advantages, allowing you to choose the one that best fits your Excel version, skill level, and specific needs. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in