This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Thank you in advance for reading. I was hoping someone would have experience with the below problem.
I am using a batch macro to iterate through a number of named ranges across a number of Excel files. The schemas of the named ranges are different. I am trying to normalise the data in these named ranges (i.e. need to transpose the data), however I also need to be able to do this dynamically as there are over 60 named ranges.
Because the named ranges have different schemas, the "Key Fields" and "Data Fields" in the Transpose Tool alson need to change. Below are a few examples of what the named ranges look like. Key fields are marked in blue and data fields are in yellow.
Example of named ranges with different schemas
I have looked at a few threads on the community but have not been able to solve this problem. I would appreciate any help.
Dynamic doesn't necessarily mean that you don't have to guide it. Even with the daunting task of 60 inputs, A static workflow will be more efficient than a manual excel labor.
If I were building this macro solution, like the dynamic input I would ask for metadata about the sheets. Not only where are the named ranges, but also which fields are keys and which are data.
Before I build the macro I would consider if there are groups of inputs that could use an existing macro like the CReW wildcard excel or the 2015 Excel Gift Macro (on Alteryx gallery). Maybe setting up 6 groups would be a better investment if your time.
How often does the process change? How often do the incoming files change schemas? How often does the process run? Who will maintain this if I win the lottery?
Lots of considerations to be had.
Alteryx ACE & Top Community Contributor
Chaos reigns within. Repent, reflect and reboot. Order shall return.
Personally I think this can be made dynamic but that is with a couple of conditions.
There is always an error check field between the key fields and the data fields; which we can use as a reference point.
And secondly what you want the output from the batch and transpose too. Remember, because you have different key fields the output schema for reach batch is going to be different. So you may need to move any processing on each named range into the batch macro too.
You could potentially double transpose and to solve this problem. First around they key fields and then in its entirety.
If you were to post the actual xlsx file then I'm happy to give this a go if I can find time.