Determine if equipment Available or Deployed

[adorlytanglao]

https://docs.google.com/spreadsheet...pIeks0N2drOE40OU5nNmhPeXc&usp=drive_web#gid=0

The table is a tracking for equipment rental. Each Door No. has a history, for example the Door No. ATC0005 took up the Cell A2 to A8. Each history has a record of Date Mobilized and Date Demobilized.

I need a formula to under Column E to return only 2 values: Deployed or Available.

A. Deployed is returned by determing if a certain cell under Date Demobilized is blank.

B. Available is return by determining 2 conditions using AND statement.

1. Get the list of Door No. that has the same value, for example ATC0010 (Row 25 - 28), it needs to determine the cell which has the recent date of Date Mobilized, in that case its C28.
2. The adjacent cell or D28 should not be equal to blank. Result: The cell E28 should return available

 

[adorlytanglao]

@caabyyc, is the formula you type the exact way it was written on the cells? because i tried copy and pasting, i got some errors even if i corrected it by adding ")" in the end. I was thinking of modifying it to fix the error but that would mean it might display differently the way you intended it to. Please check the sheet on the above link and give me the correct formula. Thank you.

 

[caabyyc]

=IF(D2="","Deployed",IF(COUNTIF($A$1:A2,A2)<countif(A:A,A2),"","Available")) <countif(a:a,a2),"","available"))<countif(a:a,a2),"","available"))< html=""></countif(a:a,a2),"","available"))<countif(a:a,a2),"","available"))<>

 

[caabyyc]

<countif(a:a,a2),"","available"))
<countif(a:a,a2),"","available")) <countif(a:a,a2),"","available"))<countif(a:a,a2),"","available"))<="" html="">=IF(D2="","Deployed",IF(COUNTIF($A$1:A2,A2)delete<countif(A:A,A2),"","Available"))


type above in E2,then delete the wording of "delete"</countif(a:a,a2),"","available"))></countif(a:a,a2),"","available"))

 

[adorlytanglao]

@caabyyc, the formula is still the same.

 

[caabyyc]

=IF(D2="","Deployed",IF(COUNTIF($A$1:A2,A2)<COUNTIF(A:A,A2),"","Available"))

@caabyyc, the formula is still the same.

 

[caabyyc]

=IF(D2="","Deployed",IF(COUNTIF($A$1:A2,A2)+COUNTIF(A:A,A2),"","Available"))

 

[caabyyc]

Then change + to <, this F*** system always partially display my this formula....

=IF(D2="","Deployed",IF(COUNTIF($A$1:A2,A2)+COUNTIF(A:A,A2),"","Available"))

 

[caabyyc]

Sorry for coarse word, it seems the forum always delete the portion from < to then end , pls change the "+" to "<" in formula then hit enter

Then change + to <, this F*** system always partially display my this formula....

 

[adorlytanglao]

Thank you very much, it worked. That is exactly what i need.

 

[caabyyc]

Ur welcome. As what I said, pls double check E108, in your example, it is blank, in the list by using my formula, it is " available ". Time to sleep now, bye.

Thank you very much, it worked. That is exactly what i need.