-3

I have 19,000 datapoints in each row, now my problem is how to delete 20 rows simultaneously after an interval of 10 rows?

Improve this question
1
  • Your question is really unclear. Please add some sample data showing the kind of transformation you want to make.
    – nc4pk
    Mar 5, 2016 at 23:01

1 Answer 1

Reset to default
2

To clarify: You want to keep the first ten rows of data, delete the next twenty rows, and then repeat the pattern? If so, insert a new column to the left of your data, mark the rows to be deleted using the following formula, filter out the blanks (rows to be kept), and then delete all rows marked with an "x".

I'm assuming that you have headers in row 1, and your data starts in row 2. Paste this in A2 and fill it down. If your data starts somewhere other than A2, just edit ROW(A$1) to reference the cell directly above wherever you paste this formula.

=IF(MOD(ROW()-ROW(A$1),31)>10,"x","")

This formula counts the number of rows it is from the reference, divides by 31, and then returns the remainder. If that remainder is larger than 10, then it's a row that you want to delete.

I used 31 because MOD(30,30) returns zero. Just to illustrate how MOD() works a little further, you could also use this formula to accomplish the same thing. 

=IF(MOD(ROW()-ROW(A$1)-1,30)>=10,"x","")

There are two adjustments there. One of them shifts the zero from row 30 to row 1, and the other is to keep the "x" from shifting also.

See it in action

Improve this answer
1
  • You don't have to thank anyone in advance, but rather accept the best answer after the fact.
    – picobit
    Mar 4, 2016 at 4:01

Not the answer you're looking for? Browse other questions tagged .