Free Microsoft Excel 2013 Quick Reference

Data validation for unique values Results

I did a custom data validation for unique values.

This is my formula used:


However, it does not work.

Please advise.Thanks.

Dear viewers
i want to validate the list of unique values from the data.

in the enclosed file i validate the list on B4 but the values are repeating.

thanking you for any support

In MS Excel 2010 How do I display only the unique values from a column in a drop-down list box in another cell and have it update the drop down list whenever the source list changes?
For example, if column A contains "chocolate, vanilla, chocolate, strawberry, vanilla, chocolate, strawberry, pecan", I want to have a drop down box in another cell which contains only the values "chocolate, vanilla, strawberry, pecan". I also need this to update automatically, so if I add a new entry to the source list (raspberry) then as soon as it (or any other unique value) is added it would appear on the drop down list.
I created the "unique records only" list from the master list using the Filter menu "Advanced" and then "Copy to" option. I then created the "Drop Down List" using data validation restricting the values to the range with the unique values in it. But the unique values list does not update automatically unless I do the filter again, and then I have to re-assign the drop down list to read the unique list again.

Is it possible to use Data Validation ->List -- to only include the unique Values, thus not include a big list of duplicates?

If so, Can anyone suggest the best method for this!

Any advice would be appreciated,

Thanks in advance,

