+ Reply to Thread
Results 1 to 10 of 10

List the differences betweed two P6 dumps exported into Excel

  1. #1
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    807

    List the differences betweed two P6 dumps exported into Excel

    I have two tables which are imports of Primavera P6 schedules. I need to list all changes including where Activities have been deleted or added, where the Activity descriptions have changed, and where the Successors have changed, and ditto for the Predecessors. I can't get it to work using formulas so I suspect it needs array formulas.

    Any help is much appreciated. I have attached a workbook where I have sample data.
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: List the differences betweed two P6 dumps exported into Excel

    So far this is all I have been able to solve.

    Fields A1 and A2 are unique but data in A1 may not be in A2, and visa versa. I want to extract a list where there are differences
    In A1 not in A2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In A2 not in A1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The other parts have me stumped so far.

    Edit I am considering an idea that may require sorting Table 2 on Field B2. Is this acceptable?
    Last edited by FlameRetired; 02-13-2018 at 12:18 AM.
    Dave

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: List the differences betweed two P6 dumps exported into Excel

    1. Fields A1 and A2 are unique but data in A1 may not be in A2, and visa versa. I want to extract a list where there are differences
    2. Fields B1 and B2 are not unique. I want to extract a list of differences where Field A1 and Field A2 are the same but the data in Field B1 and B2 no long match.
    3. Fields C1 and C2 are not unique. I want to extract a list of differences where Field A1 and Field A2 are the same but the data in Field C1 and C2 no long match. Ignore where the data in C1 and C2 match but are in a different order.
    4. Fields D1 andDC2 are not unique. I want to extract a list of differences where Field A1 and Field A2 are the same but the data in Field D1 and D2 no long match. Ignore where the data in D1 and D2 match but are in a different order.

    Item 1 is covered above.
    Item 2 formula is
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Item 3 formula is
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Item 4 formula is
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    807

    Re: List the differences betweed two P6 dumps exported into Excel

    I am no longer worried about the fact I ccouldn't find the solution myself. You are using techniques I've never seen. Those formulae are so elegant, and not an Shift-Control-Enter amongst them. Once again, I offer profound thanks. There's a chair at SpaceX with your name on it.

  5. #5
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    807

    Re: List the differences betweed two P6 dumps exported into Excel

    FlameRetired

    YOu make refernce to ranges below the tables. I can't work out how they work, what part they play.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: List the differences betweed two P6 dumps exported into Excel

    I don't understand what references you refer to. Please give an example.

  7. #7
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    807

    Re: List the differences betweed two P6 dumps exported into Excel

    Solutions three and four references to areas where there is no data:

    =IFERROR(INDEX($K$6:$K$32,AGGREGATE(15,6,(ROW($A$6:$A$32)-MIN(ROW($A$6:$A$32))+1)/(ISNUMBER(MATCH($H$6:$H$32,$A$6:$A$27,0))*($K$6:$K$32<>"")*ISNA(MATCH($K$6:$K$32,$D$6:$D$27,0))),ROWS(E$40:E40))),"")

    The last segment references rows E$40:E40

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: List the differences betweed two P6 dumps exported into Excel

    The last segment references rows E$40:E40
    If you are familiar with SMALL/LARGE functions they require a k value indicating 1st smallest, 2nd smallest etc.

    AGGREGATE is like the Swiss Army Knife of functions. It has a menu of several sub-functions. AGGREGATE(15 operates the same as SMALL. It likewise requires a k argument. The k argument (rows E$40:E40) counts the number of rows. As you copy down it increments rows E$40:E40, rows E$40:E41, rows E$40:E42, rows E$40:E43 etc. returning 1, 2, 3, 4 etc.

    Did this help?

  9. #9
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    807

    Re: List the differences betweed two P6 dumps exported into Excel

    I think I've got it. Thanks again for your help. Invaluable.

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: List the differences betweed two P6 dumps exported into Excel

    You bet. Thanks for the feedback and marking your thread Solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel 2007 : List differences between tables
    By Yathish.d in forum Excel General
    Replies: 6
    Last Post: 07-29-2012, 12:19 AM
  2. Replies: 2
    Last Post: 01-27-2012, 02:19 AM
  3. Replies: 1
    Last Post: 06-10-2011, 06:32 AM
  4. Drop-Down list exported to html
    By michael.g in forum Excel General
    Replies: 2
    Last Post: 09-14-2010, 09:59 AM
  5. Replies: 0
    Last Post: 05-20-2009, 05:37 AM
  6. Excel CSV Dumps
    By Curryman54 in forum Excel General
    Replies: 3
    Last Post: 07-26-2008, 06:33 PM
  7. Copying formula betweed sheets
    By ricoandquesa in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 11-13-2007, 12:38 AM
  8. Replies: 11
    Last Post: 03-02-2005, 11:16 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1