Solved

Trying to delete certain rows in a Excel Spreadsheet

Posted on 2014-01-17
5
347 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 32

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 32

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

929 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

14 Experts available now in Live!

Get 1:1 Help Now