If Cell Contains Value then delete row AND next 3 rows down

[Goran]

Hi everyone,

I have a column [Current] with numerical data:

Current
123
6
22
74
124
98
765
951
123
6
76
49

I'm trying to work out a way that if the cell CONTAINS value 123 then that row plus the next 3 rows down will be deleted.  If the cell does not contain 123 then this action will not be triggered until 123 is found.  So, the desired result would be:

Desired

124

98

765

951

The values in the 3 cells below the 123 cells are completely random.  I can't quite figure out an efficient way of doing this.  Any ideas?

Thanks!

[Felipe_Ribeir0]

Hi @Goran 

One way of doing this

[binuacs]

@Goran One way of doing this

[Goran]

Thanks very much for the suggestion!

Question, what if the cell CONTAINS 123 as part of a larger string rather than just 123?  How would we adjust your formula to account for that?

[Goran]

Thanks for the suggestion!

Your method appears to work perfectly with the first grouping of 4 rows, but after that it seems to only group the first 2 rows and ignores the last two rows.  Any suggestions?

[Felipe_Ribeir0]

Hi @Goran 

You can change the type of the column to string and then change the formula to be like this

if Contains([Current], '123') OR Contains([Row-1:Current], '123') OR Contains([Row-2:Current], '123') OR Contains([Row-3:Current], '123') then 1 else 0 endif

[Goran]

Thanks, Felipe.  I actually tried that exact formula but each time I get an error message that [Row-2:Current] doesn't exist.  Actually, anything other than [Current] and [Row-1:Current] throws up an error that Row-2:Current, Row-3:Current etc. is invalid.  Any suggestions on this?

[Felipe_Ribeir0]

Hi @Goran 

Try this workflow, here it is working fine