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!
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?
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?
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
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?