+ Reply to Thread
Results 1 to 9 of 9

Filter Entries to look for certain criteria

  1. #1
    Registered User
    Join Date
    06-29-2012
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    41

    Unhappy Filter Entries to look for certain criteria

    Hello,

    I am looking for someone to create a code for me that will filter for 2 criteria: be between a time interval (July 19,2011 - july 19, 2012) , and match a list of pump #'s.

    First i need it to create an array for the pump list.
    Then it has to look through 3 worksheets " North , south , and east". It is looking for dates that are inbetween a certain time interval ( that will be input in a worksheet named "MTBR", then compare to the pump array and if the pump #'s match, failure count goes up by 1 (populated in cell g2 for each worksheet).

    it would be greatly appreciated to have someone assist me
    I will attach the workbook so its easier to understand.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Filter Entries to look for certain criteria

    Hello,

    I am not sure I am following you correctly. Are you looking to sum between date period? try SUMIF.

    In 'MTBR' sheet B6,

    =SUMIF(North!D:D,">="&C$3,North!G:G)-SUMIF(North!D:D,">"&C$4,North!G:G)

    then copy down, change North to other sheet names in B7:B8
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  3. #3
    Registered User
    Join Date
    06-29-2012
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    41

    Unhappy Re: Filter Entries to look for certain criteria

    Hello, thanks for replying.

    I tried the sumif in the colum you told me and it gave me nothing.

    What i'm trying to do is get a macro to look at those 3 worksheets: find equipment #'s that match the equipment #'s in "sheet1", AND that were scheduled between the dates (that will be input in the MTBR sheet, so they will vary). So for example: if there is a pump "P-2304" and the job to complete it was July 12, 2012. If that pump # is in the list in "sheet1" and happened between the two dates specified in "MTBR" sheet (January 1, 2012 - December 31, 2012), it will count it as 1 failure, once it has gone through a worksheet it will populate the total in the MTBR sheet column C6-C9.

    I hope that makes sense now, reference the workbook as you need, this is not about cost sums its just trying to figure out what important pumps in sheet1 are failing.

    thanks!

  4. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Filter Entries to look for certain criteria

    OK, try this in C6,

    =SUMPRODUCT(ISNUMBER(MATCH(North!C$3:C$22,NorthEquip,0))*(North!D$3:D$22>=C$3)*(North!D$3:D$22<=C$4))

    Defined a name for Sheet1!A2:A153 as NorthEquip. Change the reference in C7:C8

    If Master sheet has all the combined (or Input) data

    =SUMPRODUCT(ISNUMBER(MATCH(Master!C$19:C$31,NorthEquip,0))*(Master!A$19:A$31="North")*(Master!D$19:D$31>=C$3)*(Master!D$19:D$31<=C$4))

    Adjust the ranges & "North" to "South", "West". Better to use dynamic range.

  5. #5
    Registered User
    Join Date
    06-29-2012
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    41

    Unhappy Re: Filter Entries to look for certain criteria

    Hello,
    thanks for all your work but these functions are giving me zero's. I have tried to modify them to work but it is just not occuring. Did you test them yourself on the workbook? Cause I can't figure out whats wrong with them.

    thanks

  6. #6
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Filter Entries to look for certain criteria

    All the dates in North, South & East are in 2010. The Start & End date in MTBR sheet is from 2011. I changed the Start Date to Jul/1/2010

    Also make sure the calculation mode is Automatic.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-29-2012
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: Filter Entries to look for certain criteria

    Hello,

    I have opened the workbook you edited and i changed stuff around and it seems to be working just greatly! The only problem now is when I try to change the ranges for example "=SUMPRODUCT(ISNUMBER(MATCH(South!C$3:C$25 (to the entire column C instead of 3-25) ,SouthEquip,0))*(South!D$3:D$25(to entire column D instead of 3-25)>=C$3)*(South!D$3:D$25(to entire column d instead of 3-25)<=C$4) ) .. When i do that it gives me an "#N/A" error. This workbook will have entried added to it once every 4 months, so I need this function to calculate the new additions, which means looking at the entire column.

    What i tried to do was just write "=SUMPRODUCT(ISNUMBER(MATCH(South!C:C,SouthEquip,0))*(South!D:D>=C$3)*South!D:D<=C$4)), and i got the error message.

    hope that makes sense.

    and i appreciate your assistance once again, it is marvelous!

  8. #8
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Filter Entries to look for certain criteria

    You can use Dynamic Range instead A:A so whenever Add/delete entries will update it. I have used some dynamic range for North, South & East. whenever insert a new date in column D will include until that cell. Same as equipment. whenever add/delete equipment in appropriate locations, will include it.

    Assuming equipment will always be text NOT a VALID numbers. alpha-numeric characters is not a problem.

  9. #9
    Registered User
    Join Date
    06-29-2012
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: Filter Entries to look for certain criteria

    Hello HaseebA,

    I am just curious about the functions you gave me in this thread. "=SUMPRODUCT(ISNUMBER(MATCH(INDEX(SouthData,0,1),SouthEquip,0))*(INDEX(SouthData,0,2)>=C$3)*(INDEX(SouthData,0,2)<=C$4) )".. instead of calling this column "Sheet1!C:C", when it was looking to match it up, how did you get it to be called a different name and yet still identify that it was this range it was looking at.

    thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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