Deleting every nth through every nth row in a column
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? |
Deleting every nth through every nth row in a column
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 DataFilterAutofilter 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 HIS "chris" wrote in message ... 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? |
All times are GMT +1. The time now is 07:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com