Free Microsoft Excel 2013 Quick Reference

Data Validation List - Unique Values from a List

I need to set data validation from a list and I need the list of values to dynamically display the unique values from a list in a range. So if the list had:
111
222
222
333
333
333
444
444

I need the dropdown to propose:
111
222
333
444

I know how to do a dynamic range name, it is the unique value bit that I cannot do. I would prefer a single formula that can be entered in the data validation dialog as opposed to creating multiple ranges and then pointing the list to the last one.

TIA,
Ken


I use this code everyday to extract unique values from a sorted list that contains duplicates. I find it invaluable. hope you do too.


	VB:
	
 extract_unique() 
    Dim myrange As String, invalidcell As String 
    Dim endpoint As Integer, x As Integer 
    Dim firstrun As Boolean 
    invalidcell = "Invalid 'Extract To' cell entered" & vbCrLf & vbCrLf & "Please select a cell between" & vbCrLf & "A1 and
IV" & (65536 - Selection.Count) & "." 
    myrange = InputBox("Enter the cell you would like the top of the" & vbCrLf & "list of values to land in." & vbCrLf &
vbCrLf & "For example 'D1':", "Need some info here...") 
    If myrange  "" Then 
        endpoint = Len(myrange) + 1 
        firstrun = True 
        For x = Len(myrange) To 1 Step -1 
            If Not IsNumeric(Mid(myrange, x, 1)) Then 
                endpoint = x 
                Exit For 
            End If 
        Next x 
        If endpoint < Len(myrange) And endpoint < 3 Then 
            Dim rtrn(2) As Integer 
            For x = 1 To endpoint 
                rtrn(x) = Asc(Mid(UCase(myrange), x, 1)) - 64 
            Next x 
            If endpoint > 1 Then 
                If rtrn(2) > 0 Then 
                    rtrn(1) = rtrn(1) * 26 
                    col = rtrn(1) + rtrn(2) 
                Else 
                    col = rtrn(1) 
                End If 
            Else 
                col = rtrn(1) 
            End If 
        End If 
        lenghtofmyrange = Len(myrange) - 1 
        If Len(myrange) - endpoint < 1 Then 
            MsgBox invalidcell, vbOKOnly, "'Extract To' Cell Out Of Range" 
            End 
        End If 
        Row = CInt(Right(myrange, Len(myrange) - endpoint)) 
        If Row < 1 Or Row > (65536 - Selection.Count) Or col < 1 Or col > 254 Then 
            MsgBox invalidcell, vbOKOnly, "'Extract To' Cell Out Of Range" 
            End 
        Else 
            For Each c In Selection 
                If firstrun Then 
                    cprev = c.Value 
                    Cells(Row, col).Value = "'" & cprev 
                    Row = Row + 1 
                    firstrun = False 
                Else 
                    ccurr = c.Value 
                    If ccurr  cprev Then 
                        Cells(Row, col).Value = "'" & ccurr 
                        cprev = ccurr 
                        Row = Row + 1 
                    End If 
                End If 
            Next c 
        End If 
         
    Else 
        MsgBox invalidcell, vbOKOnly, "'Extract To' Cell Out Of Range" 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Side Note: The resulting set that gets generated has a PrefixCharacter of ' forcing the cell to be text. I did this mostly because it's what I deal with most, not to mention that I have a "nix_tix" function that removes the tic mark if necessary. I also did it so that when values with leading zeros get extracted, the leading zeros remain on the value. Without adding the tic mark the leading zeros were being truncated. If this code can be re-written to be more globally used or to be more efficient I'd love to see/use it.

Enjoy!

Mav

I am trying to find a way to create a list of unique items from a named range. I have found a few solutions using filters for ranges that are contained in the one column but not named ranges that contain multiple rows and multiple columns.

Is there any way to copy all of the unique items from the named range to another location? Lets call the named range Table1 and it spans from B2 to J100.

Thanks in advance!!
Andrew

Hello! Friends

I have a list of names containing duplicate names and empty cells.

Using Data > Validation > List

I want get a drop down list of unique entries having no empty cells.

How can do this??? Please help me...

I am using the change event to monitor when a cell changes on a worksheet A. Depending on the value in another worksheet (B), I need to reset the values of three cells on worksheet A (all of which are subject to data validation -- lists) to values from a third spreadhseet (C).

