Notifications
Clear all
Topic starter
I have data that I want to ensure that specific ranges of rows remain in the dataset.
In the example below. I want to keep 4 rows below item c in col1. For every entry of 'c'.
Col 3 shows for example purposes the rows that I want to remain.
I cannot for the life of me find a way of doing it.
| Col 1 | Col 2 | Col 3 |
| a | 1 | |
| b | 2 | |
| c | 3 | x |
| x | 4 | x |
| y | 5 | x |
| fff | 6 | x |
| g | 7 | |
| h | 8 | |
| i | 9 | |
| j | 10 | |
| k | 11 | |
| l | 12 | |
| c | 13 | x |
| d | 14 | x |
| e | 15 | x |
| f | 16 | x |
| g | 17 | |
| r | 18 | |
| s | 19 | |
| c | 20 | x |
| cd | 21 | x |
| er | 22 | x |
| ff | 23 | x |
Posted : 30/04/2021 2:54 pm
Hi Mark,
Welcome to our forum!
You can use this formula in a new column to extract the values from column 1:
= try if [Col 1] = "c" then [Col 1] else
if #"Changed Type"{[Col 2]-2} [Col 1] = "c" then #"Changed Type"{[Col 2]-1} [#"Col 1"] else
if #"Changed Type"{[Col 2]-3} [Col 1] = "c" then #"Changed Type"{[Col 2]-1} [#"Col 1"] else
if #"Changed Type"{[Col 2]-4} [Col 1] = "c" then #"Changed Type"{[Col 2]-1} [#"Col 1"] else null otherwise null
Then filter out the null rows. See file attached.
Mynda
Posted : 01/05/2021 5:19 am
Hi Mark
You could also use a formula instead.
In cell C2 enter =IF(A2="c",1,IF(C1<>0,IF(C1<>4,C1+1,0),0))
You can then filter out the zeros.
Sunny
Posted : 01/05/2021 8:19 am
