Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column picked randomly with probability relative to number of entr | Excel Worksheet Functions | |||
Conditional Format as a MACRO | Excel Worksheet Functions | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
IF/AND/OR/DATEIF Issue...sorry...long post... | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) |