Consulting

Results 1 to 8 of 8

Thread: Duplicate Values in Data Validation

  1. #1
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location

    Question Duplicate Values in Data Validation

    Hello, how do I remove the duplicates in the data validation on column B?

    I read about =SORT(UNIQUE for O365 however that's not showing up for me. (Maybe because I am using O365 Plus Pro?)

    Attached is the sample file.

    thank you
    Attached Files Attached Files

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Some reading

    https://www.myonlinetraininghub.com/...ic-unique-list

    https://www.myonlinetraininghub.com/...dynamic-arrays


    Especially

    Data Validation

    Now that we have our nicely sorted unique list it makes sense that you might want to use it in a data validation list. To reference a spilled array we simply use the new Spilled Range Operator, #, as shown in the Data Validation ‘Source’ field below:

    The above approach requires a 'sort of' 'helper' column


    A more involved approach that doesn't require the helper column

    http://www.vbaexpress.com/forum/show...rkbook-Startup
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Hi Paul, I have a few questions:
    1: what are _xlfn and _xlws? Are they named ranges? Why can't I find them in the Name Manager?
    2. how come sort and unique are working with those two but I can't make it work?
    3. why can't I click on the dropdown list in C2?

    I posted in the thread instead of here

  4. #4
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Quote Originally Posted by Paul_Hossler View Post
    A more involved approach that doesn't require the helper column

    http://www.vbaexpress.com/forum/show...rkbook-Startup
    Hmmm I'm not sure how I'll be able to apply this to mine because I need to use it in multiple rows in the same column

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Hidden names

    Option Explicit
    
    
    Sub unhideAllNames()
        Dim tempName As Excel.Name
        
        For Each tempName In ActiveWorkbook.Names
            tempName.Visible = True
        Next
    End Sub
    Attached Images Attached Images
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Oh okay so they're hidden.. Why am I unable to click on the dropdown list?

  7. #7
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Capture.JPG

    Using Example_1.xlsm, does the list (above) look the same?

    What happens when you try the dropdown list on the other sheet?

    Capture.JPG
    Last edited by Paul_Hossler; 05-03-2020 at 12:06 PM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Oh let me try

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •