1

Okay, so, here's what I'm working with and what I need to do:

All the cells in column BQ are filled with either "good", "bad" or are blank. It's mostly blank. I need to get rid of some of the rows where BQ is blank but not all of them. The rows I want to retain are all the rows where BQ has a value (good or bad, doesn't matter) AND the 2 blank rows that immediately follow.

So, if there exists a formula that can seek out every "good" or "bad" value in col BQ and then fill in literally any value into exactly 2 cells in the same column (BQ)? Then I can filter BQ for blanks and delete the remaining rows.

Or if anyone has another solution for this issue, I'm stumped.

Improve this question

1 Answer 1

Reset to default
1

Based on your description, you can try:

=filter(BQ:BQ,
map(BQ:BQ,lambda(x,
if(len(x),1,if(or(len(offset(x,-1,0)),len(offset(x,-2,0))),1,0)))))

This creates an array with a 1 for any row in BQ which is non-empty or <=2 empty rows after a non-empty row, and a 0 for all other rows. This can be used directly as the input to FILTER.

EDIT Based on clarified requirements:

=map(BQ:BQ,lambda(x,if(len(x),x,if(or(len(offset(x,-1,0)),len(offset(x,-2,0))),,1))))

Then use the 'create a filter' menu option to remove all the rows which have a '1'.

Improve this answer
9
  • Thank you so much for your help!! I'm not at all familiar with most of these functions, so I'm not fully sure how to read this formula. But I copy pasted this in a new column in my sheet and swapped A:A for BQ:BQ but all that happened was it mimicked the BQ column exactly. All the blank cells were still blank. Do I need to replace the "x"s with something else?
    – JRM
    Feb 28 at 23:25
  • Kudos for picking up on the A:A to BQ:BQ swap - I forgot to change this at my end when answering. Difficult to diagnose exactly why it's not working in your hands, but at my end, on a column of cells with some randomly placed goods, bads and blanks the result (if the formula is placed in a cell in a neighbouring column) is that the only cells that are returned are those which are non-blank or which are less than or equal to two rows below a non-blank cell. The MAP inside the FILTER generates a 1 for cells which meet the above criteria and a 0 for all others. Don't change the 'x's!
    – The God of Biscuits
    Mar 1 at 15:29
  • Okay, I've made a copy of just this (BQ) column in a sheet I can share publicly to show you what's happening on my end. I'm not getting any 1's or 2's or 0's in my returns and I don't know why.
    – JRM
    Mar 1 at 15:53
  • Sent too soon... here's a link to the sheet. You should have editing access. I also notice that the formula stops populating bad's and good's at line 2915 even though there are 3461 lines in column A.
    – JRM
    Mar 1 at 15:55
  • 1
    I've added an updated answer based on your 'Alt Ex Return' example. It sounds like you want to filter using the menu rather than a formula, so use this formula to generate a '1' for the rows you are not interested in and then remove these rows using the 'filter by values' to deselect the '1's.
    – The God of Biscuits
    Mar 1 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.