Closed Thread
Results 1 to 27 of 27

not showing duplicates in data validation list from dynamic table

  1. #1
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    not showing duplicates in data validation list from dynamic table

    Hi,

    how can i avoid duplicates in data validation list from dynamic table?

    As data validation list source i have:

    Please Login or Register  to view this content.
    Screenshot_44.png

    Please help,
    Jacek

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: not showing duplicates in data validation list from dynamic table

    You'd have to use code to create a unique list in another range. If you are certain that the list won't ever exceed 255 characters, you could also use a delimited string instead.
    Rory

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: not showing duplicates in data validation list from dynamic table

    You don't have to use code - it can be done with a formula if you prefer.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: not showing duplicates in data validation list from dynamic table

    True, if you don't mind overloading a sheet with array formulas sufficient to cover any length of table. Or you could use PQ as long as you remember to refresh.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: not showing duplicates in data validation list from dynamic table

    Rory - it doesn't require an array formula.

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: not showing duplicates in data validation list from dynamic table

    Do tell then.

  7. #7
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: not showing duplicates in data validation list from dynamic table

    Hi,

    thank you.

    VBA code - only as the last solution.

    oo, it would be great to have this in formula.

    Best,
    Jacek

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: not showing duplicates in data validation list from dynamic table

    Jacek - please provide a sample workbook and I will give you a solution using a non-array formula.

  9. #9
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: not showing duplicates in data validation list from dynamic table

    In attachment
    Attached Files Attached Files

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: not showing duplicates in data validation list from dynamic table

    No, not a ZIP file - please upload a workbook (.xlsx). It does not need to be your entire dataset - just a small sample workbook.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: not showing duplicates in data validation list from dynamic table

    Oh, well - never mind. I'm sorry, but I won't open a .zip file and I don't have time to create a mock-up from scratch. Your choice.

  12. #12
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: not showing duplicates in data validation list from dynamic table

    Omg.

    Zip is allowed here so i uploaded.

    Xlsx in attachment.

    Book1.xlsx

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: not showing duplicates in data validation list from dynamic table

    ZIP is allowed here, yes, but I won't open ZIP files - I explained why in my PM to you. I will have a look at your file now.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: not showing duplicates in data validation list from dynamic table

    OK. In F2 (this column can be hidden, or the list can be placed on another tab):

    =IFERROR(INDEX(t_table[Tab1],MATCH(0,INDEX(COUNTIF($F$1:F1,t_table[Tab1]),0,0),0)),"")

    Copy down as far as you need.

    In the data validation box:

    =INDIRECT("$F$2:$F"&MATCH(99^99,$F:$F))
    Attached Files Attached Files

  15. #15
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: not showing duplicates in data validation list from dynamic table

    Was it the Bard who said "an array formula by any other name would be as computationally expensive"?

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: not showing duplicates in data validation list from dynamic table

    Rory - all formulae with arrays in them are array formulae. None of these require entering with CTRL+SHIFT+ENTER, however, which is what I thought you were referring to. Stop trying to score points - it's not very helpful to the OP.
    Last edited by AliGW; 08-13-2018 at 12:01 PM.

  17. #17
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: not showing duplicates in data validation list from dynamic table

    Thank you,

    nice!

    can you explaing the result (=5) for below code?:

    Please Login or Register  to view this content.
    What is 99^99 (result = 3,69729637649726E+197) here?

    Best,
    Jacek

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: not showing duplicates in data validation list from dynamic table

    Yes, of course!

    MATCH(99^99;$F:$F)

    99^99 is just a very large number - a number that will be greater than any number in column F. As long as it is greater than any number in that column, it will return the last row with a number in it as a match. In this case, it is whichever is the row of the last cell in the list to contain a value. If you add more values and the lookup list grows, then the row number returned here will also grow, and because of that, your lookup list will grow.
    Last edited by AliGW; 08-13-2018 at 12:50 PM.

  19. #19
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: not showing duplicates in data validation list from dynamic table

    thank you for explanation,

    Best,
    Jacek

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: not showing duplicates in data validation list from dynamic table

    You're welcome!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  21. #21
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: not showing duplicates in data validation list from dynamic table

    One more thing.
    How to sort this?

    Best,
    Jacek

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: not showing duplicates in data validation list from dynamic table

    That's not just one little extra - it's a whole new ball game!!! Why didn't you say this at the start?

    Back to the drawing board ...

    Will these only ever be numeric values?

  23. #23
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: not showing duplicates in data validation list from dynamic table

    Hi AliGW,

    sorry i didnt know about it. Tomorrow morning (Warsaw Time) i found out about this.

    It can be numeric or alphabetic...

    Best,
    Jacek

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: not showing duplicates in data validation list from dynamic table

    Oh, dear - OK, well you will need to give me time to work through it. As I said, it needs a whole new approach.

    Can you give me some properly representative data to work with? Are we talking about a mix of numeric and alphabetic in one list?

    This will definitely require array entered formulae (that use CTRL+SHIFT+ENTER), I am afraid.
    Last edited by AliGW; 08-14-2018 at 02:51 AM.

  25. #25
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: not showing duplicates in data validation list from dynamic table

    As you have started a new thread for the sorted list, I am marking this thread as solved. I notice that in that thread you have also ditched everything I have done for you here. I hope that others will be able to guide you to a solution, but I'm bowing out now.

    For anyone interested in helping, the new thread is here: https://www.excelforum.com/excel-pro...mic-table.html

    No further posts should be made to this thread. Thanks.

  26. #26
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: not showing duplicates in data validation list from dynamic table

    AliGW,

    this is about formula, other topic is about VBA. I have to know 2 solutions.

    I can open new topic for sorting lists and getting unique values.

    Best,
    Jacek

  27. #27
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: not showing duplicates in data validation list from dynamic table

    No, I'm sorry - you can have only one thread per issue and get whatever help you need with it there, in one thread, both formulae and VBA. Don't open any further threads.

    This thread is now closed.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Data Validation & Dynamic named ranges - full list not showing
    By dancing-shadow in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-19-2017, 06:55 AM
  2. Dynamic Data Validation List Using Pivot Table
    By seethesun in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 05-05-2016, 11:02 AM
  3. Dynamic Validation List from DB Like Table
    By codewalk3r in forum Excel General
    Replies: 3
    Last Post: 09-02-2015, 10:16 AM
  4. [SOLVED] Data Validation List to Return Variable/Dynamic Data based on Reference Table
    By dilbert1865 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-13-2014, 09:08 AM
  5. Replies: 3
    Last Post: 07-05-2013, 03:32 PM
  6. Replies: 1
    Last Post: 10-11-2010, 06:19 PM
  7. [SOLVED] Showing or Hiding rows based on data validation list
    By Magnet Peddler in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-12-2006, 04:40 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1