So with help from the well of knowledge here, I was able to tackle my first issue of changing the first number in a value from one number to another, but now I'm looking to expand on the formula to handle a VLOOKUP to column A for a specific identifier "beta". If "beta" is present in the Type A column row & cell, then I want to apply the formula. If "beta" is not present (in this case "alpha" is), then simply copy the value from the Old Value B column cell to the New Value C column cell f the same row.
Any help you can provide to get me there would be much appreciated. Here is the current formula and an example data set:
=--REPLACE(J29,1,1,LEFT(J29,1)+OR(--LEFT(J29,1)={1,2,3}))
This works to change the first digit from the Old Value column cell from 1 to 2, 2 to 3 and 3 to 4 when I manually filter the Type A column to "beta" then apply the filter and copy the formula down.
I'm hoping someone can assist with a VLOOKUP to the current formula. I do need to copy ZERO as well if present. The current formula handles that, but need it to also copy ZERO when beta is not present and the value is ZERO in the Old Value column cell.
Hope this all makes sense.
Any help you can provide to get me there would be much appreciated. Here is the current formula and an example data set:
=--REPLACE(J29,1,1,LEFT(J29,1)+OR(--LEFT(J29,1)={1,2,3}))
This works to change the first digit from the Old Value column cell from 1 to 2, 2 to 3 and 3 to 4 when I manually filter the Type A column to "beta" then apply the filter and copy the formula down.
I'm hoping someone can assist with a VLOOKUP to the current formula. I do need to copy ZERO as well if present. The current formula handles that, but need it to also copy ZERO when beta is not present and the value is ZERO in the Old Value column cell.
Hope this all makes sense.
A | B | C | |
1 | Type | Old Value | New Value |
2 | alpha | 0 | |
3 | alpha | 20695 | |
4 | alpha | 20695 | |
5 | alpha | 20695 | |
6 | alpha | 20695 | |
7 | beta | 10660 | 20660 |
8 | beta | 0 | 0 |
9 | beta | 0 | 0 |
10 | beta | 0 | 0 |
11 | beta | 30139 | 40139 |
12 | alpha | 40139 | |
13 | alpha | 0 | |
14 | alpha | 0 | |
15 | beta | 30139 | 40139 |
16 | beta | 0 | 0 |
17 | beta | 0 | 0 |
18 | beta | 0 | 0 |