We have an excel file with more than 200 columns, for which a certain column name might be in column 4 and sometimes in column 5. However, the name of the column will not be different.
I need to automate some vlookups, but I am struggling as some of the columns might be in column 4 or column 10.
We are currently using the following approach:
- open excel application scope
- read range and store as dt
- for each row
- write cell with the vlookup syntax (based on cell names and not on column names) to write in excel (with an increment counter to go to the next row)
but this is not the ideal situation as we do not cover scenarios where the columns are in a different order.
Any ideas how we can make it more variable for the column name?