How to dynamically rename field name based on year

[ruangkhaw]

Hi All,

I have to union hundred of files in a directory. My problem is : each file has no column names that specify the year of each field, for example some files started a column with "FY 22" for April Fiscal year 2022 then the next column is "F12" for May Fiscal 2022 then "F13" for Jun Fiscal 2022 so on and so forth.

While some of the files starting the column with "FY 21" for April Fiscal Year 2021 and so on and so forth.

My plan is to create a batch macro for dynamic rename using IF THEN ELSE expression as picture below

However, I run to a road block where when the file doesnt have a column that start with "FY 21" then it will match the wrong Fiscal year.

Is it possible to write a formula where IF  "FY 21" then "F12" = May FY 2021 and "F13" = Jun FY 2021 if NOT "F12" concatenate with "FY 22" etc.

Any thoughts on how I can union all these hundred of files without matching the wrong columns?

Sorry I cant share the file because it's in my work directory.

Thanks!

[JarekSkudrzyk]

@ruangkhaw you are welcome:) I am glad I could help:)