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
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.
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)
That was your requirement...What do you actually want it to report...Both?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.
https://www.excelforum.com/excel-for...milestone.htmlHi, 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 change in the code will give you the result as per above quoted requirementthe code will only return orders 1,3,4,5,6 when it ideally should return orders 1 - 5
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!!!
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..."
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
No, the last code I assumed solved your problem...The adapted code in post 4 solves this
So...it will give duplicates as wellorder1 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
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
so SharersName (column E) report on data only if 'Someone1' and Status (O) is 'Open' etc.
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
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.
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
Hi, here's a mock up. Sorry if adding confusion.
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.
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
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.
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?
Why did you change the 2 to 11...It must be 2
Please Login or Register to view this content.
I assumed that as I had moved the output table down to row 11 I needed to change it . . .
That uRng refers sheets("WIP")
ok, got you. just trying to learn as I go along mate, maybe smaller steps needed.
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.
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?
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
here you go
My bad...
Please Login or Register to view this content.
but you're also imparting wisdom as wouldn't have understood that was an issue yesterday
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.
why have you hidden all those columns....
This works...
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
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
ah sounds like I have confused you mate . . . ideally wanted it to do the followingThe code is not set up for All & any other selection or other selection and All
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
See if this is what you mean...
Please Login or Register to view this content.
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
Put this line...
before...Please Login or Register to view this content.
Please Login or Register to view this content.
Hi Sintek,
I made the changes suggested but keep getting this runtime errorand the code stops at this lineRun-time error '91':
Object variable or With block variable not setPlease Login or Register to view this content.
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.
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?
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....
Will only know when seeing setupPlease Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks