Harmonising Fields after Batch Macro Union

jay_hl

I have inputted a number of excel files through a batch macro. They all have slightly different schemas (field names) so the macro is set to union the results on output. 

This then ends up with multiple different fields for what is in reality the same field (eg Name, Names, FullName, Fullname) where lots of the rows are blank depending on what was in the source data.

Is there a quick and easy way to harmonise these to the same column?  My natural instinct would be to write a formula like the below:

If Name is not null, then Name, elseif Names is not null then Names elseif FullName is not null then FullName etc.

Given there are approximately 10 “real fields” across 30 variations I wondered if there was a best practice to tackle this and align all the data to a single column easier than nested ElseIfs

Jay

pedrodrfaria

Hi @jay_hl 

You have different ways to approach this.

If they are always the same file structure, you can use the Union tool to manually assign everything to the correct place.

If you know that columns will always say Quantity or Qty or Quant or QTYs, etc. you can use the Dynamic Rename and create a If Contains logic to change the name to a single name throughout all the tables.

You can also do a logic with the Dynamic Rename, Select and the interface Dropdown to dynamically select the column that represents that specific column, and use the dynamic rename and the select to rename it to the proper name and the select to only select that column.

Please let us know which way works best for you and then we can better support you.

Pedro.