Results 1 to 3 of 3

Thread: Ignore Duplicate Entries on Dynamic Validation List

  1. #1

    Ignore Duplicate Entries on Dynamic Validation List



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I am using =OFFSET('IB BRANCHES'!$B$2,0,0,COUNTA('IB BRANCHES'!$B:$B)-1,1), but when I am entering any duplicate field, its showing on the Data Validation List. Can I modify the formula to prevent the duplicates from showing on the Validation List.

    Please let me know.

  2. #2
    Anyone Please

  3. #3
    Good morning,

    I'm not sure about modifying the formula, but you could use a helper column to get a unique list. Something like:

    Col A - Current List of all things
    Col B - =if(countif($A$1:A1,A1)>1,"",row()
    Col C - =Index(A:A,match(small(B:B,row()),B:B,0)) **if you have a header(s) you will have to modify the small function to: small(B:B,row()-**# of rows of headers here**)

    Column B gives a dynamic number to each unique entry and column C pulls each unique in the order in which they appear.

    Hope this helps,

Posting Permissions

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