Hi everyone.
I have an overview sheet in a workbook, that compares data. In column A I
have a unique order reference number and in column B are the corresponding
volumes ordered. Column C is again for the order reference number and column
D is the actual invoiced volume. I have a Gain/loss calculation in Column G
which basically just does a sum of the preceding volumes.
Now I have another 2 columns with additional data in I and J. These are
volumes that have been booked with incorrect order references (so ref number
in column I and the volume in G).
Currently my VBA code adds a data validation to all rows where the gain/loss
is not 0 in column E. The user can then choose from the drop-down list and
the volume gets updated automatically by a vlookup. This data validation
picks the values from the list in columns I and J.
Now I wanted to make this whole procedure a bit more elegant. Instead of
using the data validation I would like to create a user-form which pops up
whenever the user double-clicks in one cell in column E or F. This user-form
then takes it's values from the list that is currently in columns I and J.
When the user makes his choice, the actual choice will be moved from the
list over to the cell that he double-clicked (so the actual choice of ref
and volume will be cut from the list and pasted to the cell he
Now my question here is: I only want the userform to appear when the user
actually clicks in an appropriate cell (in columns E or F) and not when he
double-clicks anywhere else in the sheet. What would be the best way to do
I thought that I might do a check to see if the intersection between the
double-clicked cell and range E:F is true. If yes show the userform and if
not display a msgbox. But I'm not sure if this is the best way to go. Is
there any easier way to define a range where the double-click actually is
Thanks very much in advance for any comments or tips.


In MS Excel 2010 How do I display only the unique values from a column in a drop-down list box in another cell and have it update the drop down list whenever the source list changes?
For example, if column A contains "chocolate, vanilla, chocolate, strawberry, vanilla, chocolate, strawberry, pecan", I want to have a drop down box in another cell which contains only the values "chocolate, vanilla, strawberry, pecan". I also need this to update automatically, so if I add a new entry to the source list (raspberry) then as soon as it (or any other unique value) is added it would appear on the drop down list.
I created the "unique records only" list from the master list using the Filter menu "Advanced" and then "Copy to" option. I then created the "Drop Down List" using data validation restricting the values to the range with the unique values in it. But the unique values list does not update automatically unless I do the filter again, and then I have to re-assign the drop down list to read the unique list again.

Hi everyone,

I am trying to re engingeer the following code.

This looks at a range, copies all unique values into its own workbook and creates a directory for all of these new files when the Macro is run.

What I am looking for is instead of looking at a range in a specific Column, I need the macro to loop through a validation list and copy the entire sheet as opposed to a given range.

I have attached a test spreadshet

 'This example will create a new folder for you and will create
 'a new workbook with the data of every unique value in this folder.
 'The workbooks will be saved with the Unique value in the new folder.
 'It will also add a worksheet to your workbook named "RDBLogSheet" with
 'hyperlinks to the workbooks so it is easy to open the workbooks.
 'Every time you run the macro it delete this worksheet first so the information is up to date.
 'Note: this example use the function LastRow in the ModReset module
Sub Copy_To_Workbooks() 
     'Note: This macro use the function LastRow
    Dim My_Range As Range 
    Dim FieldNum As Long 
    Dim FileExtStr As String 
    Dim FileFormatNum As Long 
    Dim CalcMode As Long 
    Dim ViewMode As Long 
    Dim ws2 As Worksheet 
    Dim MyPath As String 
    Dim foldername As String 
    Dim Lrow As Long 
    Dim cell As Range 
    Dim CCount As Long 
    Dim WSNew As Worksheet 
    Dim ErrNum As Long 
     'Set filter range on ActiveSheet: A11 is the top left cell of your filter range
     'and the header of the first column, D is the last column in the filter range.
     'You can also add the sheet name to the code like this :
     'Worksheets("Sheet1").Range("A11:D" & LastRow(Worksheets("Sheet1")))
     'No need that the sheet is active then when you run the macro when you use this.
    Set My_Range = Range("A11:D" & LastRow(ActiveSheet)) 
    If ActiveWorkbook.ProtectStructure = True Or _ 
    My_Range.Parent.ProtectContents = True Then 
        MsgBox "Sorry, not working when the workbook or worksheet is protected", _ 
        vbOKOnly, "Copy to new workbook" 
        Exit Sub 
    End If 
     'This example filters on the first column in the range(change the field if needed)
     'In this case the range starts in A so Field:=1 is column A, 2 = column B, ......
    FieldNum = 1 
     'Turn off AutoFilter
    My_Range.Parent.AutoFilterMode = False 
     'Set the file extension/format
    If Val(Application.Version) < 12 Then 
         'You use Excel 97-2003
        FileExtStr = ".xls": FileFormatNum = -4143 
         'You use Excel 2007
        If ActiveWorkbook.FileFormat = 56 Then 
            FileExtStr = ".xls": FileFormatNum = 56 
            FileExtStr = ".xlsx": FileFormatNum = 51 
        End If 
    End If 
     'Change ScreenUpdating, Calculation, EnableEvents, ....
    With Application 
        CalcMode = .Calculation 
        .Calculation = xlCalculationManual 
        .ScreenUpdating = False 
        .EnableEvents = False 
    End With 
    ViewMode = ActiveWindow.View 
    ActiveWindow.View = xlNormalView 
    ActiveSheet.DisplayPageBreaks = False 
     'Delete the sheet RDBLogSheet if it exists
    On Error Resume Next 
    Application.DisplayAlerts = False 
    Application.DisplayAlerts = True 
    On Error Goto 0 
     ' Add worksheet to copy/Paste the unique list
    Set ws2 = Worksheets.Add(After:=Sheets(Sheets.Count)) 
    ws2.Name = "RDBLogSheet" 
     'Fill in the pathfolder where you want the new folder with the files
     'you can use also this "C:UsersRontest"
    MyPath = Application.DefaultFilePath 
     'Add a slash at the end if the user forget it
    If Right(MyPath, 1)  "" Then 
        MyPath = MyPath & "" 
    End If 
     'Create folder for the new files
    foldername = MyPath & Format(Now, "yyyy-mm-dd hh-mm-ss") & "" 
    MkDir foldername 
    With ws2 
         'first we copy the Unique data from the filter field to ws2
        My_Range.Columns(FieldNum).AdvancedFilter _ 
        Action:=xlFilterCopy, _ 
        CopyToRange:=.Range("A3"), Unique:=True 
         'loop through the unique list in ws2 and filter/copy to a new sheet
        Lrow = .Cells(Rows.Count, "A").End(xlUp).Row 
        For Each cell In .Range("A4:A" & Lrow) 
             'Filter the range
            My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & _ 
            Replace(Replace(Replace(cell.Value, "~", "~~"), "*", "~*"), "?", "~?") 
             'Check if there are no more then 8192 areas(limit of areas)
            CCount = 0 
            On Error Resume Next 
            CCount = My_Range.Columns(1).SpecialCells(xlCellTypeVisible) _ 
            On Error Goto 0 
            If CCount = 0 Then 
                MsgBox "There are more than 8192 areas for the value : " & cell.Value _ 
                & vbNewLine & "It is not possible to copy the visible data." _ 
                & vbNewLine & "Tip: Sort your data before you use this macro.", _ 
                vbOKOnly, "Split in worksheets" 
                 'Add new workbook with one sheet
                Set WSNew = Workbooks.Add(xlWBATWorksheet).Worksheets(1) 
                 'Copy/paste the visible data to the new workbook
                With WSNew.Range("A1") 
                     ' Paste:=8 will copy the columnwidth in Excel 2000 and higher
                     ' Remove this line if you use Excel 97
                    .PasteSpecial Paste:=8 
                    .PasteSpecial xlPasteValues 
                    .PasteSpecial xlPasteFormats 
                    Application.CutCopyMode = False 
                End With 
                 'Save the file in the new folder and close it
                On Error Resume Next 
                WSNew.Parent.SaveAs foldername & _ 
                cell.Value & FileExtStr, FileFormatNum 
                If Err.Number > 0 Then 
                    ErrNum = ErrNum + 1 
                    WSNew.Parent.SaveAs foldername & _ 
                    "Error_" & Format(ErrNum, "0000") & FileExtStr, FileFormatNum 
                    .Cells(cell.Row, "B").Formula = "=Hyperlink(""" & foldername & _ 
                    "Error_" & Format(ErrNum, "0000") & FileExtStr & """)" 
                    .Cells(cell.Row, "A").Interior.Color = vbRed 
                    .Cells(cell.Row, "B").Formula = _ 
                    "=Hyperlink(""" & foldername & cell.Value & FileExtStr & """)" 
                End If 
                WSNew.Parent.Close False 
                On Error Goto 0 
            End If 
             'Show all the data in the range
            My_Range.AutoFilter Field:=FieldNum 
        Next cell 
        .Cells(1, "A").Value = "Red cell: can't use the Unique name as file name" 
        .Cells(1, "B").Value = "Created Files (Click on the link to open a file)" 
        .Cells(3, "A").Value = "Unique Values" 
        .Cells(3, "B").Value = "Full Path and File name" 
        .Cells(3, "A").Font.Bold = True 
        .Cells(3, "B").Font.Bold = True 
    End With 
     'Turn off AutoFilter
    My_Range.Parent.AutoFilterMode = False 
    If ErrNum > 0 Then 
        MsgBox "Rename every WorkSheet name that start with ""Error_"" manually" _ 
        & vbNewLine & "There are characters in the name that are not allowed" _ 
        & vbNewLine & "in a sheet name or the worksheet already exist." 
    End If 
     'Restore ScreenUpdating, Calculation, EnableEvents, ....
    ActiveWindow.View = ViewMode 
    With Application 
        .ScreenUpdating = True 
        .EnableEvents = True 
        .Calculation = CalcMode 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Any assistance would be greatly appreciated. Please let me know if I was not clear.


I'm trying to create the following functionality in reference to the following:

LongList ShortList dog chicken dog dog pony donkey pig horse donkey ostrich horse pig ostrich pony chicken chicken pony pony
1. ShortList is a sorted list of the unique elements in LongList. 2. ShortList also serves as data validation list, meaning user can add to LongList by selecting ShortList items from drop-down menu.
3. ShortList is dynamic, in the sense that if user adds new item to LongList, it shows up on ShortList, and is thus available from the drop-down menu.
4. User can add overwrite an item anywhere on LongList with a new item that is not on ShortList, and ShortList will update automatically.

I'm having difficulty with #4, which seems a kind of Catch-22 situation. Because LongList is using the Data Validation feature (to get the dropdown menu), it seems it's not possible for the user to be able to add an item to the middle of the list that is not on ShortList, i.e., the data validation list.

I understand that I could require the user to add only to the bottom of LongList (thus allowing the range of data validation to grow dynamically), but that's not what I want.

I've looked over the various range.validation properties hoping to be able to turn something off to allow user to enter a value in the middle of ShortList, but nothing jumps out at me.

Any ideas?

I'm trying to find a way of having a data validation list (to us in a drop-down) that will be generated from a list of data on another sheet tab. The list of data can contain duplicate entries but I want the drop-down to show only the unique values. Also, the drop-down must be dynamic because changes can be made to the source list.

Col A

The drop-down list should give me only one instance each of Apple, Pear, Lemon and Orange to choose from. I'll use a defined name for the validation list so that I can use it on the sheet where I need it.

Can anyone help?



I assume my question/problem can be solved with VBA only - therefor I hereby ask you for assistance:

In the attached picture (although taken form an Hebrew "Excel") you will notice that I limited the Blue area in Col. "A" to accept Unique values only.

Everything works fine UNTIL I copy Cell A6 to A7.
In such case, "Excel" DOES NOT alerting of a 'Not allowed value'.

Is there any way to eliminate the possibility of allowing COPYING a unique value !?

I have noticed that the copied value from A6 to A7 DOES NOT change the DATA VALIDATION Formula in cell A7.

Thanks, Michael

hi buddies,
Is there any way to sort and get unique values from data validation list.

Thanks in advance for reply.

I have a spreadsheet that contains a value in cell B1 that will determine what data will be populated in a table below starting in cell A5. The data in column A appear multiple times on another sheet in the same workbook. Is there a macro I can write/run that will give me unique values in column A based on the value in cell B1? FYI - cell B1 is a validation list so when the user makes a selection from that list, the table will change...that is my goal.

Thank you in advance for your help.

Hello - I'm new to posting to the forum because it's been so useful in answering my questions in the past and I haven't needed to post a question. So thanks to all that have unknowingly helped me in the past!

I want to setup a Data Validation rule on a cell which will use a list. I'm familar with doing this and have used it many times before, however my new scenario is unique and I'm having trouble finding a solution.

Say I have a list called "Projects" which covers 3 cells (in a column, if that matters) that contain "Project X", "Project Y", and "Project Z".

I set up a Data Validation list on a cell (call it the "Project" cell) so that I can choose from one of these projects in that cell using a drop-down list.

I have a cell next to this one called "Sub-Projects". I'd like to setup a similar Data Validation rule using a list whose list content would change based on the selected value in the "Project" cell. I've set up a similar list where by I may have two columns like this:

Project X | Sub-Project A
Project X | Sub-Project B
Project X | Sub-Project C
Project Y | Sub-Project D
Project Y | Sub-Project E
Project Y | Sub-Project F
Project Y | Sub-Project G
Project Z | Sub-Project H

I want the "Sub-Project" cell to effectively look up the value used in the "Project" cell and use the list of sub-projects as the source for the Data Validation rule.

For example, if I select "Project X" in the "Project" cell, I'd want the drop-down list in the "Sub-Project" cell to only contain the values "Sub-Project A", "Sub-Project B", and "Sub-Project C".

I add to both the "Projects" and "Sub-Projects" list frequently, so I want a dynamic approach to specifying the source list for the "Sub-Project" data validation rule that doesn't require me to update it each time I add a new Project or Sub-Project.

Any ideas?

Thanks again for all the help, past, present, and future!

I found this bit of code that the user came up with to run through a data validation list and print each unique page. i'd like to take this code and instead of print, copy and paste values+formats to a new workbook.

can someone to direct me on how?

Originally Posted by FinancialAnalystKid Sorry everyone...

I just figured it out.

Sub PrintSheet()
    Dim Cell As Range
    For Each Cell In Range("PlanList")
        X = Cell.Value
        Range("C9") = X
        ActiveWindow.SelectedSheets.PrintOut copies:=1
    Next Cell
End Sub

A horizontal filter macro was created to sort data horizontally in Excel. Although this macro works to small data set, it does not work properly and at times crashes with large data set. Any input would be appreciated. Thank you.

Link to the program:


Option Explicit

Private c As Range
Private rLastCell As Range
Private rHFilterRow As Range
Private i As Long
Private strFilter As String
Private bFilter As Boolean
Private lCalc As Long

Sub SetrHFilterRange()

On Error Resume Next

Application.ScreenUpdating = False

' Get the Last Cell of the Used Range
Set rLastCell = ThisWorkbook.Sheets(1).UsedRange.SpecialCells(xlCellTypeLastCell)

' Reset Range "rHFilter" from Cell C2 to last cell in Used Range
ThisWorkbook.Names.Add Name:="rHFilter", RefersTo:= _
"=Sheet1!$C$2:" & rLastCell.Address

For Each rHFilterRow In Range("rHFilter").Rows

With rHFilterRow

With Cells(.Row, 2)
.Value = "-"
.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="=""-"""
.FormatConditions(1).Interior.ColorIndex = 44
.Interior.ColorIndex = 22
End With

strFilter = "-"

i = 3

' Get the unique values in each row of rHFilter
' Then make a list with Data Validation
For Each c In .Cells

If Application.CountIf(Range(Cells(.Row, 3), _
Cells(.Row, i)), c.Value) = 1 Then

strFilter = strFilter & "," & c.Value

End If

i = i + 1

Next c

With Cells(.Row, 2).Validation
.Add Type:=xlValidateList, Formula1:=strFilter & ",Blank Cells"
.InCellDropdown = True
End With

strFilter = ""

End With

Next rHFilterRow

For i = 1 To 4

Range(Cells(2, 1), rLastCell).Borders(i).LineStyle = xlContinuous

Next i

Application.ScreenUpdating = True

On Error GoTo 0
End Sub

Sub SetrHFilter()

On Error Resume Next

ThisWorkbook.Sheets(1).Columns.Hidden = False

If Application.CountIf(ThisWorkbook.Sheets(1).Columns(2), "-") _
= Range("rHFilter").Rows.Count Then Exit Sub

If rLastCell Is Nothing Then

Set rLastCell = ThisWorkbook.Sheets(1).UsedRange.SpecialCells(xlCellTypeLastCell)

End If

' Speed the code up changing the Application settings

With Application
lCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With


' Hide columns if cells don't match the values in Column B

For Each rHFilterRow In Range("rHFilter").Rows

With rHFilterRow

If Cells(.Row, 2) "-" Then

For Each c In Range(Cells(.Row, 3), Cells(.Row, rLastCell.Column))

If Cells(.Row, 2).Value = "Blank Cells" Then

If c.Value "" Then c.EntireColumn.Hidden = True


If c.Value Cells(.Row, 2).Value Then c.EntireColumn.Hidden = True

End If

Next c

End If

End With

Next rHFilterRow

If bFilter = False Then
bFilter = True
GoTo FilterRows
End If

' Change the Application settings back

With Application
.Calculation = lCalc
.EnableEvents = True
.ScreenUpdating = True
End With

Set rLastCell = Nothing

On Error GoTo 0
End Sub

Sub ResetrHFilter()

On Error Resume Next

ThisWorkbook.Sheets(1).Columns.Hidden = False


On Error GoTo 0
End Sub

Sorry if the title is ambiguous, but here is a summary of what I'm doing:

1. I have a named range 'MSAs' which containes the names of about 30 sheets, each of which contain the same table structure for each 'MSAs' but data relevant to each MSA
2. My output sheet (deliverable) has a dropdown using the named range MSAs in cell B9. The user will first select the proper MSAs value which will feeds my dropdowns below (using indirect and the common ranges shared among the tables).
3. The table contains several variables with one to many relationships:
a) Data Source (C4:N4)
b) Data Type, (B5:B326)
c) Data Date (C3:N3)
d) Subcategory (A5:A326)
e) Data (C5:N326)I am trying to use dependent dropdowns which when selected will feed my multivariate vlookups which populate the data we're looking for.

