Trying to delete certain rows in a Excel Spreadsheet

I have a spreadsheet , that I have attached.  There are thousands of rows that we want to delete automatically if a certain condition is true.

The rows represent unique records that are kind of nested. We want to delete the rows associAted with the  "ABC STAR" as an example:

SelectHoldingsConstraintsRebalance   T   10017410203395 REBECCA BUFFINGTON FOR DCB   10/31/2013 US
    ABC STAR  
         IJR ISHARES CORE S&P SMALL-CAP E  32.000%  30.670%  1.330%    
          CASH  3.000%  4.000%  1.000%    


if we find ABC STAR, we want to delete the row above "SelectHoldings...." and delete the rows following ABC STAR until we hit the next occurance of SelectHoldings...." .  We want to repeat this until all rows are deleted that we want. in the example spreadsheet you will see :  We do not want those rows to be deleted.  Is there anyway to write a macro or something to do this?  This is example data and does not contain and private or confidentail information.

SelectHoldingsConstraintsRebalance      12107418931409 THOMAS H O'BRIEN JR IRA IMA   12/20/2013 NF
    Jennison Large Cap Grwth  
         NBL NOBLE ENERGY INC  0.000%  0.430%  0.430%    
         EBAY EBAY INC  0.000%  0.420%  0.420%    

    SelectHoldingsConstraintsRebalance      12107418931409 THOMAS H O'BRIEN JR IRA     Jennison Large Cap Grwth  
         NBL NOBLE ENERGY INC  0.000%  0.430%  0.430%    
         EBAY EBAY INC  0.000%  0.420%  0.420%
testing-security-drift.xlsx
seamus9909Asked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
See attached with sheets for each stage.

Thanks
Rob H
ABC-Star-list.xlsx
0
 
Dan CraciunIT ConsultantCommented:
Your data is not really a spreadsheet, so instead of using Excel for that you can use GREP or similar, with regular expressions.

I'll give it a go in a bit, in the mean time add regular expressions to the category, if you want quick results.

HTH,
Dan
0
 
Rob HensonFinance AnalystCommented:
Try this in a blank column to oneside.

Insert a row above the data and insert formula in row 2 referring to data in column A:

=IF(A2="","",IF(C1="Delete","Delete",IF(C1="Keep","Keep",IF(ISERROR(MATCH("*ABC STAR*",A2:A5,0)),"Keep","Delete"))))

Copy down as far as required.

In the first row of each block, this tries to find "ABC STAR" within the next 3 rows. If found puts "Delete" otherwise puts "Keep". The following rows then repeat the "Keep" or "Delete" result until it gets to a blank in column when it resets to "" (Blank). This does rely on column A actually being blank, your data does have some value.

You can then filter on this column and delete rows as required.

To get column A truly blank, the AutoFilter will recognise the"empty" cells as blank so can filter on this column first and then delete, but delete contents only so that you still have a blank row between sets.

Thanks
Rob H

Thanks
Rob H
0
 
seamus9909Author Commented:
Can you show that to me in the spreadsheet I provided?
0
 
Dan CraciunIT ConsultantCommented:
As I said, it can easily be solved without Excel. See the image for how to solve this in Notepad++ (free, http://notepad-plus-plus.org/).

I just copied the text from your spreadsheet and then I can run the expression in blue to delete all the blocks that contain "ABC STAR"

This can be done with any editor that supports regular expressions.
use regular expressions
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.