Excel - Formula to Copy Rows to Another Sheet but Skipping Columns with 0's

Question

I have data that looks like the example below. In some places there are 0's. I want to write a formula in a blank worksheet that will copy the data over but skip rows with 0's.

So, in my example rows 7-18 would now be rows 1-12 and then rows 19-27 would be skipped and row 13 would be the next row without 0's, etc. In other words, I want to copy rows and remove rows with 0's but I do not want blank rows where the 0's are.

Answer 1

Try one of the followings, which should work accordingly :

=FILTER(A1:J27,BYROW(A1:J27,LAMBDA(x,AND(x>0))))

---

Or,

=FILTER(A1:J27,BYROW(A1:J27>0,AND))

---