Generate a report on second sheet from a filtered values from the first sheet

The table on Sheet 1 is being used a record system for history of each lifting equipment we mobilized to our client for rental. The Door No. (Column A) is a name for each equipment. The Date Mobilized is the date when equipment left our premises and started working on site and Date Demobilized is the date when equipment went back to our yard.

To explain my requirement, refer to the first 7 rows indicates that the Door No. ATC0005. It was mobilized 7 times but demobilized 6 times. Meaning that the equipment is currently deployed because the last mobilization dated 01-Jul-13 doesnt have an corresponding data of Demobilization. My first requirement is to have a filter where in it will show the list of currently mobilized equipment. In that case I just go to Date Demobilized and filter it by only showing the blanks.

My second requirement is to know how to filter to only show the available equipment. I was able to analyze it this way: For example we take a look at the history of FLD0001, it has 5 Mobilization Date and 5 Demobilization Date. So i need to know a function to filter a Door No. that has the same number of Mobilization Date and Demobilization Date. My first solution is to just filter the Date Demobilized to not show blanks hence it will show all equipment that has the same number of Date Mob. and Date Demob. But my exact requirement is to have a real time report on a separate sheet (refer to Sheet 2).

Sheet 2 is report for equipment status. Under Equipment (Column A) are the list of equipment. Using the data available on Sheet 1, the cells under Client (Column B) should be filled up either "Available" or "Deployed".


Lastly, for all Deployed equipment only the Client Name should also be retrieved on the report on Sheet 2.

filter values.xlsx

bump no response

Thank you for your response. I was able to determine base on the pivot table which equipment is available or deployed by returning the value of cells iN column H and Column I if they are equal or not. I think, if we follow this path it is possible to accomplish my exact requirements.

First, remove and incorporate the exact pivot table you made on Sheet 2 so that it will be used as my report.

Second, if a certain equipment returns the value of Deployed as the status, it should retrieve the Client Name. The Client Name that will show up is determined by the certain row on Sheet 1 that doesnt have a date demobilized. Example is Row 8: the Door No is ATC0008, it doesnt have a Date Demobilized, so the Client Name that will show up on the report on Sheet 2 is "E".

If its no bother, I want to know exactly how the pivot table was made, specifically base on my scenaro. If in case you can provide me the steps, teach me on how to make this on a separate sheet. I think upon learning how to do a pivot table myself, i will be able to understand and can solve my own requirements.

Just an update i was able to recreate the pivot table myself, and i did it on Sheet 2. All i need now is to accomplish my 2nd requirement. to determine the Client Name of each deployed equipment.

I think that will work. We just to determine the recent date from the list of Date Mobilized under one equipment then it will return the value of Client, but i don't know how to do that. Using the same workbook you uploaded, can show how me for example the ATC0005 to retrieve the Client "E" on cell J6. Because the ATC0005 equipment has the recent mobilization date of 15-Jun-13.

Hi, I was able to encounter some error for the pivot table. It is given that a certain Door No., for example ATC0005, has unequal value of Date Mobilized and Date Mobilized hence it should be deployed. But when we go row #8 to put a date value on cell D8 or the Date Demobilized, Date Mob. and Date Demob. should have equal value, but you will notice on the pivot table on the right the Count of Date Demobilized for ATC0005 remains 6, however we should expect that it will return 7.

bump no response

bump no response

bump no response

bump no response

Good day MarvinP, thank you for giving me some materials to teach myself about pivot table. Apparently i need to search some other learning materials about pivot tables for beginners like me because upon checking the files, it might take me time to understand. On the other hand, i was able to simplify my requirements and i think it will only need a formula.

Using the same worksheet - the formula should be on Column E and will 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. Then the adjacent cell or D28 should not be equal to blank. Result: The cell E28 should return Available.

I know the formula how to get Deployed.

on Cell E2

Formula:

=IF(D2="","Deployed","")

I just need to have that formula modified to get the Available.

bump no response