Solved

Trying to delete certain rows in a Excel Spreadsheet

Posted on 2014-01-17
5
378 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 35

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 35

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

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…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

705 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