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

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.

Custom Tools

Accepted answers

@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

ImortSheets_11_07.yxmd

All comments

@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

ImortSheets_11_07.yxmd

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!

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

ImortSheets_11_07.yxzp

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

I use 2021.2.1.35394

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

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

@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

ImortSheets_11_07.yxzp

@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

@binu_acs can you explain what the macro does?

@wonka1234 the input tool only returns the sheet names from the given excel sheet and feeding to the macro, the macro just uses the full path and the sheet names and read each sheets and stacks to one another like a union tool. Since you have different schemas only the bactch macro is able to read those records. if you look at the below post you will get much idea on this

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

Quick Links

Tags

This months top contributors

AkimasaKajitani 389

Hollingsworth 335