Hi. I have a column A with 1s, 2s and 4s. It's easier to see the example I have below. If there is a 1 it needs to see if there is another 1 right above. If it doesn't it should copy it. When it sees a 4 and all above are 1 it has to continue with the same number but the range restarts. So when it sees a 1 it should copy the new corresponding number. When there is a 2 and above there is a 1 or 4 it should copy the previous number again for the last time. If there is a 2 again it should bring 0 now. Hopefully you will understand with the example because I did not explain it well! Thanks for any help.
| sample new1.xlsx | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | Desired | ||||
| 2 | 1 | 21 | 21 | ||
| 3 | 1 | 25 | 21 | ||
| 4 | 1 | 26 | 21 | ||
| 5 | 1 | 20 | 21 | ||
| 6 | 1 | 22 | 21 | ||
| 7 | 1 | 24 | 21 | ||
| 8 | 1 | 25 | 21 | ||
| 9 | 1 | 24 | 21 | ||
| 10 | 4 | 22 | 21 | ||
| 11 | 1 | 27 | 27 | ||
| 12 | 1 | 28 | 27 | ||
| 13 | 2 | 21 | 27 | ||
| 14 | 2 | 27 | 0 | ||
| 15 | 2 | 19 | 0 | ||
| 16 | 2 | 18 | 0 | ||
| 17 | 1 | 24 | 24 | ||
| 18 | 1 | 28 | 24 | ||
| 19 | 2 | 29 | 24 | ||
| 20 | 2 | 31 | 0 | ||
Sheet1 | |||||
