Find and Replace involving Multiple Columns input
I have to do a find and replace that is dependent on information from additional columns.
The use case is for rows where my Department column is not populated. To this end, I want to be able to update a specific department based on the account type in the row. ie Balance Sheet accounts go to one dept, Revenue to another and so on.
My assumption is I could easily accomplish the goal in Advanced Query with a reference table, but I am trying to minimize use of code of any kind. As an alternate, I envision the following:
- Create a copy of the Account column
- Strip it to the first column to assess type
- Merge with the Dept column
- Use Regex Find and Replace to map the blanks to the appropriate department
- Use Regex Find and Replace to clean up the extraneous account info
I would group all of the commands to highlight that it is all one step, but I think it is a lot of steps and might not be clear.
At the end of the day, I am looking for the best practice with minimal coding, to transform a column based on input from two or more columns.
For the above use case, here is some sample data:
The data needs to look like this:
Because Balance sheet accounts go to ZZZ, and Revenue Accounts go to AAA.