# Google Sheets problem that may not have a solution. Formula to input any values into exactly 2 cells following any cell with a value. Details below

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.

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'.

• 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, 2023 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! Mar 1, 2023 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, 2023 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, 2023 at 15:55
• 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. Mar 1, 2023 at 17:41