I've used the indirect function on B9 to select the range for my dropdowns for a, c, and d [b is relative based on the position of the formula on the output page]- but they are not currently functioning as dependent. Is this is situation where I would need VBA code? Any guidance to how a non-programmer would approach this?

The variable sheets make it challenging to set up named ranges, so I am hoping there is a cleaner way to do this while showing only uniques.

Happy to provide any additional information necessary. Thanks for any direction/advice!

I'm not sure if a similar thread has been posted about this already but I badly need all your help on my issue below.

First, I only know a little about macro - so please bear with me.

How can I automate/ create the macro to:
- Display the duplicate and unique values for each worksheets and pull out each of their data.

Please find attached for my scenario and expected results.
Please also note of the following:
1. The main column to be validated for duplicate and unique to both sheets would be the "Code" column (highlighted in yellow)
2. Records in red font in each sheets are records that appear twice.

Thanks a lot for your help in advance.

I have many columns, one of which is DIVISION with about 500 values
(rows.) The data looks like this...

40 - 49
20 - 29
40 - 49
20 - 29
40 - 49
50 +
50 +

I need a cell with a drop down menu where the only items displayed are
unique values from DIVISION. Currently Data-Validation-List sort of
works but it will list all 500 values. I only want the unique values.

Thanx for all who help.



