Sorting and Moving Pass Fail Results

Topic

New Reply

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

Reply | Quote

Replies

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.

[*]Select the SHT_Failures.
[*]Click into cell A1
[*]Click on the Data tab and select Advanced
[*]Under Sort & Filter click Advanced.
[*]Click OK at the error message and ignore it.
[*]Click the Copy to Another Location radio button.
[*]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.
[*]In the Criteria Range Type Criteria.
[*]In the Copy To range type Results.
[*]Click OK

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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:
[noparse]=Sheet1!A1[/noparse] 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.:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thank you…..

Reply | Quote