I have a subroutine that works perfectly when called by the user, but when the change event subroutine calls it, it merely stops working at the first assignment statement. The routine I'd like to use is


	VB:
	
) 
    Dim Pre As String, Suf As String 
    Application.EnableEvents = False 
    Pre = Trim(Prefix) 
    Suf = Trim(Suffix) 
    Range(Pre & "_inp6") = Worksheets("C").Range("ttp_250_" & Suf)(2) 
    Range(Pre & "_inp7") = Worksheets("C").Range("dor_250_" & Suf)(2) 
    Range(Pre & "_inp8") = Worksheets("C").Range("cbr_250_" & Suf)(2) 
    Application.EnableEvents = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Why is it stopping here?

Hello! Friends

I have a list of names containing duplicate names and empty cells.

Using Data > Validation > List

I want get a drop down list of unique entries having no empty cells.

How can do this??? Please help me...

I did a custom data validation for unique values.

This is my formula used:

=countif(C3:E5,C3)=1

However, it does not work.

Please advise.Thanks.

This seems like a difficult one, way above me.

What I want is a data validation list in a cell on one worksheet. The list should include all the unique values from a range in another worksheet (There will be more that one similar value in the range and but I don't want them to repeat in my list).

Is this possible!!??

I need help with creating a formula that returns value(s) similar to data/advanced filter/unique records/copy to location. I have one column of sorted numbers with many duplicates. Without using the data filter, I want to create a formula on another sheet that returns a list of the unique values from that column (excluding blanks). I have stumbled through a combination of OFFSET, INDEX, MATCH and COUNTIF functions but can't seem to get a formula that works that I can also copy down the rows. Any Ideas?
BuzzG

Wish a happy day to all

i need the help to create a list of unique values from a data.

i already created a list in the enclosed file but the values are repeating in this list and i need that one value should not be repeated. Further, the list should be sorted from low to high value

thanks in advance for any support

I have a spreadsheet that has whole numbers in. MAny of these are repeats. I need to be able to populate a list box with the unique values from the list. How do i go about this in VB?

Any help please.

Antony

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,

I have been looking for an entire day to find a solution to this. Found
'some' answers but not the thing I need.

I got a few workbooks with a lot of worksheets. For almost every workbook
there needs to be a list of unique values coming from different worksheet
(always in the same workbook). I would like to get a clue on how to do that.

So suppose worksheet 1, and worksheet 2 I would like to create a list on
worksheet 3 gathering all unique values from columns B (of both worksheet 1
and 2).

So far I have been directed towards VBA, some commercial add-ins and the
classic copy and paste.
But you can imagine that I do not like to pay for such a (in my opinion)
basic function, nor copy over 20.000 rows (risking a lot of errors and
'forgotten' rows) and VBA well... I'm not good at it...

I tried to filter data, but can only do that for a single column. And I
really need a list, so dropdown won't do...

Suggestions very much appreciated.

I have a list that is going to constantly be changing. One time the list may
have 185 records & next time the list might have 18,212 records. I need to
evaluate the values in a column & return a list of only the unique values in
the column !!!without using filters, pivot tables or any menu items - it has
to be formulas or arrays ONLY because my users wouldn't know how to handle
the pivot tables or follow directions from the menu!!!

For example...

3.03
3.03
3.5
3.57
3.99
3.99
3.99
4.0
4.0
4.1
4.3
4.33

The result would be:

3.03
3.5
3.57
3.99
4.0
4.1
4.3
4.33

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

Hi forum,

I am using Excel 2010.

I have a range named Data that runs down one column. There is a lot of duplication of the values within the range

I am trying to use the following formula (which I got out of an Excel book) to get a list of unique values:

=INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT("1:"&ROWS(Data))),MATCH(Data,Data,0),""),ROW(IN DIRECT("1:"&ROWS(Data)))))

I enter the formula into a single cell then press Ctrl+Shft+Enter

All I get is the value that is on top of the list.

What am I doing wrong and how can I get a list of ALL unique values from the range?

Thanks very much

Dave

I need a UDF to make live easier. I have a huge excel file and I need a final function (I suppose a UDF) that lists all values from a range spanning over multiple columns and rows.
I would not really like a macro, since it does not update when certain values are changed.

It's part of a bigger functionality, but I need this one, really bad, since it spans over 6000 rows as the search range, and 7000 as the search values. In short:
I got to list all the unique values. They are in specific cells on worksheet 1 (A1:C5 and A25:C31) and should be listed on worksheet2 starting in field A2. As 'simple' as that. Nothing more, nothing less, just list all unique values of a certain range, which I specify. If it can not be done, by selecting the two fields separately, it's no problem at all to list all values in eg A1:C6000. Do note that some of the cells will be blank, and I don't need blanks. I only need the actual data sets.

