Solved

Trying to delete certain rows in a Excel Spreadsheet

Posted on 2014-01-17
5
362 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 33

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 33

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

808 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