+ Reply to Thread
Results 1 to 39 of 39

Extract orders by longest lead times based on criteria

  1. #1
    Forum Contributor
    Join Date
    11-30-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    112

    Extract orders by longest lead times based on criteria

    Hi,

    I would like to develop the attached code to filter on 'ShareName' column E and/or 'Status' column O.

    Also, the present code can't distinguish duplicated lead times so only reports on the first order so if there's two different orders with the same lead time it just reports the first.

    example

    order1 999 days
    order2 999 days
    order3 100 days
    order4 99 days
    order5 98 days
    order6 97 days
    order7 97 days

    the code will only return orders 1,3,4,5,6 when it ideally should return orders 1 - 5
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,257

    Re: Extract orders by longest lead times based on criteria

    Hi HKPhooy,

    See if a small modification to your data structure and then a Pivot Table showing Max is what you want. See the attached.

    Pivot Table showing Max.xlsm
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    11-30-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Extract orders by longest lead times based on criteria

    Hi, the tab 'OldestOrders' is the output sheet.

    What I need is the five highest unique lead times (column DN) in each milestone MS1 -MS7 (column DK) which is working apart from the duplication issue if there's the same lead time. The big ask here is to filter that output based on the order status (O) and ShareNAme (E)

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,154

    Re: Extract orders by longest lead times based on criteria

    present code can't distinguish duplicated lead times so only reports on the first order so if there's two different orders with the same lead time it just reports the first.
    That was your requirement...What do you actually want it to report...Both?
    Hi, if there's duplicate lead times I would prefer to take the first. basically the lowest order number as this would have been the older order.
    https://www.excelforum.com/excel-for...milestone.html
    the code will only return orders 1,3,4,5,6 when it ideally should return orders 1 - 5
    This change in the code will give you the result as per above quoted requirement
    Please Login or Register  to view this content.


    Please explain in more detail what you mean by....
    The big ask here is to filter that output based on the order status (O) and ShareNAme (E)
    Last edited by sintek; 06-12-2018 at 02:41 PM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere.....
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember......Mark Thread as Solved.
    Excel Forum Rocks!!!

  5. #5
    Forum Contributor
    Join Date
    11-30-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Extract orders by longest lead times based on criteria

    Hi Sintek,

    I thought this was still a problem?

    "Not to familiar with extracting unique values within a multicolumn array....Perhaps someone else can assist...
    Best I can do sorry..."

  6. #6
    Forum Contributor
    Join Date
    11-30-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Extract orders by longest lead times based on criteria

    Please explain in more detail what you mean by....

    The big ask here is to filter that output based on the order status (O) and ShareNAme (E)

    basically need to add something to the code or maybe a drop down boxes on the sheet with the values in columns O and E so that I can extract the oldest orders by customer and also only open (column O) orders

  7. #7
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,154

    Re: Extract orders by longest lead times based on criteria

    No, the last code I assumed solved your problem...The adapted code in post 4 solves this
    order1 999 days
    order2 999 days
    order3 100 days
    order4 99 days
    order5 98 days
    order6 97 days
    order7 97 days

    the code will only return orders 1,3,4,5,6 when it ideally should return orders 1 - 5
    So...it will give duplicates as well


    Edit...Still don't understand...
    basically need to add something to the code or maybe a drop down boxes on the sheet with the values in columns O and E so that I can extract the oldest orders by customer and also only open (column O) orders

  8. #8
    Forum Contributor
    Join Date
    11-30-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Extract orders by longest lead times based on criteria

    so SharersName (column E) report on data only if 'Someone1' and Status (O) is 'Open' etc.

  9. #9
    Forum Contributor
    Join Date
    11-30-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Extract orders by longest lead times based on criteria

    be good to pick up from some drop down on the report rather than having to edit the code each time but hey beggars can't be choosers

  10. #10
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,154

    Re: Extract orders by longest lead times based on criteria

    Perhaps you should start from scratch and explain in detail what you require...Maybe even upload a sample file with a mock up result so that we can see what your requirement is...
    My code filters MS numbers 1-7 ...finds the top 5 and populates the output sheet.sheet accordingly...
    Must there be more criteria added to this before populating the output sheet.

    If my code in both these threads was not solving your solution, why did you not say that?

    Edit...Do you only want to extract For "Someone1" and if status is open?????????????
    Last edited by sintek; 06-12-2018 at 03:02 PM.

  11. #11
    Forum Contributor
    Join Date
    11-30-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Extract orders by longest lead times based on criteria

    It solved the original ask perfectly. . . just wondered if we could break down the top 5 oldest orders by Sharer . . . I'll mock up a view

  12. #12
    Forum Contributor
    Join Date
    11-30-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Extract orders by longest lead times based on criteria

    Hi, here's a mock up. Sorry if adding confusion.
    Attached Files Attached Files

  13. #13
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,154

    Re: Extract orders by longest lead times based on criteria

    This adapted code will extract the lead times based on Sheets("OldestOrders") ranges [G1] & [G3] criteria
    For now only works with Company A or B and open or suspended...
    your dropdown selection of all is not featured and it will error...
    It will copy top 5 even if there are duplicates....
    Please Login or Register  to view this content.
    Last edited by sintek; 06-12-2018 at 04:00 PM.

  14. #14
    Forum Contributor
    Join Date
    11-30-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Extract orders by longest lead times based on criteria

    Hi, the 'All' criteria isn't in the dataset. I just wondered if there's a way to select 'All' and it returns everything? i.e. excludes filters

  15. #15
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,154

    Re: Extract orders by longest lead times based on criteria

    Here you go...If All is selected then it will not filter on Column E And O
    Please Login or Register  to view this content.

  16. #16
    Forum Contributor
    Join Date
    11-30-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Extract orders by longest lead times based on criteria

    Hi Sintek, I had a go at amending the code to accommodate a layout change on the form and the introduction of 'All' on the status drop down but keep getting some run errors. I've been trying for over an hour but just keep getting deeper in the mire - any chance you take a look?
    Attached Files Attached Files

  17. #17
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,154

    Re: Extract orders by longest lead times based on criteria

    Why did you change the 2 to 11...It must be 2

    Please Login or Register  to view this content.

  18. #18
    Forum Contributor
    Join Date
    11-30-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Extract orders by longest lead times based on criteria

    I assumed that as I had moved the output table down to row 11 I needed to change it . . .

  19. #19
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,154

    Re: Extract orders by longest lead times based on criteria

    That uRng refers sheets("WIP")

  20. #20
    Forum Contributor
    Join Date
    11-30-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Extract orders by longest lead times based on criteria

    ok, got you. just trying to learn as I go along mate, maybe smaller steps needed.

  21. #21
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,154

    Re: Extract orders by longest lead times based on criteria

    Also just realized that if there is no data for a specific MS then code errors...this will fix
    Please Login or Register  to view this content.

  22. #22
    Forum Contributor
    Join Date
    11-30-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Extract orders by longest lead times based on criteria

    getting a run time error on this line when I run it :.Range("DK1:DK" & lrow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.Range("DK1:DK" & lrow), Unique:=True


    Is this because the auto filters been applied and not reset when code ran for a second time?

  23. #23
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,154

    Re: Extract orders by longest lead times based on criteria

    The code resets filters unless you were stepping through and not completed code...
    upload sample you working on that gave that error so that i can see

  24. #24
    Forum Contributor
    Join Date
    11-30-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Extract orders by longest lead times based on criteria

    here you go
    Attached Files Attached Files

  25. #25
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,154

    Re: Extract orders by longest lead times based on criteria

    My bad...
    Please Login or Register  to view this content.

  26. #26
    Forum Contributor
    Join Date
    11-30-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Extract orders by longest lead times based on criteria

    but you're also imparting wisdom as wouldn't have understood that was an issue yesterday

  27. #27
    Forum Contributor
    Join Date
    11-30-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Extract orders by longest lead times based on criteria

    HI, that re-sets and works but the filters don't appear to be working. It's ok with Project 'All' and Status 'All' but any other combination is bringing up the wrong records.
    Attached Files Attached Files

  28. #28
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,154

    Re: Extract orders by longest lead times based on criteria

    why have you hidden all those columns....
    This works...

  29. #29
    Forum Contributor
    Join Date
    11-30-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Extract orders by longest lead times based on criteria

    hi, hid them to check results. I'll check again as got the same results when I selected Project = All/status = All and Project = SiteShare/Status = All

  30. #30
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,154

    Re: Extract orders by longest lead times based on criteria

    Think you are confusing me with your selections...
    If All is selected then it does not filter columns E and O
    Anything else other than All then it does filter those columns...The code is not set up for All & any other selection or other selection and All

  31. #31
    Forum Contributor
    Join Date
    11-30-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Extract orders by longest lead times based on criteria

    ah sounds like I have confused you mate . . . ideally wanted it to do the following
    The code is not set up for All & any other selection or other selection and All

  32. #32
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,154

    Re: Extract orders by longest lead times based on criteria

    Yes I think you have...

    Do you want...
    When Selecting Project All and status Open it must populate MS1-7 with only those that are open etc etc
    When selecting status all does that mean open and suspended etc etc

  33. #33
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,154

    Re: Extract orders by longest lead times based on criteria

    See if this is what you mean...
    Please Login or Register  to view this content.

  34. #34
    Forum Contributor
    Join Date
    11-30-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Extract orders by longest lead times based on criteria

    It's almost there mate. The only problem is when there's no data for a specific milestone it just leaves the last values. For example if you select Project = All and Status = Suspended it still reports on MS3,MS5 and MS7 even though there's no records in the dataset

  35. #35
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,154

    Re: Extract orders by longest lead times based on criteria

    Put this line...
    Please Login or Register  to view this content.
    before...
    Please Login or Register  to view this content.

  36. #36
    Forum Contributor
    Join Date
    11-30-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Extract orders by longest lead times based on criteria

    Hi Sintek,

    I made the changes suggested but keep getting this runtime error
    Run-time error '91':
    Object variable or With block variable not set
    and the code stops at this line
    Please Login or Register  to view this content.
    Attached Files Attached Files

  37. #37
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,154

    Re: Extract orders by longest lead times based on criteria

    I see in row 150 Col DK there is a value COMP....
    That why errors....
    Change the code to include if ...Will overlook that error
    Please Login or Register  to view this content.

  38. #38
    Forum Contributor
    Join Date
    11-30-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Extract orders by longest lead times based on criteria

    I didn't notice the milestone 'COMP' in the dataset. If I added 'COMP' to the bottom of the 'OldestOrders' list would it pick them up as well?

  39. #39
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,154

    Re: Extract orders by longest lead times based on criteria

    Not with current code unless It is also Comp1 Comp2 etc
    If not then Will have to put if statement with difference ... such as eg only....
    Please Login or Register  to view this content.
    Will only know when seeing setup

+ 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] Return cell with longest character limit based on additional criteria
    By SAsplin in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-12-2018, 09:06 AM
  2. [SOLVED] Supplier Lead Times
    By spamspamspam in forum Excel General
    Replies: 11
    Last Post: 10-17-2017, 08:34 AM
  3. Create printable daily work orders based on criteria within a column.
    By myDjinni in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-27-2016, 07:20 PM
  4. Replies: 1
    Last Post: 10-30-2012, 02:05 AM
  5. [SOLVED] working out lead times with 2 dates
    By NinjaBear in forum Excel General
    Replies: 2
    Last Post: 07-02-2012, 05:04 AM
  6. Taking into account lead times ...
    By Turvy86 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-30-2009, 02:25 PM
  7. Vlookup longest three times
    By Dave69rock in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-05-2008, 11:20 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