Results 1 to 7 of 7

Thread: Delete first rows and keep later ones

  1. #1

    Delete first rows and keep later ones



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am struggling to work out a problem I hope you can help with.

    I have an Excel spreadsheet with 10 columns of data and 1000+rows. The first x number of rows are called "Group 1" identified by the word "Group 1" in column A of each row. The next x number of rows are identified in the same way as "Group 2" and so on. The number of rows in each group can vary from 4 to 40.
    Later in the list another batch of rows may also be identified as "Group 1". In fact there may be several batches of rows tagged as Group 1. Although they are tagged as Group 1, they may have a different number of rows than a previous Group 1.
    What I need to do is:

    Wherever a group is repeated in my list, I need to KEEP only the last occurrence of the group (all rows) and delete the rows from all earlier occurrences of that group.

    I have made reference to Group 1, but, similarly, any of the groups can be repeated and I will only want to retain the last occurrence on the list.

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Shoul be easy enough with VBA. Can you upload a dummy workbook for me to use? Will save me some time.

  3. #3

    Great thanks

    I'm new to these forums. Hopefully the xls file with dummy data has attached. s1
    Attached Files Attached Files

  4. #4
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Here's a very simple formula-based solution:
    In row K, put "Filter" into K1
    In K2, type =A2<>A3
    Copy that formula down the entire column (quickest method is to select the cell, and double-click on the little box on the bottom right hand corner).
    Extend the autofilter so that it also filters on column K. (Quickest method is to select the data, turn filter off, then turn it on again).
    Filter column K on "False", select all, and delete. This will leave just the records you want. (Note that it will also return errors in Column K, but that's no problem...just delete it.)

    Can also record a macro to do this. Simply start up the macro recorder, record your actions, and then stop recording. You will need to then adjust the code so that it handles any sized range. If you need help to do this, just hollar.

  5. #5

    Hey thanks, but..

    That doesn't quite do what I want. As an example... In the spreadsheet there are 3 sets of rows with Group1 in column A. The first set of rows (rows 2-6) and the second set of rows (rows 102-120) should all be deleted, and the 3rd set of rows (rows 193-226) should be retained. Thus keeping the last batch of Group1 on the list. Need similar for all of the other groups. This sample sheet has a total of 285 rows. My real world spreadsheet has close to 1 million. Thanks again.

  6. #6
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Ahhh. 2nd time I've misinterpreted a question in as many days! I'll take a look.

  7. #7
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Okay, try this formula:
    =OR(NOT(IFERROR(MATCH(A2,A3:$A$285,0)>0,FALSE)),AND(A2=IF(ISBLANK(A3),A2,A3),IF(ISBLANK(K3),TRUE,K3)=TRUE))
    Note that the $A$285 bit will need to be changed to the last row in your data, and the dollar signs MUST be there for this particular reference (i.e. it is an absolute reference, while the others are relative).

    Same approach as before:
    In row K, put "Filter" into K1
    In K2, the above formula.
    Copy that formula down the entire column (quickest method is to select the cell, and double-click on the little box on the bottom right hand corner).
    Extend the autofilter so that it also filters on column K. (Quickest method is to select the data, turn filter off, then turn it on again).
    Filter column K on "False", select all, and delete.

    If you are going to do this more than once, I could help you automate it via a macro.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •