Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Brighton, Michigan
    Posts
    184
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Sorting and Moving Pass Fail Results

    Good Day To All Loungers,

    The attached excel spreadsheet has "pass" (green) "fail" (red) results col. C and Col. D for a series of products manufactured, I have been unsuccessful in attempting to do the following:

    For each column C & D (separately) for each instance of a failure i.e. a red block, I would like to copy all the data in that row to a second worksheet within the same workbook, and lastly total the number of failures by calendar month and if possible by week as shown in the delivery date column. This spreadsheet is a sample of a much larger file that could not be uploaded.

    Your help would be greatly appreciated.....funny thing about excel if you do not use it frequently enough you forget a bit or two...

    Regards,
    Marty
    Attached Files Attached Files
    Last edited by mojave1; 2013-01-26 at 13:16. Reason: clarification of request
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Marty,

    Here's a solution using the Advanced Filter feature.

    I don't follow the article referenced above exactly but it will give you a flavor.
    I set up the SHT_Failures to contain both the Criteria Range {rows 1:3} and the Results Range {row 6}.
    Both of these ranges have been named: Criteria and Results respectively.

    1. Select the SHT_Failures.
    2. Click into cell A1
    3. Click on the Data tab and select Advanced
    4. Under Sort & Filter click Advanced.
    5. Click OK at the error message and ignore it.
    6. Click the Copy to Another Location radio button.
    7. Use the data data selector icon in the List Range and select your source data table, or you can name the data table and paste or type the name in which is my preferred method but I didn't do it here.
    8. In the Criteria Range Type Criteria.
    9. In the Copy To range type Results.
    10. Click OK


    HTH
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    mojave1 (2013-01-29)

  4. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Brighton, Michigan
    Posts
    184
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Hi Retired Greek,

    I was hoping you would respond...as you have helped me out before. Thank you, I will gives this a try, although I thought using a formula in lieu of advanced filter would be easier.

    I'll give it a shot...

    Regards,
    Marty
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  5. #4
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Brighton, Michigan
    Posts
    184
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Hi RetiredGeek,

    Firsrt, my apology for addressing my last reply to "Retired Greek".....to clarify the request for any row that contains a "F" or failure I would like to copy that entire row to a new worksheet within the same work book, if possible I'd like to do this with a formula so that as the +10,000 lines or rows of data grows the identification and transfer of rows with an "F" would occur without my intervention or attention. Sorry for the faux paux and confusion...

    Marty
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  6. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Brighton, Michigan
    Posts
    184
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Hi RetiredGeek,

    I made several attempts to execute the steps as outlined and immediately after step 5 I receive the follwoing error message: "The extract range has a missing or illegal field name", any suggestions? To be clear I am using Excel 2010 and I highleted the range "Sheet1 A2:E3350"......

    Thanks for your assistance.
    Marty
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Marty,

    Usually, you get that message if you typed in the Column headings in the Extract range and made a typing error. Often this is not apparent because of extra spaces. The best way to get the column references, as I did in the Example, is to reference them from the data table. So if the Data table is on Sheet1 and the Column Headers are in row 1 you would reference them as:
    =Sheet1!A1 for the first column and continue across the row. Note not all the headers have to be in the Extract Range only the columns you want to copy. Check out the headers on the SHT_Faliure sheet in the example I posted above.
    Last edited by RetiredGeek; 2013-01-30 at 15:46.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. The Following User Says Thank You to RetiredGeek For This Useful Post:

    mojave1 (2013-01-30)

  9. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Brighton, Michigan
    Posts
    184
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Thank you.....
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •