Hi ,
I need to extract from an excel list the top 5 (oldest) lead times by milestone (MS1 -MS7). I need the five highest lead times per milestone. I've tried using INDEX and SMALL but after 4 hours need some help!
Hi ,
I need to extract from an excel list the top 5 (oldest) lead times by milestone (MS1 -MS7). I need the five highest lead times per milestone. I've tried using INDEX and SMALL but after 4 hours need some help!
is that what you want ?
done with PivotTable
or ver2
Last edited by sandy666; 06-06-2018 at 12:05 PM.
test response as keep getting server rejection errors
Thank you for the prompt response but looking for a formula or vba solution. I should have been clearer in my original ask.
I've use a formula to extract on given criteria but can't figure what to use for top x of a given criteria
Hi sandy666, thank you for the prompt response but looking for a formula/vba solution. I should have been clearer in my original ask.
I've used below to extract from a dataset with criteria but can't figure how to only select the top 5 in value
I've tried posting a formula but keep getting a site error message (below) and don't know why
Your request was not authorized due to its content (HTML code not allowed).
Hi Sandy666,
I tried replicating your version 2 pivot table and can't apply the value filter to the job id column - any suggestions?
but this is not formula or vba
ok, maybe it will help
top5.jpg
first add fields then choose filter
Hi,
sometimes you need to just get quick and dirty with the solution.
I've tried this approach but there's a couple issues I've come across. The main one is it doesn't return my five oldest orders based on lead time, it just returns the five highest unique lead times so if I have five orders with a lead time of 9999 it will only return the first one.
The second is if there's less than five the pivot only returns what' s there (i.e. 2 orders). Is there a way to force the pivot to return blanks so all five rows are populated with some data?
Best Regards
any example Excel file with these problems?
here's a better description of the ask
I have a spreadsheet where I want to identify the top five highest lead time (oldest) orders by milestone (MS1 -MS7). I could use the TOP X function in pivot table but it doesn't give you the unique orders. i.e. if you have 5 orders with the same lead time it won't return the five unique order numbers, which to be fair isn't what your asking it to do.
It always sounds simpler than it actually is but I just need to extract from my spreadsheet the five oldest lead times in column (DL) based on the Current Milestone (DK)
Any help greatly appreciated
What date decides the oldest lead time...And I suggest a sample file with more criteria to work with...Not just 2 MS and 2 lead time
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!!!
Its just a calculation of Today() minus the last milestone claimed date; so for MS2 it would be the closure date of MS1 minus todays date.
And....Drumroll....closure date is in Column???
Hi, here's some extra data. closure dates are in columns; AG,AT,BI,BT,CO,CX,DG
This date set aside...Is this close to your requirement..
Please Login or Register to view this content.
Hi,
The outputs to my list tab is exactly what I want but need some help understanding how I do the same for all 7 milestones (MS1 - MS7) and how I select the five highest lead times to place in the boxes. The second file is the actual live data and contains all the milestones (Column DK) with their respective lead times (DN)
Still not sure what the dates have got to do with it, but this extracts the 5 highest to lowest lead times...
Sintek, Superb mate.
I'll know need a few long hours trying to understand how it works. Is there a way I can bump some positive feedback your way?
Hi Sintek, I thought we had it but I need the JobID from column A retuned in the table as well as the lead times. Idea is to quickly identify the oldest orders by milestone if that makes sense?
In a different column?as well as the lead times.
Ideally in a different column; Job ID, Lead Time.
Not to familiar with extracting unique values within a multicolumn array....Perhaps someone else can assist...
Best I can do sorry...
Please Login or Register to view this content.
Hi Sintek,
it's a superb piece of code (it made me feel like a caveman seeing fire for the first time) and I can live with it picking up the first order if the lead times are the same. If anyone's able to crack this particular nut that would be appreciated.
I have identified a problem at MS3 & MS7 where the code is picking up the right lead times but the incorrect milestones, all three are MS4 but don't know if this just coincidence? added sample to attached spreadsheet.
Is it possible to arrange the output as per the attached spreadsheet and how can I add a filter to pick up the lead times by a customer which in this spreadsheet is column E. i.e. out put the same list but by a customer.
Best Regards
If there are duplicates...Which one must be taken...first or second?
Look at the results on attached...You will see there are duplicates...Will amend as soon as your answer above question...
Please Login or Register to view this content.
Last edited by sintek; 06-12-2018 at 08:29 AM.
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.
This solves...
Please Login or Register to view this content.
Superb mate. Thank you. If there's any way way to introduce some filtering into the code that would be appreciated. Basically want to be bale to run report based on ShareName (E) and/or Status(O)
Again many thanks
Think it is time to open a new thread for that new requirement...Basically want to be bale to run report based on ShareName (E) and/or Status(O)
If this one is resolved, please mark as solved...Tx for rep +
have done mate.
if you fancy a look;
https://www.excelforum.com/excel-pro...ml#post4916547
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks