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.
- Sometimes the report is blank, in which case I want to ignore everything in the file.
- 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"
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?