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.
I'm trying accomplish something really straightforward - to only read data that has certain value iin certain columns before I compile data from multiple excel sheets. Be more specific, I hope my tool can add certain filter to make sure all the inputs are using the standard template without filtering out any data, kinda like a "check" before any further. An easy scenario could be out of several input sheets, it only reads the sheet that has "apple" on cell A2 and splits out a list of unqualified tab names.
Is there any solution already available? Any thoughts? Thanks everyone for your time to look into this!
I tried my best to incorporate what you built into my workflow, however it's still pretty challenging for me to understand some of the connections.
I put together a demo which has exactly the function I'm looking for and some testing data, and the ideal output should capture information from vegetable file that has a "KEY" in cell D2 and a new column identifying data source. Would you mind taking a look and letting me know what did I miss? I'm a little bit confused with the first dynamic tool you have and how it is able to pull information from the identified cell.
You can change the A2:A2 to whatever cell you need to read (e.g. D2:D2). The output is something like Sheet1$A2:A2
This is then set as the query for the Dynamic input. Alteryx allows you to read sub-ranges from sheets if you wish (it's just a little hard to enter in current UI).
As I know I am reading one cell per sheet and the order is preserved I am then using a Join by Record Position to reattach the sheet name.
The second dynamic input is then just reading the entire sheet but again you could choose to read specified range as needed. Due to the varying formats I moved to a batch macro to read the single cells in.
I have built up a sample around what you supplied.
Have a look through and ask me any details you need.