Delete first rows and keep later ones

s1lverface

New member
Joined
Jun 15, 2013
Messages
4
Reaction score
0
Points
0
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.
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
Shoul be easy enough with VBA. Can you upload a dummy workbook for me to use? Will save me some time.
 

s1lverface

New member
Joined
Jun 15, 2013
Messages
4
Reaction score
0
Points
0
Great thanks

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

Attachments

  • Test Groupings.xls
    102 KB · Views: 9

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
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.
 

s1lverface

New member
Joined
Jun 15, 2013
Messages
4
Reaction score
0
Points
0
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.
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
Ahhh. 2nd time I've misinterpreted a question in as many days! I'll take a look.
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
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.
 
Top