Ignore Duplicate Entries on Dynamic Validation List

crossword

New member
Joined
Jun 26, 2013
Messages
10
Reaction score
0
Points
0
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.
 

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
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,
 
Top