Function to add column rule before importing entire sheet

[yaoerpi]

Hi All,

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! 

[jdunkerley79]

Yes it's possible.

I suggest the following:

- First use an input tool to get sheet names

- You can then use a formula to make a reference to the specifc cell e.g. `Sheet1$A2:A2`

- Feed this into a dynamic input tool to read each of the value for the sheets

- Filter to sheets with condition

- Run another dynamic input to read data in (makes assumption sheets are same format other wise will need a batch macro)

[yaoerpi]

Hi there!

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.

Thank you so again for the help!!

[jdunkerley79 · Accepted solution]

Sorry is a fairly advanced Alteryx technique.

The formula tool specifies the range:

[Sheet Names] + "$A2:A2"

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.