I am trying to undertake some crude data filtering. I have a single column of data, which requires filtering based on three rules:
1) if the current value is numeric and equal to the previous numeric value, replace with #N/A, otherwise keep current numeric value.
2) if the current value is numeric and greater or less than 0.5 difference from the previous numeric value, replace with #N/A, otherwise keep current numeric value.
3) if the previous value is #N/A or non-numeric, look up the closest previous numeric value above, and undertake 1) and 2).
Obviously 1) and 2) are very easy to achieve, but I'm struggling with 3). Any ideas for a solution (formula or VBA)? Thanks in advance.
Example raw data and the desired filtered result for illustrative purposes:
1) if the current value is numeric and equal to the previous numeric value, replace with #N/A, otherwise keep current numeric value.
2) if the current value is numeric and greater or less than 0.5 difference from the previous numeric value, replace with #N/A, otherwise keep current numeric value.
3) if the previous value is #N/A or non-numeric, look up the closest previous numeric value above, and undertake 1) and 2).
Obviously 1) and 2) are very easy to achieve, but I'm struggling with 3). Any ideas for a solution (formula or VBA)? Thanks in advance.
Example raw data and the desired filtered result for illustrative purposes:
Book1 | ||||
---|---|---|---|---|
A | B | |||
1 | raw | filtered | ||
2 | 0.5 | |||
3 | 1 | 1 | ||
4 | 1.5 | 1.5 | ||
5 | 2 | 2 | ||
6 | 2.5 | 2.5 | ||
7 | 2.5 | #N/A | ||
8 | 3.1 | #N/A | ||
9 | 3.2 | #N/A | ||
10 | 3.3 | #N/A | ||
11 | 2.6 | 2.6 | ||
Sheet1 |