I'm honestly a newbie here and have no idea what I'm doing. I've never even looked at VBA before. I don't also know too much about advanced features of excel, so I don't even know if this is the best route to go about doing this, so let me know if I'm way off-base here.
Basically I have a sheet that will have data copied and pasted into it, the source uses multiple names for the same thing. E.g. "Pennsylvania" might be "PA" or "Penn". I have another sheet with a table with 2 columns, column A is the known multiple spellings for a word, column B is the "Master name".
I want the script to go through the column with the names and then in an adjacent column, write a master list, wherein it puts the replaced terms and the ones that didn't need replacement. I would also need it to autoupdate every time after data is copied/pasted. Both the length of what is to be replaced and the master list could grow/shrink over time
example:
.........................................
known mistake ||| replacement
AAB ||| AAA
....................................
before script:
column of original names ||| Master column
AAB |||
BBB |||
CCC |||
.................
after script:
column of original names ||| Master column
AAB ||| AAA
BBB ||| BBB
CCC ||| CCC