MACRO - Column Name Mismatch

Question

I have 2 Macro of which the first one imports Multiple File sheet names and the second Import Multiple schema flat files Macros.

Importing File 1 & File 2 and each file has one Tab called F1.

The issue I'm facing is on File 1 the third column is Called STATUS and File 2 the Fourth column is called STATUS NEW

Expecting solution for

- Firstly how to Handle the STATUS & STATUS NEW Column name changes ( so that i have one column called status)
- Secondly on file 2 there where new column added will that be an Issue when we do joins further down the workflow.

Attached the Macro along with the Input Data and Sample workflow.

Thanks

Column Renaming.yxzp

File1.xlsx

File2.xlsx

suby · Accepted Answer

Thanks for your solution just to clarify on the Formula Field do i need to Select the existing STATUS column and then apply the formula or I have to create a new column Called STATUS.

I have hundreds of columns but there are quite few columns which are not aligned is this the only approach to handle this so if we want to change the multiple columns then is there a smart way of doing it.

Many Thanks

JoaoLeiteV · Accepted Answer

In the example I've made I used the already existing "STATUS" to update the values, if you have other columns with the same problem, you can do the same for each of them.

About best approach: If your data has aligned columns, you could unite them by Position, but as some files may have more columns, this would not be optimal.

I tried using another solution:

1. Using a RecordID to number the lines.

2. Transpose tool (Where Record ID is a Key Column) to change columns to rows.

3. Formula to find everything that contains "Status" and change it to "Status".

4. Crosstab to change data back into columns (RecordID is set as a Group Data, "Name" is header, "Value" are Values, Method is Concatenate)

This way, even if you have 2 or more STATUS with different names, they would all be considered. You still would need to do this formula for every column that needs to be readjusted.

Also, beware of columns that may be renamed wrongly, due to having a similar name.