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]

I check the E108. Already took care of it. Our system apparently works in some special ways, there are certain items that has some exception to the process. While were at it, i want to take a chance if you can solve a new requirement. I really didn't notice this in the beginning that's why its not included on the first worksheet. Check this updated worksheet and check the rows 144-147.

https://docs.google.com/spreadsheet...pIeks0N2drOE40OU5nNmhPeXc&usp=drive_web#gid=0
The first worksheet includes Door No. of our own equipment. However, we also have what we call cross-hired equipment.

These equipment came from our suppliers. We do not own them. In the event our client requested an equipment and its not available with us, we rent it from our suppliers.

Cross hired are treated differently. First our naming system start with "X-" followed by the Door No. named by our supplier. Once the Cross-hired equipment is demobilize we will not tag it as Available, it will go back to our supplier, because it is not our own.

The rows 144-146 are crosshired equipment because they have the "X-" on the Door No. Except for row 147, the first three were Mobilized and they were also Demobilized.

So this is my requirement , once a cross-hired equipment is recognized with a value on Date Mobilized and Date Demobilized it should be tagged as "Returned to Supplier".

Lastly, the row 147 will be have this condition: If the Date Demobilized is blank AND the Door No. starts with "X-" the Status will be "Crosshired-Deployed".

 

[caabyyc]

If "Crosshired door" was returned to supplier on 22/Mar/13(D146), it is still possible for your customer to re-rent the same door from you later, this condition is not included in your updated file. use the door at A146 as example, copy that to Row 148, then intentionally delete the date in D148. what do you want to see in D146 ? (blank or "Return to Supplier")
A. if you want to see "Return to Supplier", use following in E2,change "+++" to "<",then copy to bottom
=IF(LEFT(A2,2)="X-",IF(D2="","Crosshired - Deployed","Return to Supplier"),IF(D2="","Deployed",IF(COUNTIF($A$1:A2,A2)+++COUNTIF(A:A,A2),"","Available")))<COUNTIF(A:a,a2),"","available")))<countif(a:a,a2),"","available")))

B. if you want to see blank, use following in E2, change "+++" to "<"(2 places),then copy to bottom.
=IF(LEFT(A2,2)="X-",IF(D2="","Crosshired - Deployed",IF(COUNTIF($A$1:A2,A2)+++COUNTIF(A:A,A2),"","Return to Supplier")),IF(D2="","Deployed",IF(COUNTIF($A$1:A2,A2)+++COUNTIF(A:A,A2),"","Available")))<COUNTIF(A:a,a2),"","return ,?Deployed?,IF(COUNTIF($A$1:A2,A2)<COUNTIF(A:A,A2),??,?Available?)))

I check the E108. Already took care of it. Our system apparently works in some special ways, there are certain items that has some exception to the process. While were at it, i want to take a chance if you can solve a new requirement. I really didn't notice this in the beginning that's why its not included on the first worksheet. Check this updated worksheet and check the rows 144-147.

https://docs.google.com/spreadsheet...pIeks0N2drOE40OU5nNmhPeXc&usp=drive_web#gid=0
The first worksheet includes Door No. of our own equipment. However, we also have what we call cross-hired equipment.

These equipment came from our suppliers. We do not own them. In the event our client requested an equipment and its not available with us, we rent it from our suppliers.

Cross hired are treated differently. First our naming system start with "X-" followed by the Door No. named by our supplier. Once the Cross-hired equipment is demobilize we will not tag it as Available, it will go back to our supplier, because it is not our own.

The rows 144-146 are crosshired equipment because they have the "X-" on the Door No. Except for row 147, the first three were Mobilized and they were also Demobilized.

So this is my requirement , once a cross-hired equipment is recognized with a value on Date Mobilized and Date Demobilized it should be tagged as "Returned to Supplier".

Lastly, the row 147 will be have this condition: If the Date Demobilized is blank AND the Door No. starts with "X-" the Status will be "Crosshired-Deployed".

</COUNTIF(A:a,a2),"","return></COUNTIF(A:a,a2),"","available")))<countif(a:a,a2),"","available")))

 

[caabyyc]

anytime when I use "<" in my formula, this forum system will automatically delete the section after "<".......what is wrong with it ??

 

[adorlytanglao]

Thank you very much, it works.