Imort multiple sheets from file, Look for number beside string(vlookup)

[wonka1234]

Hi,

1) I am trying to import all sheets from file that start with "CHT1".

2) I am only wanting to extract values next to "Current Month Principal for Reinvestment". Almost like doing a vlookup in each CHT1 sheet.

For example, looking in each sheet for this, regardless of cell position.

How can I import these values for each CHT1 sheet?

Thanks.

[binuacs · Accepted solution]

@wonka1234 One way of doing this

1. Update your input tool with the below configurations

2. Update your custom filter like below 

3. Update formula tool 

4. Update dynamic input tool like below

once all sheets in your output you can filter out the data you want

[wonka1234]

@binuacs 

1) For the dynamic input, do I still do <List of Sheet Names> 

2) Is there a way to search all these sheets? all the sheets im reading in doesnt have the same structure :(

However they all have the small image I showed above. "Current Month Principal for Reinvestment"  and 4 columns over. But could be in any cells!

[binuacs]

@wonka1234 If the schema is different you need to use the batch macro, attaching one for your reference

[wonka1234]

@binuacs  I dont have this version of alteryx, do you have a previous version?

I use 2021.2.1.35394

[binuacs]

@wonka1234 open the given .yxmd file into notepad or notepad++ then change the version to your version and save. 

[wonka1234]

@binuacs 

cant seem to open above file in notepad without it looking like weird characters.

[binuacs]

@wonka1234 the attached file is .yxzp which is a zipped file, first you need to unzip this file by double clicking the file, once you unzipp the workflow and the macro will be opening in a seperate folder with the same name as the zip file after that update the .yxmd file as mentioned above.

I updated the version to 2021 and attached here. double click the file and open in the designer

[binuacs]

@wonka1234 the above file is not working then go through the below post and download the given files

The Ultimate Input Data Flowchart - Alteryx Community entrée de données eingabedaten

[wonka1234]

@binuacs  can you explain what the macro does?