+ Reply to Thread
Results 1 to 17 of 17

Extracting Data with Multiple Criteria - $150 Reward

  1. #1
    Registered User
    Join Date
    09-20-2012
    Location
    Qatar
    MS-Off Ver
    365
    Posts
    36

    Exclamation Extracting Data with Multiple Criteria - $150 Reward

    Weekly Cycle Organizer.

    I have attached My Workbook so that you can read the comments and try to update my formulas to give the correct result.

    1) - Must use the formulas used in the cells or similar to give exact results (Extract the correct Data leaving out the unwanted criteria & paste it into the corresponding Worksheet).
    2) - No modification to the Master Data work sheet at all is a must.
    3) - Please read and understand the comments on the worksheet.

    Now ill explain in more details so everyone gets the picture of the requirements:

    The master data is a Table for the Maintenance Activities for the week.
    It has an Order Number, a Sort Field, a Functional Location,a Work Center, a Frequency (Std text key column), a Plant Section and Plant.

    The functional location such as the first from the master data worksheet = REF1/B/01/0108J

    REF1 is the Plant
    /B/ is the Process type
    18/ is the Unit
    and 0108J is the asset (in this case its a Pump)

    So for REF1 plant I want to extract onto the REF1 - METC worksheet all the rows that are:

    METC (Mechanical) & is REF1 but I also want to include two Units onto this worksheet which are:

    REF2/*/18* (Any unit 18 assets) as well as REF2/*/19* (Any unit 19 assets) that are METC (Mechanical), in Cells B4, C4, D4, & E4
    So extract rows if its = B4 & C4 and also if it is = B4 & D4 or B4 & E4

    This is for REF1 - METC Worksheet

    Now for REF2 - METC Worksheet I want it to:
    Extract the rows from Master Data if it is:

    METC (Mechanical) & is REF2 but I also want to exclude two Units onto this worksheet which are:

    REF2/*/18* (Any unit 18 assets) as well as REF2/*/19* (Any unit 19 assets), in Cells D4, & E4
    So extract rows if its = B4 & C4 and exclude if it is = B4 & D4 or B4 & E4


    Then there is the count Cell F4
    This counts how many records fit the criteria for each worksheet.

    The last thing left is to update the frequency for each record.
    The Frequency is the last two characters in the cells of Column K.

    Column K is the Std text key, example : PMPUU6M, where 6M means Six Monthly (Frequency) task list to maintain the asset.
    If you look at some Order Numbers, some are duplicated but the data in column K is not available, I want to update Column K
    if the Order numbers are the same and one of them has the cell populated. This can be from above or below the Row we are trying to populate.

    Thats about all for this Workbook. Once this is complete I have another Challenge. The Performance Charts Data Table.
    So if anyone is interested in this & happens to solve my queries I will give the $100.

    Lets see if it can be done easily, I think if your an advance excel user it should not take more than one hour of your time to make a $100.

    Best of luck.


    24WLA FILE ORGANIZER NEW.xlsx.zip
    Last edited by emirmansouri; 09-21-2012 at 04:05 PM. Reason: Title not descriptive enough.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,108

    Re: Extracting Data with Multiple Criteria - $100 Reward

    i know this is a new thread and you closed the previous 1, but is IS still the same thread
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    09-20-2012
    Location
    Qatar
    MS-Off Ver
    365
    Posts
    36

    Re: Extracting Data with Multiple Criteria - $100 Reward

    its exactly the same requirements, yes.
    Just I would put a clearer description..

  4. #4
    Registered User
    Join Date
    09-20-2012
    Location
    Qatar
    MS-Off Ver
    365
    Posts
    36

    Re: Extracting Data with Multiple Criteria - $150 Reward

    Ok, I have increased the reward to $150.
    Seems no one has been able to do it, now what shall I do?
    Anyone, please help.....

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,108

    Re: Extracting Data with Multiple Criteria - $150 Reward

    is there a reason you dont want a helper column added to sheet1? you can always hide it, or put it far to the side.

  6. #6
    Registered User
    Join Date
    09-20-2012
    Location
    Qatar
    MS-Off Ver
    365
    Posts
    36

    Re: Extracting Data with Multiple Criteria - $150 Reward

    Hi FDibbins, if you think you can do it lets go for it.
    I tried putting in the formulas you provided and it does part of the job but when you look at the table it still shows rows of data for 18 & 19.
    I want to extract only REF2 and not those. So they should not show up on the REF2 - METC Worksheet.
    If we can do that it would be great.

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,894

    Re: Extracting Data with Multiple Criteria - $150 Reward

    This is a pretty big request in one chunk. I'm sure you'll get better response if you break down your question into several parts, so that they can be tackled one after the other.

    Also, we're happy to help you with your effort, but nobody here will be keen on developing a solution from scratch. This forum is about helping people learn how to use Excel, not a free development service.

    Have you started any work on this project yet?
    How far did you get?
    Where are you stuck?

    We can help you develop your own code, but if you want someone to code this for you all the way, you should try and find a commercial developer.
    Ben Van Johnson

  8. #8
    Registered User
    Join Date
    09-20-2012
    Location
    Qatar
    MS-Off Ver
    365
    Posts
    36

    Re: Extracting Data with Multiple Criteria - $150 Reward

    Hi Ben,
    If anyone takes the time to look at my sheet they would understand that there is only 3 formulas to update. Its not from scratch at all. Yes the query is long but its three equations.

    The request & description are long so as to explain it as good as I could, to be clear.
    Thank you for your time.
    But im really despatate to get it up and running thats all.
    Im not asking anyone to do it from scratch.

  9. #9
    Registered User
    Join Date
    09-22-2012
    Location
    london, england
    MS-Off Ver
    Excel 2011
    Posts
    25

    Re: Extracting Data with Multiple Criteria - $150 Reward

    Is it usual to pay for help on this forum ?
    Last edited by hatrickx; 09-22-2012 at 06:04 AM.

  10. #10
    Registered User
    Join Date
    09-20-2012
    Location
    Qatar
    MS-Off Ver
    365
    Posts
    36

    Re: Extracting Data with Multiple Criteria - $150 Reward

    hatrickx, no its unusual. I asked a mod and he said it was fine.
    Why have I done anything wrong by offering a reward!

  11. #11
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Extracting Data with Multiple Criteria - $150 Reward

    Just to get you started..

    Count on REF1 sheet
    Please Login or Register  to view this content.
    Count on REF2 sheet
    Please Login or Register  to view this content.
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,108

    Re: Extracting Data with Multiple Criteria - $150 Reward

    take a look at the attached and see if this is what you want?

    i had to restrict the number or rows on sheet 2 and 3 to 100, but you should just be able to copy down as far as you need, if this works for you
    Attached Files Attached Files

  13. #13
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Extracting Data with Multiple Criteria - $150 Reward

    this has been posted on other forums too so you should provide links to those posts in accordance with the forum rules on cross-posting
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  14. #14
    Registered User
    Join Date
    09-20-2012
    Location
    Qatar
    MS-Off Ver
    365
    Posts
    36

    Re: Extracting Data with Multiple Criteria - $150 Reward

    Look guys im looking for a solution, so I posted on one other forum the exact same post (so please do not go over the top when you say forums!). I did not link it to this post. All I did was link the file from this site.
    I am in need of urgent help thats all. If I have offended anyone im sorry, if I have broken any rules, im honestly sorry, but for a valued forum contributor I hope this is not a contribution, have you read my query or did you even understand my request. I would rather see ppl contribute their efforts in helping ppl rather than worrying about the forum rules. I will obey by them as I get more and more familiar with the forum and ppl that roam around here, but I thought there was a nicer way to send a message to someone. Maybe a PM would have been more appropriate?, im just saying...

    Anyway I would like to thank you for your message I will look at the rules and make the required changes. How many days do you generally allow? is it 2?

  15. #15
    Registered User
    Join Date
    09-20-2012
    Location
    Qatar
    MS-Off Ver
    365
    Posts
    36

    Re: Extracting Data with Multiple Criteria - $150 Reward

    FDibbins thanks for your effort, I see that it works well on REF2 METC but on REF1 METC its not including REF2 units 18 or 19. Is this possible.

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,108

    Re: Extracting Data with Multiple Criteria - $150 Reward

    when you select REF1 as a criteria, why would you want it to include anything that has REF2?

    i tested it with REF1/METC, and on your data sheet, you only have 3 entries that match that criteria. you will see in the file that i uploaded, i added filters to your MASTER sheet, use them to test the answers

    when you decide to EXCLUDE something (in this case 18 & 19) i assume that you want to exclude it from whichever MAINT PLANT is selected? if that is the case, we could just enter "18" and "19" in the "excude" cells, and then just use those as a reference for the helper column on your MATER sheet, it wiould give you more flexibility than hardcoding 18 and 19 into the formulas

  17. #17
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Extracting Data with Multiple Criteria - $150 Reward

    Hello emirmansouri, and welcome to the forum.

    Unfortunately you have inadvertently broken two of the forum rules. Please read the following and make the necessary change. Thanks.

    Firstly, I'm afraid your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

    Secondly, because you have not done what was asked of you in post #13 (post link to post in other forum):
    Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore Moderators' or Administrators' requests - note that this includes requests by senior members as well, if you are unclear about their request or instruction then send a private message to them asking for help. Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc

+ 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