I added a sample excel file, that shows a representation of the data. I can not upload the actual file, since it's way too big (60 MB). So a sample file should give you the idea.
Three sheets:
Data 1: some value lists multiple columns
Data 2: some value lists, again multiple columns
Desired result: the result as it should be listed.
example.xls.zip

Thanks in advance...

Cross-ref on the same http://www.ozgrid.com/forum/showthread.php?t=86444

Hi all,

I've been using Dave Hawley's "Excel Data Validation List - Automatically Add to a Data Validation List" in my wb with great results, but now I am going to use a secondary wb to keep the Data Validation List and herein lies my problem. I lack the VBA skills to be able to modify the code and references so that when I input into the primary wb the data is updated in the secondary wb.
The Primary wb is an xlt that I'm developing, each time a new xls is opened from the Primary.xlt, vba in the Primary.xls opens Secondary.xls.
Secondary.xls has a named range "Names" which I wish to be able to update when data validation is performed in Primary.xls per the sample on this web site.http://www.ozgrid.com/Excel/excel-va...ist-update.htm
If anyone is able to help it would be greatly appreciated. If there is any info I have omitted please let me know.

Thanks in advance

I have a column that sometimes has multiple values in a single cell. I want to pull the unique values from that cell using a formula.

Example
Column A

2) Dog
3)Cat
4)Bird,Bird,Cat,Dog
5)Frog,Dog,Frog,Frog,Frog
6)Mouse
7)Monkey

In cell B2 I want Dog, Cell B3: Cat and B4: Bird,Cat,Dog, B5:Frog,Dog

I want a formula I can type in B and have it look to the Cell and extract unique values

I'm having troubles creating a Data Validation List from a range. I have a list with:

All
All
Various
Various
Various
Finance
Exploration
Exploration
Exploration
Exploration
Exploration
Exploration

etc in Column A

I need a Data Validation List showing only:
All
Various
Finance
Exploration

Is there a way to get rid of the duplicates?

Thanks!

I have a cell that uses data validation (list) which points at a named range
that represents a range on a different worksheet.

I need to be able to reset the cell value to the first value on the named
range list. Here is my sample code, which isn't working:

S = Sheet12.Range("E2").Validation.Formula1
Sheet12.Range("E2").Value = Range(S)(1)

debug.print Sheet12.Range("E2").Validation.Formula1
results in the named range
=DatesForSelection14days

when I use the immediate window to set:
S = Sheet12.Range("E2").Validation.Formula1
then
debug.print Range(S)(1) I get
10/3/2008
which is correct.

But when the two lines of code run within the sub itself, on the second line
I get a run-time error 1004, method 'Range' of object '_worksheet' failed
huh?

Any advice greatly appreciated.
Thanks,
Keith

This may need programming to do but I am not sure. I have a column, C,
of 150 cells each with a data validation linked to a named list, lets
say X, Y Z being the values in the list. What I would like ideally is if
there is a value in cell A1 for example, then the first value X of the
data validation list should appear in cell C1. Same for A2 and C2, A3
and C3, and so on. Every time it is simply the first value of the drop
down list that appears in the cell. If the user wants to change to Y or
Z then he has the drop down selection option. I am aware I could
probably do it with a control toolbox combo box but there are 150 cells
with data validation in the column which I do not think would be
suitable in this situation.
I value any guidance.

Kind Regards,
Graham Haughs
Turriff
Scotland

Hi,

I am trying to create a script that when run produces a list of values from a source data list. However the source data list may contain the same value more than once, how do I get it to remove the repeated values and show it in this new list?

Thanks,

Jon

I have a problem, using a macro I have already filtered a data set for
a unique value using the autofilter method. I now want to obtain the
unique values from another column within this range. When I try and
apply and advanced filter to generate the unique values it automtically
takes off the advanced filter ruining my filter range.

Is there a way I can do this while still using the advanced filter
method?

Hi,

I just need a way to control my Auto Filter by what a picked in a data validation list box.

Here the setup: Sheet1 = Summary, Sheet2 = Data

Sheet1 has a data validation box with peoples names in them.
Sheet2 is setup with autofilter

What I want some how is once a name is picked from the list is for the autofilter to filter by this name.

Can someone get me going on this?

Thank You, Mike