I need to list on a report thre top five lead times by milestone

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!

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).

Originally Posted by HKPHOOY

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

No problem, have a nice day.

btw. post excel workbook with your code/formula (desensitize data before post!)

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

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.

Hi, here's some extra data. closure dates are in columns; AG,AT,BI,BT,CO,CX,DG

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)

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?

Ideally in a different column; Job ID, Lead Time.

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

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.

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

have done mate.


if you fancy a look;

https://www.excelforum.com/excel-pro...ml#post4916547