Excel 2010 - I need help to delete rows of a report which contains $- or zero value. The report is grouped by account number (in hundreds of account group and thousands of rows) and not in the list format, which make it difficult to use Excel filter and formula to delete the unwanted $- value.
1. I need to delete the $- rows but not "Beginning Balance with $-" should remain in the report. But if the "Subtotal" is $-, then need to delete all the rows from "Account Number" to "Subtotal" plus one empty row (Beginning Balance will be deleted too). This program should continue to search and delete until all $- rows are removed.
2. For each rows (data between "Beginning Balance" to the last row of that Account Number group), I need to copy the Account Number (example 211-00-5936-00-001-3-00-2-00 etc) and paste beside the Data in column F, paste formula =(IF(B11="","7/1/2012",EOMONTH(DATEVALUE(B11),0))) in column G, paste formula =MID(F11,8,4) in column H, and past formula =MID(F11,16,3) in column I.
I attached the file with raw data on ReportData sheet and the expected output on FinishedReport. Appreciate any assistance, help, or reference.
Bookmarks