Solved

Trying to delete certain rows in a Excel Spreadsheet

Posted on 2014-01-17
5
336 Views
Last Modified: 2014-01-17
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
0
Comment
Question by:seamus9909
  • 2
  • 2
5 Comments
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39788613
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39788636
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
 

Author Comment

by:seamus9909
ID: 39788702
Can you show that to me in the spreadsheet I provided?
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39788745
See attached with sheets for each stage.

Thanks
Rob H
ABC-Star-list.xlsx
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39788818
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now