Ultimately, what I am trying to accomplish is to give the user the
ability to select any value from the DIVISION column and have a small
table populated from matching rows. Autofilter does this but it does
it with the entire sheet. I want to have more control.


I have a problem with data validation:

I have a list that is called in the data validation. However I want to change the value in the list (i.e. rename an entry), but the cell that references this list does not update. How can this be achieved?

Also, is there away to have a non-unique list entry? For example, where the list is a catalogue of room names, two cells in the list are called 'office'.Excel seems to just find the first one it comes upon. Can I get it to reference the one I picked from the list?

Thanks very much for any help

Can someone help me ?

I have 2 sheet in my excel.
Sheet 1 contains "master data"
Sheet 2 contains "transaction data"

In "master data" sheet I have a list contains 2 columns
Column 1 - Unique key data (sorted in ascending)
Column 2 - Date
101K 2009-01-01
102K 2009-03-01
103K 2009-04-01
104K 2009-07-01
105K 2009-08-01
106K 2009-09-01
107K 2009-09-10

In "transaction data" sheet, I have to do data entry depend on the key value available in "master data" sheet. I am using VLOOKUP function in Data Validation->Custom.

For instance,
Column "C" contains Keys from "master data", Column "A" contains Date, Column "D" is used for data entry.

1 2009-04-01 104K (data entry - 0 or 1)

Now, the idea is using key from "transation data" sheet get the date from "master data" sheet, compare the date present in A1, if Ok, I can do the data entry in current row with possible values 0 or 1.

For instance, If the key is "104K", then get the date "2009-7-01" from "master data" sheet and compare the date with A1, if matches allow to
enter the value 0 or 1 in D1.

For this, I used - Data -> Validation -> Custom and the formula is
=AND(D1=0, D1=1, A1=VLOOKUP(C1,Masterdata,2)). This is not working and
it is allowing me to enter any values. Can someone help me. Any other solutions or suggestions is well appreciated.


No luck finding an answer? You could always try Google.