Validation list populated from rows having a specific text

[candeniz]

Suppose column "A" has inserted with multiple data (numeric & text) and some includes the word "HOTEL" end of each phrase. Eg: AMBASSADOR HOTEL


Can Data Validation List in row B2 be auto populated from the rows containing text "HOTEL" and displays them in dropdown menu without VBA. Eg: AMBASSADOR HOTEL, THE ELYSIUM HOTEL, etc.


Thanks in advance

 

[footoo]

Create a new list that identifies cells containing "HOTEL" and use the new list in Data validation.
The easiest way is to use column B for the new list.

Enter in B1 : =IF(ISERROR(FIND("HOTEL",UPPER(A1),1)),"ZZZ",A1)
Drag down.
Select columns A:B and sort by column B
The range for the data validation list will be the cells in column B before the first ZZZ.