Conditionally input dynamic range from multiple Excel files

[jbbg]

I'm fairly new to using Alteryx, but I have made several successful routines that consolidate all the data contained in multiple files that are consistently formatted using these tools:

I have one vendor report (therefore a report I cannot change) that I don't know how to consolidate across multiple files, though.  There are two complications that I don't know how to handle.

1. Sometimes the report is blank, in which case I want to ignore everything in the file.
2. When the report does have data, there will be a variable number of relevant rows followed by additional data that I don't want.

So I want a routine to look at all the "Vendor report*.xlsx" files (which I know how to do), determine if the file has relevant data (which I need help with), and if so, figure out how long the report is (which I need help with).

Below is a sample of the report when there is relevant data (colored orange).  The yellow cell A2 can be referenced to determine whether or not a particular file has data, and the other yellow cell can be used to determine the length of the data to consolidate (if there's a way to match that text and find the row number minus one).  I don't need Alteryx to pull in anything that isn't orange.

Below is an example with no data, but I don't know if it always looks like this (there might be a circumstance that produces data in the second part but not the first part):

What I want to do that I don't know how to program would be something like this:

IF A2 = "Pay Element Type: Deductions"
THEN
Import A4:H#
ELSE
'Do nothing
END IF

where # = [row with "Total Amount for Pay Element Type: Deductions" in column A] - 1

The closest solution I could find was here, but it seems to be either multiple files, or a specific range, not both: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Import-Multiple-Excel-Sheets...

Is anyone able to help me please?

Thank you!

[j_acon]

Why don't you create a batch macro the reads all the contents of the file regardless of whether there is data or not. The macro itself will process the data and output it, either empty fields if no data, or values if it finds data.

Then all you need to is pass the list of files to the macro to process.

[jbbg]

Thanks for your suggestion.  I've never done a batch macro in Alteryx before; how is what you're describing different than a non-macro routine that combines all the data from all the files?

Just to clarify, when there is data I care about, the same file has some junk underneath that I'm trying to exclude.  And when there isn't data I care about, there still might be junk in the file.  I dug up and attached another example file that might illustrate this better than in my original post (I tried to put in a screenshot but that doesn't seem to be working).  The amount is $0, but I don't have any guarantee that this would always be true.

So, whether or not I use a batch macro, it seems like combining all file contents would include a bunch of junk that I'd have to filter out, which would run the risk of accidentally removing something it shouldn't.

I just thought surely there'd be a better way, but if there's not, I can either accept that risk or do the data consolidation in VBA and hope it doesn't overwhelm Excel.

[j_acon]

Here a sample

Just point the Directory tool to a folder with the 3 files excel files you attached here.

[jbbg · Accepted solution]

Very cool, @j_acon !  Thank you so much for putting that together!

The macro you made solved my first question, and helped me narrow down my searches enough that I found the answer to my second question: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/how-to-dynamic-select-the-rows/td-p/63...

Here's what the macro ended up being after I edited it:

The formula on the Multi-Row Formula tool:

Thank you again for your help!