Determine if equipment Available or Deployed

adorlytanglao

New Member
Joined
Jun 23, 2013
Messages
46
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hi

I think you are better off trying to find a Google spreadsheet forum. As far as I can tell this requires an array-formula and those are a little different in Google docs.

Best regards
Per Erik
 
Upvote 0
There are some forums but i dont think i will be able to familiarize myself quickly to the environment compare to this. I understand that i used google docs to upload the spreadsheet, because i didn't find any attachment capability here, so i though a link would work. So i need what is the preferred third party sharing site for me to upload and link up my documents. In the meantime, since this is no different to an ordinary excel file, and if you have an idea how to answer my query, you may download the file by clicking the link, Click "File" on the top-right part of the page, and choose "Download As"
 
Upvote 0
Hi

I think this formula array-entered meets your requirements:
=IF(D28="";"Deployed";IF(MAX(IF($A$2:$A$143=A28;$C$2:$C$143;""))=C28;"Available";""))


Best regards
Per Erik
 
Upvote 0
Tested on Excel version.
Put the formula below into E2 and drag down.

=IF(A2=A3,"",IF(D2="","Deployed","Available"))
 
Upvote 0
Hi perimidt, thank you for the response. I tried your formula, copied it on cell E28. It only shows the Deployed. One thing, the formula you are using uses ";" instead of ",. So i changed it because its not being accepted, i need to know if its supposed to be that way of I am using a different version of some sort. If you tried applying this on the same excel file i uploaded and it works on your end, can i see that file you are working on.
 
Upvote 0
Tested on Excel version.
Put the formula below into E2 and drag down.

=IF(A2=A3,"",IF(D2="","Deployed","Available"))

This formula perfectly works only when the table is sorted based on the Door No. But the original table is supposed to be sorted on Date Mobilized oldest from the top, because it is a tracking history, I just sorted it out by the Door No. because i thought it will be much easier that way for my requirement to be solved. So when i tried sorting it back to Date Mobilized your formula is now displaying it differently.
 
Upvote 0
=IF(D2="","Deployed",IF(COUNTIF($A$1:A2,A2)<COUNTIF(A:A,A2),"","Available"))
is there any problem at E108 for "RTC0019", that is unique equipment# and it should be "Available" not blank.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top