Deleting every nth through every nth row in a column


G

Guest

I have a column which, in every 50 rows has a value set, different than the
next set.

For example, in rows 1-50, the value 1.1
In rows 51-100, I have in each row 1.2., etc. etc.

Is there a way I can delete the first 20 of the 50 rows in every set of
values. I'm to keep only the remaining thirty, then delete the following
twenty, and keep the following thirty rows, and repeat the process for the
entire column?

I'm new to all this excel though trying to get better. Any ideas?
 
Ad

Advertisements

N

Nick Hodge

Chris

Making the assumption that your 1.1, 1.2, 1.3, etc are in column A. In B1
enter a 1, In B2 enter this formula

=IF(A2=A1,B1+1,1)

Now copy it down. This should put 1-50 alongside your sets. Now go to
Data>Filter>Autofilter and filter by less than 21. This will give you 1-20
on each row. Delete them and remove the filter. If you have just cleared
the rows, not deleted them then you can sort on 1.1, 1.2, 1.3 etc AND the
new 'index' column and the blanks will all go to the bottom (assuming
ascending sort)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top