I want to make about a hundred changes in a column on a spreadsheet. These changes have to be remade each time a new copy of the original comes. The changes are spread over several thousand rows.
The first solution that came to mind was to just use VLookup. Bad column in A, Good column in B. Start by copying A to B. Fix the entries in B. This requires fixing some mistakes multiple times. Ok, Search and replace in a limited range.
This works, but isn't very robust.
If I fixed Eleagnus to Elaeagnus for Eleagnus commutata and for Eleagnus augustifolia, I have to fix it again when my supplier introduces Eleagnus 'Silverleaf'
I found a partial solution to my make a mass of changes in a column problem.
If I use the yellow formula, it implements the yellow changes. Nesting that using B10,C10 -- the pink formula -- adds the changes in pink. Nesting that in another substitution() applies adds the changes in green.
This gets unwieldy.
At present my solution is messy, and requires at minimum a new tab. Assume the neat stuff starts in Row 4, and we use the first 3 for labels and explanations.
- Copy the column from the source into column A, use an array formula e.g.
- Create the substitute expressions in columns B and C
- Put the messy substitute array formula in D4
- If N is the number of substitutions in my formula, then columns E,F are copies of BC, but starting N rows further down. For N=10
E = ArrayFormula(B14:B) F = ArrayFormula (C14:C)
- Copy D4,E4,F4 to G4,H4,I4
- Repeat until you have exhausted the expression list.
Copy the last column to where you want it.
(In passing N=40 is quite reasonable. I create it in a text editor.)
Is there an alternative in google sheets to the nested substitutions.
In essence I want to:
FOR $counter = 4 to N ArrayFormula(substitute(A4:A, B$counter, C$counter)) End FOR
One fairly straight forward solution is do it in perl. This makes my solution non-portable. Few of my people are perl users.
Is there an elegant way to do this in sheets?