Results 1 to 8 of 8

Thread: Function to identify 'Path' of Superseded Items

  1. #1
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    80
    Articles
    0
    Excel Version
    2016

    Function to identify 'Path' of Superseded Items



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,

    Please refer tables in attachment

    I have a Table named "Changed" 'Blue' which details part number and the part number they've changed to. Sometimes there can only be one occurrence of a part changing, but on occasion that part its changed to also changes!

    What im after from this is a method to identify by part what the latest possible part number it has changed to, how many times has it been through a change and the string of those change details (orange table example)

    Im feeling its a List.Accumulate...but that beyond me .....maybe its easier than it looks??

    Thanks
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Capture.jpg 
Views:	8 
Size:	98.0 KB 
ID:	10638  
    Attached Files Attached Files

  2. #2
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    80
    Articles
    0
    Excel Version
    2016
    Anyone there????

  3. #3
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,712
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    Well, you've had over 100 views of your thread, so I guess so ...

    Let me have a look and see if I can offer any wisdom.

    EDIT: No - beyond me, sorry. Not even sure I understand what you are trying to do. Maybe you need to provide a more detailed explanation?
    Ali
    Enthusiastic self-taught user of MS Excel!

  4. #4
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    137
    Articles
    0
    Excel Version
    Office 365
    You can have any length of change chain that needs to be investigated to get a complete history. I have no idea how this could be done with Power Query.

    If I needed it for myself, I would try to create the evaluation with a VBA macro, even though I don't have much experience in VBA.

  5. #5
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    80
    Articles
    0
    Excel Version
    2016
    Im sure it can be done, HorseyRide developed a fantastic piece of M utilizing the List. Accumulate function to create a Multi Level BOM Path for me.....I don't think he lives on the forum anymore though. Shame

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,000
    Articles
    0
    Excel Version
    365
    I tend to agree with pinarello, although Power Query could very probably do it with some of its iterative/recursive functions I haven't got that far with PQ so far. In the attached is a vba solution. It uses a bit of recursion. It's not neat and tidy, I just kept experimenting until it worked. Click the button at cell H17 to fill in data from cell K17. From K31 there's another table to compare the results with your expected results. Nearly all are TRUE meaning an exact match. Where they're different is where I think you've got it wrong (but put me right if I'm wrong!)

    Important: Note that this could go into everlasting iterations if your data is not good, in that there should only be unique part numbers in Part New and Part Old columns (a single occurrence of each) and there is no attention at all paid to dates being in the correct order. I realise that people assigning new part nos. might actually issue old and no longer used part numbers. I haven't coped with that - it would involve a fair bit more coding!
    Attached Files Attached Files
    Last edited by p45cal; 2021-09-21 at 09:08 PM.

  7. #7
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    80
    Articles
    0
    Excel Version
    2016
    Thanks for looking at this P45cal, i'll have a play and see how I get on. Similar to you I've not managed to crack the really special functions in PQ yet...I need too though

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,000
    Articles
    0
    Excel Version
    365
    I forgot to delete columns G & H, they're not needed at all.

Posting Permissions

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