+ Reply to Thread
Results 1 to 118 of 118

Filtering data based on few criteria such as month, style, item etc

  1. #1
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Question Filtering data based on few criteria such as month, style, item etc

    Dear Experts,

    I have attached here an excel work sheet for your kind reference and advice. This is a planning work sheet. In this, I need the following options to work for easy planning.

    >> As per the criteria in column B,C,D and E, the values should be calculated based on the month and returned to the cells starting from F35 to Q35 (Jan, Feb, March, etc.) and across down to row 41.

    Hope your kind help in this regard.

    Thanks in advance.

    Regards
    Anuruddha

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Filtering data based on few criteria such as month, style, item etc

    Change your formula in Q6 etc to

    =IF(P$5<$M6,0,IF(P$5=$M6,MIN($K6,$O6),IFERROR(IF(MATCH(P$5,Holidays!$B$2:$B$16,0),0),IF(SUM(O6:$P6)+$O6>$K6,MAX($K6-SUM(O6:$P6),0),$O6))))

    Then use this in F35:

    =SUMPRODUCT(($B$6:$B$26=$B35)*($C$6:$C$26=$C35)*($D$6:$D$26=$D35)*($E$6:$E$26=$E35)*(MONTH($Q$5:$Z$5)=MONTH(F$34))*(YEAR($Q$5:$Z$5)=YEAR(F$34))*$R$6:$AA$26)
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Dear Glenn,

    Thanks for the solution as always. However, I just got two issues.,

    01. In the document I have sent to you, when I put the "start date" in column M6 etc, the values start on the same date starting from column Q, R,S etc. However, in the document you sent with the solution, when I put the date in Column M6, the values in the columns from Q,R,S etc start "one day after" the date in column M6.

    02. In the monthly summary table, when I select the criteria as in the attached document, the value appears as 17000. However, it should be 26200. And also, when the mouse is dragged over the values, in the excel status bar it shows as 27900.

    03. Thirdly, the dates highlighted in red, should not include any values as they are considered as holidays as per the production calendar. May be due to the issue pointed in the 1st above, these columns also get values in them now. However, in the original sheet I sent to you they are working fine.

    Regards
    Anuruddha

  4. #4
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Cool Re: Filtering data based on few criteria such as month, style, item etc

    Dear Glenn,

    Kindly ignore the previous post and refer to this with the attachment agian.

    Thanks for the solution as always. However, I just got two issues.,

    01. In the document I have sent to you, when I put the "start date" in column M6 etc, the values start on the same date starting from column Q, R,S etc. However, in the document you sent with the solution, when I put the date in Column M6, the values in the columns from Q,R,S etc start "one day after" the date in column M6.

    02. In the monthly summary table, when I select the criteria as in the attached document, the value appears as 15300. However, it should be 26200. And also, when the mouse is dragged over the values, in the excel status bar it shows as 27900.

    03. Thirdly, the dates highlighted in red, should not include any values as they are considered as holidays as per the production calendar. May be due to the issue pointed in the 1st above, these columns also get values in them now. However, in the original sheet I sent to you they are working fine.

    Regards
    Anuruddha

  5. #5
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Dear Glenn,

    One more thing I need to add.

    Will the formulas work independently on each criteria. For example, if I select only "Customer", does the formula return the total values for the customer selected in the summary, ignoring other criteria such as Item, Style etc?

    Regards
    Anuruddha

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Filtering data based on few criteria such as month, style, item etc

    OK. I'm not 100% certain what was wrong, but I think it's all OK now. I also had to change the months in F34 annd across, too. check this one over.
    Attached Files Attached Files

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Filtering data based on few criteria such as month, style, item etc

    I didn't see Post #5. No. It's looking for matches all the way across.

  8. #8
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Dear Glenn,

    The issues I have mentioned all seem to be working fine now. However, I need the criteria in column B,C,D and E to work independently and return the values to particular months ignoring other criteria which have not been selected. For example, if I selected only Customer, all the quantities/values/figures belonging to that particular customer should appear in the month column ignoring "item, style, Unit etc.

    Please kindly look in this again.

    Regards
    Anuruddha

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    Try

    in F35

    =IF($B35="","",SUMPRODUCT(($B$6:$B$26=$B35)*(ISNUMBER(SEARCH($C35,$C$6:$C$26)))*(ISNUMBER(SEARCH($D35,$D$6:$D$26)))*(ISNUMBER(SEARCH($E35,$E$6:$E$26)))*(MONTH($Q$5:$LJ$5)=MONTH(F$34))*(YEAR($Q$5:$LJ$5)=YEAR(F$34))*$Q$6:$LJ$26))

    I left "Customer" selection As-is: if you want that to be all customers change test to "ISNUMBER (......)

    Add "*" without the quotes to the DV lists to select "all" of a category.
    Attached Files Attached Files
    Last edited by JohnTopley; 04-09-2017 at 09:17 AM.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Filtering data based on few criteria such as month, style, item etc

    You should have asked for that at Post 1.

    Try it now. It's now an array formula. Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Dear Glenn,

    As always, your formulas are working brilliantly. There is one thing I noticed that, in the summary table, unless the "customer" is not selected, other options such as "style, item, factory don't show up any values. Is there any specific reason or can it be corrected to work independent of other criteria?

    Regards
    Anuruddha

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Filtering data based on few criteria such as month, style, item etc

    It was the error trap. Now as long as SOMETHING is selected, it populates fully.
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Wow, now it is working extremely beautifully. Thanks a lot Glenn. Your kindness is always beyond expectation. May the happiness and fortune be with you all the time.

    By the way, if you have a website, I would like to subscribe. Pls let me know.

    Regards
    Anuruddha

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Filtering data based on few criteria such as month, style, item etc

    Great! Glad it worked. I don't have a website. i just spend a bit of time here, especially when the weather isn't too nice. Having said that, it feels like we have two seasons here. A cold rainy season and a cool rainy season. No monsoons, though...

  15. #15
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Nice to hear a bit about you Glenn. I always love excel spread sheets and your help in developing these work sheets are supporting me a lot in doing my day today work.

    Have a nice time.

  16. #16
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Lightbulb Re: Filtering data based on few criteria such as month, style, item etc

    Dear Glenn,

    I am not sure whether I can post a query on thread that has already been marked "SOLVED". However, I faced a problem in the array formula in the attached excel sheet, which you have done.

    I extended the formula in the summary table to cover the last month in the data entry sheet above, and press "Ctrl+shift+enter" to confirm the formula. However, it shows an error. Kindly advise me how to get this corrected.

    Regards
    Anuruddha
    Attached Files Attached Files

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Filtering data based on few criteria such as month, style, item etc

    You had forgotten to change:

    *(YEAR($Q$5:$LJ$5)=YEAR(F$34))* to *(YEAR($Q$5:$MT$5)=YEAR(F$34))*
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Dear Glenn,

    Thanks for explaining the issue. You are always very kind and helpful. However, if you have time, I would like to know the "theory" behind this formula. Because, if I have to extend it again, I will be able to do it myself.

    Regards
    Anuruddha

  19. #19
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Arrow Re: Filtering data based on few criteria such as month, style, item etc

    Dear Glenn,

    Hope you would not take me wrong if I post you this on this "solved" thread.

    I have attached here the same work sheet you have helped me to develop.

    In this I need the following thing to work out;

    01. I need the date to be appeared "automatically" in column "O", as per the "last day of production" that appear in row 6, 8, 10, etc(the daily production columns).
    For example, The "crew" style's last production date is May 31st. Then this date should appear automatically in cell "O6".

    Kindly see whether this is possible.

    Thanks in advance.
    Attached Files Attached Files

  20. #20
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Filtering data based on few criteria such as month, style, item etc

    In O6, copied down: use this array formula:

    =IFERROR(IF(N6="","",INDEX($R$5:$MU$5,MAX((R6:MU6>0)*MATCH(COLUMN(R6:MU6),COLUMN(R6:MU6))))),"")

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...

  21. #21
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Wow..It is working.

    Amazing work Glenn as usual. I don't know how to express my gratitude to you. You are making my life easier. Wish you all the good luck in every moment of your life.

    Regards
    Anuruddha

  22. #22
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Filtering data based on few criteria such as month, style, item etc

    You're welcome.

    It might look a little complicated, but it had to be able to cope with the possibility of the last days production run run being the same as any of the previous days.




    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  23. #23
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Dear Glenn,

    Yes, the solution is 100% what I expected. Thanks for your brilliant work.

    Regards
    Anuruddha

  24. #24
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Wink Re: Filtering data based on few criteria such as month, style, item etc

    Dear Glenn,

    I have a need to "search" day/month/year etc, in the document without dragging by the mouse. I have included some search boxes in J2, K2 and L2( just to show the requirement, it could be the way you want also).

    When, a day/month etc is entered on these cells, that particular date should appear on right next to column Q. This is just my idea, and I don't like the filtering option in excel for this work sheet.

    Regards
    Anuruddha
    Attached Files Attached Files

  25. #25
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Filtering data based on few criteria such as month, style, item etc

    in T2:

    =DATE(L2,MONTH(DATEVALUE(J2&" 1")),K2)

    Glenn



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  26. #26
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Dear Glenn,
    I am not sure, I have missed anything. However, after I have copied the above formula in T2 and select the month, date, year etc in the search boxes, the expected result doesn't happen.

    Regards
    Anuruddha

  27. #27
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Dear Glenn,

    Actually, I think my question doesn't explain properly what I wanted.

    Let me explain again:
    When a certain date/month etc entered in the cells, J2,K2,L2 etc, that particular date should should retrieve the same date in the column R5. In other words, the table will start from the date that was entered in the "search field". This will help to look in to a particular month or date and, its production planning data, without moving the table/cells using the mouse from let to right.

    Regards
    Anuruddha

  28. #28
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    Please Login or Register  to view this content.
    Change date I2:K2

    NOTE: M2 has date in Excel format.

    And I changed the headings so month appears in every cell in row 4.
    Attached Files Attached Files
    Last edited by JohnTopley; 05-20-2017 at 07:03 AM.

  29. #29
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Dear Glenn,

    Thanks a lot for this piece of brilliant work.

    However, currently when the date is entered/selected, the selected date does not appear in the "column R", which is the starting column of the data table. Is there any possibility to make it so? Is there a possibility to show the "data from the selected date" starting from column R?
    Moreover, is there any possibility to "hide month" except at the 1st day of every month?

    Regards
    Anuruddha

  30. #30
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Dear Glenn,

    If you can make it to appear "1st day of every month" in column R, that will also do. Then, date can be sough just by using the mouse and moving the columns/cells. Then, selection criteria can be changed only based on "month and year".

    Regards
    Anuruddha

  31. #31
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    @Anuru: with all due respect to Glenn, the post came from myself.

    The reason I added the month to each row is quite simple: If you select a date other then the first of the month, there will nothing in the row which is scrolled to, to indicate the month.


    And how can you have the selected data appearing in column R: this will replace all the other dates with wrong values: If you select 25 June 2017, and put this date in R, then all other dates will be 26/27,28 June etc which will have no bearing on the actual results for those days.
    Last edited by JohnTopley; 05-20-2017 at 08:11 AM.

  32. #32
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    This will start at first of month based on Month/Year in I2:J2

    I am sure you can change the formula for adding the month if only on first of month....But why do this ???
    Attached Files Attached Files

  33. #33
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Filtering data based on few criteria such as month, style, item etc

    LoL. If there's VBA involved, I'm rarely to be seen....

  34. #34
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Quote Originally Posted by JohnTopley View Post
    Please Login or Register  to view this content.
    Change date I2:K2

    NOTE: M2 has date in Excel format.

    And I changed the headings so month appears in every cell in row 4.
    Dear Glenn,

    Now, the table starts as per the date selected in the search box. However, all the columns from E to Q disappear to accommodate for the selection. Can you make it so that E to Q columns remain(without hiding)as it is, even after the date selection??

    Regards
    Anuruddha

  35. #35
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Dear Jhon,

    Extremely sorry for addressing you wrongly thinking about Glenn. Actually, I was so concerned about the table, as too stupid to check to whom I am replying.

    Please kindly see the following:

    Now, the table starts as per the date selected in the search box. However, all the columns from E to Q disappear to accommodate for the selection. Can you make it so that E to Q columns remain as it is(without hiding), even after the date selection??

    Regards
    Anuruddha

  36. #36
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    Reset "Freeze Panes": select R2, Unfreeze then Freeze

    And minor change to VBA

    If Not Intersect(Target, Range("J2:K2")) Is Nothing Then
    Last edited by JohnTopley; 05-20-2017 at 11:35 AM.

  37. #37
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Dear John,
    Thanks for your hard work and brilliant piece of work.

    Regards
    Anuruddha

  38. #38
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Thumbs up Re: Filtering data based on few criteria such as month, style, item etc

    Dear Glenn,
    Hope you wouldn't mind asking you help for another requirement I need in the data sheet you have helped me to develop.
    Actually, the requirement arose due to need of "start up days" when planning production. Normally, when new style/product is fed/started in a production line, first few days production varies/become zero or come low depending on the total number of minutes required for producing one piece.

    Accordingly, I need to insert some "START UP DAYS" for a style in the first few days based on some criteria. During these start up days, the production qty of that particular day should be "zero".

    Criteria is is given in a table in the sheet "categories" (shaded in orange).

    I would explain this further; for example, in row 6, the daily production has started with 675 pcs per day. And, this style SMV(standard Minute Value-the time required for producing one piece) 20 minutes. According to my requirement, this style needs 2 start up days, in other words, 23rd and 24th should have "zero" production, and the production end date should be 31st June, instead of 29 May.

    Is there any possibility to include "start up" days in the existing formula without changing other criteria?

    Thanks in advance.
    Anuruddha.

  39. #39
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    I had look at this (in ignorance so I am probably wrong!):

    The "easiest" option (??) would be to look up the SMV and "Added" to the Start date" in R6: does that seem sensible?

    So in V5

    IF(V$5<$R6+VLOOKUP($J6,Categories!$J$2:$K$9,2,1),0,IF(V$5=$R6+VLOOKUP($J6,Categories!$J$2:$K$9,2,1),MIN($P6,$T6),IFERROR(IF(MATCH(V$5,Holidays!$B$2:$B$16,0),0),IF(WEEKDAY(U$5,2)=5,0,IF(SUM($U6:U6)+$T6>$P6,MAX($P6-SUM($U6:U6),0),$T6)))))

    Row 6 Start date is 18th May: moving it two days takes it 20th May which I believe is non-working day BUT the formula has production figure of 675.

    NOTE: I changed the SMV table layout.

    See row 6 of "Planning Calendar (2)"

  40. #40
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Dear John,

    Thanks a lot for your solution. However, I need to show "zero" values for the "start up days" to highlight that, those days are counted in the "planning". If no value is shown(here zero), it looks a bit incomplete.

    Kindly advise pls.

  41. #41
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    I'll will have to really think about this .... but probably leave this to Glenn who is far more able than me when it comes to complex formulae.

    I'll look tomorrow.

    EDIT: if required would it be possible to have an additional date ("Start Up") together with a "Production Start" date?
    Last edited by JohnTopley; 05-30-2017 at 03:40 PM.

  42. #42
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Cool Re: Filtering data based on few criteria such as month, style, item etc

    Hi John,

    If this cannot be done in the existing formula itself, we can insert column for Start up days in the work sheet(attached with the "start up column in K). However, the start up date in this column should be "auto calculated" based on the SMV value in J column. Further, this calculation should be done as per the criteria given in the table in "categories" sheet.

    Regards
    Anuruddha

  43. #43
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    See attached (row 6):

    in K2

    =$S6-VLOOKUP($J6,Categories!$L$2:$M$9,2,1)

    So "Start Up" is "Production Start" minus the "Start Up days"

    in W6

    =IF(W$5<$K6,0,IF(W$5<$S6,"9",IF(W$5=$S6,MIN($Q6,$U6),IFERROR(IF(MATCH(W$5,Holidays!$B$2:$B$16,0),0),IF(WEEKDAY(V$5,2)=5,0,IF(SUM($V6:V6)+$U6>$Q6,MAX($Q6-SUM($V6:V6),0),$U6))))))

    Here we an "problem" as the cells are formatted to "hide" zeros the ) for "Start Up" will be hidden: so for the purpose of testing I have used "9" to indicate a "Start up" day.

    I changed "Production Start" to 23 May: as I pointed previously, with an any earlier start date, you see a production figure in 20 may which i believe is incorrect (?)

  44. #44
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    Update:

    you see a production figure in 20 may which i believe is incorrect (?)
    ... need logic to allow for "Start up" occurring over a non-workday as per posted file.

    So production start of 21st May would have Start up days on 18/19th May.

    Need to get Glenn on the case!

  45. #45
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Exclamation Re: Filtering data based on few criteria such as month, style, item etc

    Dear John,
    One more problem, now the summary tables below(Production pcs and Production SAH) don't give the values when respective criteria are selected. For example; the Production pcs for May should be 3500 pcs, but it shows "0", and Production SAH for May should be 1173, but it shows "0". Kindly have a look at please.

    One more thing, instead of "9", you can use a small qty of production pcs( for exmplae; 10 pcs, 15 pcs etc) for the start up days if possible. And this small qty could be added into the main production calculation formula too.

    Regards
    Anuruddha

  46. #46
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    (Production pcs and Production SAH)
    where are these values:? the logic has not (as far as I know) has not changed the daily production values.

  47. #47
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Cool Re: Filtering data based on few criteria such as month, style, item etc

    Dear John,
    No need to worry about Zeros hidden issue, you can show zeros if it helps the formula/logic to work as expected. I can do some conditional formatting to hide zero if required. Kindly, work it out without worrying about zeros hidden issue, you can show zeros if necessary. I will handle it another way.

    I think, summary tables are based on arrays(as done by Glenn). There could be some complication. So, use numeric values as required. Start up days could have either zero values or some small qtys such as 10, 15 or 20 pcs. However, these values should be counted as production.

    Thanks.
    Anuruddha

    Thanks.

  48. #48
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    However, if you use zero values for start up days, kindly make the font to be "bold red". Because, then it will be easy to differentiate start up days against other zero values.

    thanks.

  49. #49
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    Problem with SUMMARY:

    The drop list in Column A is different in the "Data" vs "SUMMARY" (the entries in "Data" reflect an old version)

    You need to check these problems yourself especially as the SUMMARY formula was not changed: in these circumstances it is usually a data problem.

    Re colouring the zeros: you could use CF to select data with Dates starting "Start up Date" to Production Date"-1, with check that the two dates are NOT equal.

    CF rule for Start Up days

    =AND(W$5>=$K6,W$5<$S6,W6=0)

    Set font to RED

    To hide other zeros

    =W6=0

    Set Font to background colour
    Last edited by JohnTopley; 05-31-2017 at 06:31 AM.

  50. #50
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Dear John,
    Thanks for the reply. Summary table issue was corrected.
    However, there is one problem now; the new formula overlaps the last production days of the running style, when a new style starts. However this problem did not appear in the first solution you provided (showing empty cells for start up days: =IF(V$5<$R8+VLOOKUP($J8,Categories!$J$2:$K$9,2,1),0,IF(V$5=$R8+VLOOKUP($J8,Categories!$J$2:$K$9,2,1),MIN($P8,$T8),IFERROR(IF(MATCH(V$5,Holidays!$B$2:$B$16,0),0),IF(WEEKDAY(U$5,2)=5,0,IF(SUM($U8:U8)+$T8>$P8,MAX($P8-SUM($U8:U8),0),$T8))))).

    Moreover, when holidays occur in between a start up day, the formula calculates the holiday also as a start up day.


    Kindly have a look.

    Regards
    Anuruddha

  51. #51
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    I think, it is advisable to develop a formula to consider start up day values as numeric values which could be added to regular daily planned production.

  52. #52
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Question Re: Filtering data based on few criteria such as month, style, item etc

    Here attached the the sheet with the problems.

    Highligted in red. And, June 17 is a holiday, but production value appears there. However, this doesn't seem in other places.

  53. #53
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    The "empty cells" were zeros (hidden by formatting);

    the new formula overlaps the last production days of the running style, when a new style starts.
    No idea what this means!

    The holidays add another level of complexity which I don't know how to handle. As an example, I assume you mean ..

    Start up: 21st May for 2 days: so should be 21/22 May

    May 22nd is a holiday

    so start up finishes on May 23rd

    and Production Starts on 24th.

  54. #54
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Dear John,

    Yes, I meant exactly the same you have mentioned above;

    Start up: 21st May for 2 days: so should be 21/22 May

    May 22nd is a holiday

    so start up finishes on May 23rd

    and Production Starts on 24th.

    Also need to solve the "start up over lapping" with the last production days of the previous style.

    Thanks
    Anuruddha

  55. #55
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    The problem you highlight is the one I highlighted earlier: it is (I believe) inherent in the original but never happened because the Start of Production was never selected as a holiday.

    If you use the formula prior to any of the recent changes and select a holiday as start date, it will allocate production on that day..

  56. #56
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    Also need to solve the "start up over lapping" with the last production days of the previous style.
    As I said earlier ... what does this mean? An example please.

    I think you need to re-think your approach as this is becoming very complex (or too complex for me!).

  57. #57
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Okay. I understand. However, what about the "overlapping issue"?

  58. #58
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Dear John,
    I think this "holiday issue" occurs due to the formula in "column T". Because, I asked Glenn to make a formula so that it delivers the "last production day" of a style automatically in to "column T". The main idea was to avoid manual entry of "production start date" of the "next style".

    I think that, this formula in Col T, should be changed to "avoid" picking up a day(production start day) if that particular day is a holiday. If this is solved this "holiday issue" will be sorted. However, need to solve the "overlapping issue".

    It would be great if our excel formula super hero "'Glenn" would help us to over come this issue. I think he must be busy with his stuff.

    Regards
    Anuruddha

  59. #59
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Hi John,
    The formula in column T is delivered to column S by a simple formula. However, I think, the formula in column S should be given(extended) a "condition" not to pick up a holiday as a "production start day".

  60. #60
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Lightbulb Re: Filtering data based on few criteria such as month, style, item etc

    "Overlapping issue" means, the start up days of a new style goes inside the last production days of the previous style. For example, this production plan is for Line no 01 of Unit 01. Normally, in a month one line may have 3 or 4 styles depending on the quantity planned. Accordingly, the next style should not go in the last few days of the running style production.

    A new style is started when the running style is over. Moreover, the new style needs "some start up days" to catch up production. I have re attached the sheet with changing the whole plan to demonstrate "Line 01 of Unit 01".

    Regards
    Anuruddha

  61. #61
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    The second and subsequent starts i.e column S, no not consider any start up days.

    Perhaps it should be ....

    =T6+1+VLOOKUP($J8,Categories!$L$2:$M$9,2,1)

    so you add start up days which effectively means the Start Update becomes T6+1.

  62. #62
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Yes. This is correct. However, still it consider a holiday as a "start up day". How to avoid that issue? Can we add "holiday logic" to this formula??

  63. #63
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    I think this is the "holiday logic" in the formula in production calculation.

    (IF(MATCH(W$5,Holidays!$B$2:$B$16,0),0),IF(WEEKDAY(V$5,2)=5,0,

  64. #64
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    I don't know how this can be done: it is beyond my pay scale!

    Any day of the Start Up period could be a holiday, so (to me) it requires a count of Start Up days allocated.

    So 21/22 should be Start up days.

    22 is a holiday.

    So 21 is set to 0 (red)

    22 is set to 0 (hidden)

    how do we know 23 is now a start up day? We cannot update a cell from another cell.

    VBA maybe ??

  65. #65
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Dear John,
    In the table every Friday is a holiday, and other annual holidays are given in the sheet "holidays". I think that you can add this logic to the same formula you have already provided(=T6+1+VLOOKUP($J8,Categories!$L$2:$M$9,2,1)).

    This is the holiday logic formula Glenn used in the production planning calculation.
    (IF(MATCH(W$5,Holidays!$B$2:$B$16,0),0),IF(WEEKDAY(V$5,2)=5,0,

    I think that, if you find a way to join this logic to your one, this issue could be sorted out.

  66. #66
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    Let us say we arrive at a Production Start of Saturday (allowing for Start up Period)

    Let say Start Up period is 2 days so we would then say Start up would begin on Thursday (Production Start -2).

    But Friday is a holiday so 2nd Start up day would have to be Saturday with Production Start on Sunday.

    If Production Start was a Friday (holiday) then we would move it to Saturday (?): then we go through cycle above.

    And if we had Production Start date on an annual holiday on Thursday, .......

    I am not trying to be difficult: it is a fairly complex situation to deal with using formulae.

  67. #67
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    [QUOTE=JohnTopley;4666700]Let us say we arrive at a Production Start of Saturday (allowing for Start up Period)

    Let say Start Up period is 2 days so we would then say Start up would begin on Thursday (Production Start -2).

    But Friday is a holiday so 2nd Start up day would have to be Saturday with Production Start on Sunday.

    If Production Start was a Friday (holiday) then we would move it to Saturday (?): then we go through cycle above.

    You are right. The above procedure is right. Isn't there a way to add this logic to this (=T6+1+VLOOKUP($J8,Categories!$L$2:$M$9,2,1)) formula? In Glenn's formula((IF(MATCH(W$5,Holidays!$B$2:$B$16,0),0),IF(WEEKDAY(V$5,2)=5,0,) this has been considered.

    Regards
    Anuruddha

  68. #68
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    But Glenn's formula simply looks at dates day by day and is NOT changing any data: it simply sets a cell to 0.

    Brain is hurting so I'll leave this for now. Maybe Glenn or someone will have a look.

  69. #69
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    You need a moderator to change status of this thread from SOLVED: or start a new one.

    Contact moderator.

  70. #70
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Okay. Thanks a lot John for your hard work. I will wait for Glenn or some other experts.

    Thanks a lot again.

  71. #71
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    See my PM re attached file.

    Update:

    try (for "Start Up" days

    =IF(W$5<$K6,0,IF(W$5<$S6,0,IFERROR(IF(MATCH(W$5,Holidays!$B$2:$B$16,0),0),IF(WEEKDAY(V$5,2)=5,0,IF(W$5=$S6,MIN($Q6,$U6),IF(SUM($V6:V6)+$U6>$Q6,MAX($Q6-SUM($V6:V6),0),$U6))))))
    Last edited by JohnTopley; 06-01-2017 at 05:32 AM.

  72. #72
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc


  73. #73
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Smile Re: Filtering data based on few criteria such as month, style, item etc

    attached the sheet with the following formula applied.

    =IF(W$5<$K6,0,IF(W$5<$S6,0,IFERROR(IF(MATCH(W$5,Holidays!$B$2:$B$16,0),0),IF(WEEKDAY(V$5,2)=5,0,IF(W$5=$S6,MIN($Q6,$U6),IF(SUM($V6:V6)+$U6>$Q6,MAX($Q6-SUM($V6:V6),0),$U6))))))

    Thanks.

  74. #74
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    Last edited by JohnTopley; 06-01-2017 at 09:13 AM.

  75. #75
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Question Re: Filtering data based on few criteria such as month, style, item etc

    Dear John,

    Thanks a lot for your continuous efforts in developing this work sheet. I tested the formula changing the "Production start date" in col S. However, I found the formula again gives an error when I chose 27th May as the production starting date. You can see, a "start up date" has fallen on 3rd June which is a "weekly holiday". And, again you can see, a start up date has fallen on 16th Sep. which is also a weekly holiday.

    However, around 6, 7 other occasions formula worked as per expectation. I am confused why it happens so. I have again attached the work sheet as it is for your reference. We are almost on the solution, however, there could be a little logic missing still in the formula.

    Regards
    Anuruddha

  76. #76
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    if you change the "production start date" to 25th May(keeping the other details same), you can see the same error ccurrs on 26th Aug and 16th Sep.

  77. #77
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Hi John,
    I have found another mistake, probably due to my negligence. The weekly holiday is Friday, and there was an error in weekly holiday formula. In the previous work sheet you can see that weekly holiday has fallen on Saturday.

    I have corrected this and attached the work sheet again.

    thanks.

  78. #78
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    For this the situation I have no answer as it effectively it means looking at the RANGE of the Start Up days, checking if any are holidays , and somehow changing the Startup Up Date(s) dynamically.

    And is worse if you hit a 4 day holiday sequence!

    So I admit defeat.
    Last edited by JohnTopley; 06-01-2017 at 11:08 AM.

  79. #79
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    I think that we should target logic on "Production start" (column S formula). Why I say so, this formula (=T6+1+VLOOKUP($J8,Categories!$L$2:$M$9,2,1) is already working as per our requirement. Only thing, we have to add is the weekly and festival holiday logic to this.

    For example, when the formula finds one weekly holiday as a start up day, it should add one more date to the start up, and if the formula finds two or more holidays, it should add the same number of days to the start up value (these days will be hidden within the holidays). Festival holidays are given in the "holidays" sheet.

    If you try this way, I think it will work.

  80. #80
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Since, the added days are compensated for holiday period(weekly or annual/festival), we will finally get the start up value as we require.

  81. #81
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    And finally, we can do apply a conditional formatting to hide the "red zeros" that show up in weekly or annual holidays, and then only the actual start up days will be shown as required.

  82. #82
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    That is problem: you cannot add data to another cell: so A1 cannot add data to B1.

    You cannot "iterate" in this way with a formula: I had worked out (the logic of) what you describe.

    Think of this situation:

    Start up period is 5 days: first day of Start Up is a Thursday, the next 4 days are holidays (Friday,Saturday,Sunday, Monday) so day 2 of Start Up period is Tuesday, 3 rd Day is Wednesday, 4th day is Thursday, Oh Dear 5th Day is Friday (holiday), so 5th Day is now a Saturday.

    The above scenarios is possible: look at Period 24 to 27th June (4 consecutive holidays).

  83. #83
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Yes, this is possible.
    One more idea; if we list out all the weekly holidays and annual/festival holidays in another work sheet(as a table or so), can you set up look formula based on that?

  84. #84
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    Looking up holidays per se is not the issue: the issue is the formula logic to keep track of which "Start Up" days have been "allocated" and of the remainder, which (new) dates they are. Basically, the "start Up" dates are dynamic.

    The logic as we move across the calendar:

    Is this date in the "Start Up" range ?

    If Yes, is it a holiday? If Yes, increment the date of final Day of the "Start up" by 1 Repeat as we move along calendar

    The issue is that you cannot alter another cell via formula - only the cell in which the formula resides.

    Let us say we kept the initial count of "Start Up" days in L6 (say 3).

    If AE6 (or any cell) is a Holiday and in Start Up period we cannot via a formula add 1 to L6: you need VBA to do this.

    Now "brain dead" !! so will sign off on this.

  85. #85
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Dear John,

    Thanks a lot for taking so much efforts to work this out. Any way, if you happen to come out of something new which can handle this situation, please let me know.

    Thanks a lot again. God bless you.

  86. #86
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    Thank you the feedback. I am sorry we haven't reached a satisfactory outcome: maybe I'll have a "eureka" moment!

    I am "out of the office" all tomorrow so I may look again over the weekend.

  87. #87
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Ok John, thanks again.

  88. #88
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Exclamation Re: Filtering data based on few criteria such as month, style, item etc

    Dear John,
    I have attached here the most recent work sheet which I have developed adding some tweaks.

    Column, I, J, M are having some new information. However, only M(Style Type) affects the Start up days. The idea of this is; when I select RPT(REPEAT style- a style which had been done before), the number of start up days "reduced by 1 day". If I select NEW, it doesn't change anything. Look up criteria for this has been given under "Style Nature" table in categories sheet. I added this RPT criteria formula to "production start col"(T), and it is working as I expected.

    However, still didn't find a solution for issue which we were trying before.

    Regards

  89. #89
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    VBA version: event invoked when T6 changed.

    I changed the columns for some of date to keep the "date" info together.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  90. #90
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    John, this is working brilliantly. By the way, can I copy this code to the new work sheet (I have added some tweaks yesterday, new format is a little different)? Also Kindly explain me the following pls.

    01. When should I run the 'Run" command? after every entry? or before?
    02. Do I need to operate "Run" command" before/after every entry?
    03. If I want to add more rows (say up to 300 rows) or columns, will the VBA code work as it is? ( I need to add, of course, when more styles are available/when the period extends by months etc.)
    04. If I want to add any new information (columns) to this work sheet, will it spoil/interrupt VBA program?
    05. If I copy this work sheet, will the VBA run as it is?

    Kindly explain me pls..

  91. #91
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    The RUN" command is (maybe )redundant: it was there while I tested (See next comment).

    At the moment, the macro is triggered by entry of the date in T6 but I realise this is not the best trigger: so you need decide which data entry triggers the macro.
    An example would be entry of SMV in column Q of my sheet.

    Otherwise, you could use the RUN button after entering new data.

    At the moment, it "loops" through every second row in column A, starting at row 6 until it finds a blank (empty) entry in column A.

    FYI: I do not recommend having your summary tables on the same sheet as your "production data": it would be better on a separate sheet.

    If you add more columns, then yes the macro will be affected although it is possible to make the selection of the required columns dynamic by matching (for example) column headings.

    Copying the worksheet will copy the "Worksheet Change" code but will not copy the main macro (Get_Start_Up_Date) so if you have a new workbook, you will need to copy that code.

  92. #92
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Wink Re: Filtering data based on few criteria such as month, style, item etc

    John, I tried to add the new information(I,J and M columns), then the work sheet doesn't work as you explained. I need this VBA to run in the new format. If you don't mind, kindly copy the VBA to the new sheet,do the necessary adjustments and send me pls.(you have already done a great deal and it is highly appreciated).

    Also, explain me pls, if I add columns at the end of the table(in other words after NA, NB columns etc.) does it still affect VBA code? and, if I insert rows for new styles, after the last row of the current table, does it affect VBA code?

    Attached here the new work sheet.

  93. #93
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    Try the changes yourself

    in the VBA, change references to the columns R, S, T and U in my file the corresponding ones in your file.

    e.g

    sDate = Cells(Srow, "T") - Cells(Srow, "R")
    Cells(Srow, "S") = sDate

    Change the R, S and T as required.

    I have already explained what happens if you add columns or rows.

  94. #94
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    There are three VBA code windows(when I viewed the code), my knowledge about VBA is almost nill I don't know, what to copy in what

  95. #95
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Hi Glenn, are you busy these days??

  96. #96
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Unhappy Re: Filtering data based on few criteria such as month, style, item etc

    Hi Glenn, The Planning work sheet, you helped me to develop has now been improved to include many criteria that are required for better planning. In this case, I have a need to "add some days" as start up days(during these days, prod. is zero). These start up days depend on the criteria given in the "categories" sheet in column O and P. John gave some solutions to add these days, but we face a problem when holidays (weekly and annual/festival) etc "overlap" in between these "start up days".
    For example, start up days go inside the weekly holidays/annual holidays also. But, the requirement is start up days, should "only appear on working days". Kindly help me to get this logic in this sheet. John gave a VBA solution, but it has some limitations such as inability to add/change rows columns etc.

    Pls help. the sheet is attached herewith.

  97. #97
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Cool Re: Filtering data based on few criteria such as month, style, item etc

    Quote Originally Posted by JohnTopley View Post
    Try the changes yourself

    in the VBA, change references to the columns R, S, T and U in my file the corresponding ones in your file.

    e.g

    sDate = Cells(Srow, "T") - Cells(Srow, "R")
    Cells(Srow, "S") = sDate

    Change the R, S and T as required.

    I have already explained what happens if you add columns or rows.
    Dear John,
    As per your advice I have included VBA in to my new excel work sheet and it is working fine.

    However, I need your help to work out one more requirement in this VBA coding.

    Requirement;

    I need to start a "new style" on the same sheet just by changing the start date in the "U column" to start again from the same date as in the "row 6 of column U". The reason is, I want to use the same sheet to plan all the styles of "different lines/Units". Normally, we plan 3 to 6 styles "per line" which would run for around 3 months.
    However, as planning starts "on the same day for all the lines/units", I need to get back to the "same production start date" when I plan a different line/unit on the "same sheet".

    Hope you would give me you would give me your valuable and expert VBA technique to handle this situation.

    Regards
    Anuruddha
    Attached Files Attached Files

  98. #98
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    How is a "new style" identified as in the "Style" column, all the values are different so there no obvious "criteria"?

  99. #99
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    ...and if you change the start date manually do you get the correct results?

  100. #100
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Quote Originally Posted by JohnTopley View Post
    How is a "new style" identified as in the "Style" column, all the values are different so there no obvious "criteria"?
    There are many cases where a style with same characteristics (SMV, style no, customer, Unit, line, style status, etc.) are same. We call these "repeat orders/styles". They can be planned in the same production line/unit or in a different unit/line etc.

    However, when a "repeat order" is received, the "customer ord no(column E) always changes. But, there are times, the same "repeat order" with same "customer order no" is planned at a different unit/line during the same time period.

    Anyway, if you need any criteria that defines the "changing from old style to new style", the only thing we can apply is "keeping a blank row" when a new style is planned, which has a "start date" the same as "row 6 of column". In other words, I can always keep a blank row after the planning for a "particular line" is finished. This blank row defines that the next row will be a new line/unit with the same "Production start date".

  101. #101
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Quote Originally Posted by Anuru View Post
    There are many cases where a style with same characteristics (SMV, style no, customer, Unit, line, style status, etc.) are same. We call these "repeat orders/styles". They can be planned in the same production line/unit or in a different unit/line etc.

    However, when a "repeat order" is received, the "customer ord no(column E) always changes. But, there are times, the same "repeat order" with same "customer order no" is planned at a different unit/line during the same time period.

    Anyway, if you need any criteria that defines the "changing from old style to new style", the only thing we can apply is "keeping a blank row" when a new style is planned, which has a "start date" the same as "row 6 of column U". In other words, I can always keep a blank row after the planning for a "particular line" is finished. This blank row defines that the next row will be a new line/unit with the same "Production start date".
    Re sending with correcting the information highlighted in "red".

  102. #102
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Quote Originally Posted by JohnTopley View Post
    ...and if you change the start date manually do you get the correct results?
    No, I already tried this. It doesn't work. After I manually change and apply "run command", it ignores the change and starts considering the date in "row 6 of column U".

  103. #103
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Quote Originally Posted by Anuru View Post
    No, I already tried this. It doesn't work. After I manually change and apply "run command", it ignores the change and starts considering the date in "row 6 of column U".
    However, if you can define in VBA that "every manual change that occurs in "column U" as a "entry of a new line/style/unit, this will definitely fulfill our need.

  104. #104
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    One more thing John. Keeping a blank row will affect my "excel filtering option", which you may consider to avoid if "manual changing of date in column U" can be considered as an entry of a new line/style/unit.

  105. #105
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    See attached:

    row 16 inserted: my view better to insert two lines to have consistent formatting of data i.e. 2 rows per line).

    Please Login or Register  to view this content.
    Attached Files Attached Files

  106. #106
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Hi John,
    Thanks again for your great and invaluable support in getting this done. By the way, if I have to change/add columns in the range A - X, in case of any addtional details are required, which information I am supposed to change to match the VBA coding to work it as it is?

    Kindly let me know the information which I should change in the VBA code to accommodate for any changes in the columns?

    Regards
    Anurudddha

  107. #107
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    The same changes as before: if you add column then "U" becomes "V", if you delete a column then "U" becomes "T": the code has made no changes to the columns selected.

  108. #108
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Question Re: Filtering data based on few criteria such as month, style, item etc

    Hi John,
    I faced a little problem as I had to change the "Friday formula(Holiday)" due to a practical reason. Because, in the work sheet I have sent to you Friday holiday was counted using the following formula, which is highlighted in "red".
    =IF(Z$5<$T6,0,IF(Z$5<$U6,0,IFERROR(IF(MATCH(Z$5,Holidays!$B$2:$B$16,0),0),IF(WEEKDAY(Z$5,2)=5,0,IF(Z$5=$U6,MIN($N6,$W6),IF(SUM($X6:Y6)+$W6>$N6,MAX($N6-SUM($X6:Y6),0),$W6))))))

    However, there are times that the production lines work on Fridays, when deliveries become tight. Therefore, I have changed the formula as follows;

    =IF(Y$5<$L6,0,IF(Y$5<$T6,0,IFERROR(IF(MATCH(Y$5,Holidays!$B$2:$B$16,0),0),IFERROR(IF(MATCH(Y$5,Fridays!$A$2:$A$35,0),0),IF(Y$5=$T6,MIN($R6,$V6),IF(SUM($W6:X6)+$V6>$R6,MAX($R6-SUM($W6:X6),0),$V6))))))

    The benefit here is, in case a Friday works, I can simply delete that "Friday" from the "Friday sheet", and the formula is updated automatically as it is linked with the Friday sheet. This change was done to the earlier sheet in which the VBA coding was not present. This is working fine in that sheet.

    However, after I received the VBA new version, Final Mod 1, I did the same change to the formula. But, it is not working. After I changed the formula, it counted all Fridays as working days.

    Is the VBA code linked with this formula? If so, how can I include new Friday formula?

    I have attached here both the work sheets here for your reference.

    Regards
    Anuruddha

  109. #109
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    I will do more work when you finalise your requirement: at the moment to VBA just checks if a day is Friday.

    If you look at the VBA you will see there is a check for HOLIDAYS and another for Weekday=5: you can replace the latter with the "same" code as the holday check but the check range is (could be) "Friday" (which should be a named range).

    Please Login or Register  to view this content.

  110. #110
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    I changed the VBA code as follows:

    next_date:
    '
    ' Is this a holiday ?
    '
    Var = Application.Match(CLng(sDate), Range("holidays"), 0)
    '
    ' .. not a holiday : is it a Friday ?
    '
    If IsError(Var) Then
    Var = Application.Match(CLng(sDate), Range("fridays"), 0)
    sDate = sDate + 1
    If nd = 0 Then Cells(srow, "T") = sDate ' If First Start Date is holiday/no-work , then update Start Date
    Else
    nd = nd + 1
    If nd = 1 Then Cells(srow, "T") = sDate
    sDate = sDate + 1
    End If

    I changed Only the code highlighted in red, but it gives "compile error, Else without if" etc. May be, I have missed something else.
    Last edited by Anuru; 06-10-2017 at 11:53 PM.

  111. #111
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    Try

    Please Login or Register  to view this content.

  112. #112
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    Not working. It give run time error, 1004 and "global failed 'range' object' etc..

  113. #113
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    Is there a named range called "Fridays" ?

  114. #114
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    I am not sure, I understood your question. I have added one sheet(tab) for Fridays, and included all Fridays in a table. Then, I used the "match" formula as in "holidays". You can see the attachment.

  115. #115
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Filtering data based on few criteria such as month, style, item etc

    Create a "Named Range" called "holidays":

    Formulas==>"Define Name"==> Name: Fridays ==>Refers to: select data range in tab "holidays"..

  116. #116
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    There is already a sheet tab "Holidays" for annual/festival holidays in the work sheet. I cannot create another sheet tab with the same name "holidays".

  117. #117
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    John,
    Now, I noticed one issue with the Friday formula(range) and I corrected it. Now, production is not counted on Friday, but the "run time error 1004 and Global failed etc is still coming..

  118. #118
    Forum Contributor
    Join Date
    12-06-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2016
    Posts
    605

    Re: Filtering data based on few criteria such as month, style, item etc

    John,
    Now, the formula is working fine with the same old VBA code. No need to apply the new codes, which you have provided above.

+ 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] Sum data over multiple worksheets based on month criteria.
    By ndougl in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-10-2015, 04:02 PM
  2. Filtering excel data based on criteria of interest
    By Mary22 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-30-2015, 04:03 PM
  3. [SOLVED] Lookup data based on month criteria
    By Jhon Mustofa in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-05-2014, 11:22 AM
  4. [SOLVED] Filtering Data based on Multiple Criteria
    By excelhelporfavor in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-30-2013, 11:37 PM
  5. macro to compare item based on month
    By koi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-19-2012, 09:55 AM
  6. Filtering data based on criteria
    By rhudgins in forum Excel General
    Replies: 6
    Last Post: 04-15-2010, 02:03 PM
  7. Filtering data from one large table into many smaller ones based on certain criteria.
    By hugedomer11 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-11-2010, 12:57 AM

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