I have Excel worksheets that have 14 columns (and can have over 100,000 rows). I usually have 5-10 of these worksheets in a single workbook, so I would like to have a module where I can copy and paste in the name of the worksheet that I am working with.
The details of what I would like to do with each worksheet are below.
First, Column 7 (G) has to be checked for the presence of 'OK'. This column will be either 'OK', 'FAIL', 'NOTEST', or 'HIDATA' (no single quotes). Only OK is acceptable, all rows that have other than 'OK' can be deleted. (One exception; row 1, the header row for the worksheet, has in this column the single word 'status' in it, and I would like to keep the header row.
Next, Column 8(H) needs to be checked for single zero's (by that I mean a lone 0, not a 0 as in 10 or 101 etc.). If there is a lone 0, then it needs to be changed to a 1. Column 9 (I) then needs to checked. If it is non-zero, then the log to base 2 ratio of column 9/8 (I/H) needs to be calculated in column 10. If there is a lone zero in column 9, then it should be changed to a 1 and the log base 2 calculation done in column 10.
Then, the same has to be done for Column 9 (I). That is, check for a lone zero. If present, change to a 1, check column 8 (H) for non-zero, then calculate log to base 2 ratio of columns 9/8 (I/H) in Column 10 (J). Again, if after changing to a 1 in column 9 and there is a lone zero in column 8, then that has to be changed to a 1 and the calculation done in column 10.
When this is finished, adjust all of column 10 to a single decimal place, such as 50.1, 0.1 etc.
Columns 8 and 9 then have to be checked again. This time for any row in which the value in both columns 8 and 9 are less than or equal to 3, the entire row is to be deleted.
Column 10 then needs to be checked. For any row, that has a value in column 10 of less than 1, the entire row is to be deleted.
Finally, but not necessary if this is too difficult to do, the whole sheet (excluding the header) beginning in column 1 row 2 is to be sorted from high value to low value on column 10.
I have attached an Excel workbook with 2 worksheets. The fist worksheet (before) is an example, although real sheet could have over 100,000 rows, and the second worksheet (after) is what the before sheet would look like after having done the above.
Thank you in advance for your efforts.