Replace incorrect data cells from second source

Paul_s_Moody

Hi,

I'm attaching a very simple example workflow that "works" on the sample data but is impractical for use in the real world workflow. Hopefully there is a simple-ish way of performing what I'm trying to do.

The data is substantially cut-down from the files I'm acually using (25,000+ records with 200+ columns), but the simple example shows the problem.

My main data file has a few record/column cells that contain errors. The changes file contains the replacement values. For ease they are in the same excel workbook, but in practice are stored seperately.

In the real world, the main file would remain constant, and the changes file would increase in size as corrected values are sourced; therefore the number of columns containing corrected values may also increase.

I'm looking for a simple/dynamic way of replacing the erroneous values from main with the corrected values from changes, without having to "hard-code" the changes in a formula tool and then dropping the duplicated column, again "hard coded" using a select tool...

Thanks in advance!

InputData.xlsx

CorrectData.yxmd

BS_THE_ANALYST

@Paul_s_Moody here's one way to do it:

The objective in the workflow was to get this:

It matches the old values with potential replacements! Once we have this, we can say, if we have a replacement, replace the current value, if not, leave the value as it was. Afterwards, we pivot the table back to how it started using Crosstab

lookupANDreplace.yxmd