Data validation on a column based on another cell content on a different column and different row

[sibi1235]

I am trying to creat a data validation on a column where it doesnt allow duplicate entries based of content from another column correspondence to the original entry in the first column. 

Eg:

column A - 1,2,3,4

Corresponded column B - occupied, Not occupied, Not occupied, Not occupied

If someone enters number 1 in column A again, it should trigger error message because the corresponding value in column B says “occupied “

hope this is clarified and hope someone can help 🙂

[#2]

@sibi1235 

Let's say you enter data in A2:A50 (A1 being a header).

Select A2:A50; A2 should be the active cell in the selection.

On the Data tab of the ribbon, click Data Validation.

Select Custom from the Allow drop-down.

Enter the following formula in the Formula box:

=SUM(($A$2:$A$50=A2)*($B$2:$B$50="Occupied")*(ROW($A$2:$A$50)<>ROW(A2)))=0

Click OK.

[#3]

@HansVogelaar WOW i spend nearly 6 hours on chatGPT to fix this and you come as an Angel and fixed this in 6 seconds ! You are amazing, Thank you so much ! I got exactly what I wanted to achieve. Thanks  again