Replacing multiple columns that have the same name

[srea541]

I have multiple columns that need to be replaced with another column with the same name - one such example is below:

I want to replace this column from source one:

with this column from source two: 

I know i could do this for one change using the formula tool and just making the 1st one equal the 2nd one, but how would I do the above for multiple columns without having to write alot of formulas?

Im currently using a join but this adds "right_" to the 2nd column and I would have to manually reposition them as well.

Thanks!

[binuacs]

@srea541 If your both data are in same position you can either add record id tool in both input files and join like below or join record position

[Jon-Knepper]

I just took a look at this and was going to suggest the solution provided by @binuacs.  Its a great way to just put a new column next to your existing data as long as that data doesn't need to be associated to a specific record.

[srea541]

@binuacs @Jon-Knepper in this case, the data file has lots of columns (there are lots more than just the ones in the screenshot below, and the join would put them right at the end of the file by default), so I dont think this would work?

[Jon-Knepper]

Is it possible that you can just eliminate the field using a select tool and union the rest of the fields together?

[srea541]

@Jon-Knepper I dont think that would work either - theres almost 500 rows in the full dataset and it would take quite some time to reposition each of the 60(ish) columns from the second data source :(

[Ladarthure]

Hi @srea541,

one way to do it would be to proceed in multiple steps 

• use a record ID to identify rows (both files)
• use a transpose on all the fields (both files)
• Use a join on record id and name 
• then you can do what you want
• if you want to get back to your original first just use a cross tab tool :-)

[srea541]

Hi @Ladarthure 

will this work if the structure of the datasets dont match?

The first dataset is the one with 500-something columns

The second one only has a handful which are to replace the same named ones in the first one.

Thanks!

[HomesickSurfer]

Hi @srea541 

Can you please share a sample dataset.

[srea541]

@HomesickSurfer no worries, please find attached.

The second input data tools values need to replace the ones in the first one, whilst ensuring the first one retains its structure - however I cannot use the formula tool to make the old one equal the new one as the full version of the second input tab has 20 more columns like the two already there - I would end up with too many formulas.

Thanks!