How to run a macro that starts selecting records at different row numbers

[WickM]

Hello - 

I have multiple files in a directory I'd like to run through a macro workflow. I have the workflow set to start at row 5 each time (using select records), but when I ran the macro, I realized that not every xlsx file has the header row starting at row 5. Sometimes, it is row 4 and maybe row 6 other times.

How do I tell my select records tool to start picking up data once it reaches a certain header row in each file? Each file should have the header row starting with the words "Posting date" or "Batch number". Ideally, I'd like to set a formula that says "Pick up all data once you see Posting date in a row".

Can someone please help with this? Thank you so much for your consideration.

WickM

[CharlieS · Accepted solution]

Hi @WickM 

Let's set up the macro to import everything, then use Alteryx tools to filter/format only the data we're interested in. I would go about this by using a Multi Row Formula tool to flag where the header/data start, filtering to just those rows using that are flagged, then using a Dynamic Rename tool to rename the fields using the header row of data.

Check out the attached example where you can plug in any one of the Text Inputs to see that the data is output in the same layout each time.

[WickM]

This was very helpful. Thank you so much for the quick, effective response!!