Prevent duplicates in cells where there is a dropdown list

[IvayloKonsulov]

Hello,

I would like to ask if it is possible to prevent duplicates using data validation in cells where there is a drop down list created with data validation? For example, in my case I have cells in a column (A1:A5) where I have to select an option from a drop down menu using the same list, e.g. fruit (bananas, oranges, apples, peaches, etc.). I want to prevent selecting bananas in 2 cells at the same time. The problem is the only option I think of for preventing duplicates is using data validation and COUNTIF formula, but that will automatically exclude the dropdown list from data validation.

I would be grateful if anyone has some help to offer.
Thanks in advance.

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this is any use.
I have set up your full list in column D
E1 formula is copied down the same number of rows as the column D list
Data Validation is set up as shown below.
You could hide columns D:F if you want.

A_MrExcel.xlsm
	A	B	C	D	E	F
1				bananas	oranges	$E$1:$E$2
2	apples			oranges	peaches
3				apples
4	bananas			peaches
5
6
DV without duplicates

Cell Formulas
Range	Formula
F1	F1	=ADDRESS(ROW(E1),COLUMN(E1))&":"&ADDRESS(ROW(E1)+COUNTIF(E1:E4,"?*")-1,COLUMN(E1))
E1:E4	E1	=IFERROR(INDEX(D$1:D$4,AGGREGATE(15,6,(ROW(D$1:D$4)-ROW(D$1)+1)/ISNA(MATCH(D$1:D$4,A$1:A$5,0)),ROWS(E$1:E1))),"")

Cells with Data Validation
Cell	Allow	Criteria
A1:A5	List	=INDIRECT($F$1)

 

[IvayloKonsulov]

Thanks for the quick answer!

Your solution helped me a lot - not only with my current case, but also solved another problem I had.

 

[Peter_SSs]

Cheers. Thanks for the follow-up.