Guild of Project Controls: Compendium | Roles | Assessment | Certifications | Membership

Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Filter Open ended Activities??

3 replies [Last post]
Wed, 2006-06-21 10:44
sam dsoza
User offline. Last seen 5 years 51 weeks ago. Offline
Joined: 20 Jun 2006
Posts: 55
Groups: None
Hi
I would like to filter open ended activities (no predecessors/successors). Could anybody help me with this??
I am not niteresting in Using Schedule tool as I wish to view those activities in Bar chart format
Also, I have created a new column and marked activities which did not have predecssor or successors or both.
I was wondering if there is an easier way than this to do the same.
Thanks
Top

Replies

Wed, 2006-06-28 18:09
#1
Jaime Linchangco
User offline. Last seen 15 years 30 weeks ago. Offline
Joined: 16 Jul 2001
Posts: 29
Groups: None
Hi Milind,

Good Day!

Assuming your have done VLOOKUP correctly, #N/A only means that particular activity is not open-ended. Delete all rows containing #N/A leaving only those with NP and NS. Re-sort "ACT" to remove those blank rows in between. Save it and import it back to your P3 schedule.

In case you still experiencing any problem, then give me your email address and i will send you an excel file to give you an example.

Cheers!

Jaime

Top
Wed, 2006-06-28 14:45
#2
sam dsoza
User offline. Last seen 5 years 51 weeks ago. Offline
Joined: 20 Jun 2006
Posts: 55
Groups: None
Jamie

Using Vlook up function is a BIg problem. If you can exlain in greater detail then it would be great. I get "N/A#" error for activities that do not contain NP or NS and I really do not know whats to be done with activities having both.

Thanks
Milind
Top
Fri, 2006-06-23 19:36
#3
Jaime Linchangco
User offline. Last seen 15 years 30 weeks ago. Offline
Joined: 16 Jul 2001
Posts: 29
Groups: None
Hi Millind,

There another way using Excel. It’s a little bit tricky and need few knowledge in excel "Function" command. Try the following steps.

1). Create new activity code OPEN (Lenght=2) with values of "NP" for No Predecessor and "NS" for No Successor.

2). Export all with Activity ID and Activity Code as "open.dbf". I always preferred data base.

3). Under Tool, Go to Schedule and click "Open-ended" then Run Schedule. View it then highlight all of the open-ended activities and "Copy" it (Ctrl + C).

4). Open a new excel file and Paste it (Ctrl + V) in Column A.

5). Highlight again all those open-ended and go to Data, then Filter, then click "Autofilter".

6). In the top most cell, you will see the drop down arrow and go to "Custom". Use "Contain" "No Predecessor". It will filtered all activities without predecessors. Copy it again (Ctrl + C) and paste it on sheet 2.

7). On worksheet 2, on the right side (Column B) of those activities without predecessors, use the formula "MID".

=MID(A2,11,10)

Copy this formula down to the last activities.

There you will see now the activity ID only. Copy it as a "Value" to remove the formula.

8). Beside the activity ID, type "NP" for No predecessor (on Column C).

9). For no Successor, do the same step 6 and 8. Do it in another worksheet (say Sheet 3) Except the MID formula would be now,

=MID(A2,52,10)

Then, type "NS" (Colum C) for No Successor.

9). Now, open the exported file "open.dbf" that you created inside P3 with Activity ID and Activity Codes. Note: Both files are now open.

10). Use function "VLOOKUP" to assign the proper code under "OPEN" at the exported file.

11). Once done, save "open.dbf" and close excel.

12). Inside P3, import the file "open.dbf".

13). You could now filter all open-ended activities.

With little practice, these process would be very helpful especially when you have hundreds or thousands of open-ended activities.

Goodluck!

Jaime
Top