Extract data from table shuffled at different row number

bubblyjai1982

Hi All

Need your assistance !!

Using dynamic input tool I have extracted multiple files but facing a challenge in extracting the data available at different row and column.

Example from tab file 1

Tsec: PTXHKD00DBEPL01

Total: 550

Similar I want to extract same field from other files - The challenge is the table available in different files are not at same row number.

Input and output data attached for your reference.

Thanks

Extract Tsec and Total.xlsx

Workflow

Accepted answers

binu_acs

@bubblyjai1982 , for the file name, select option 5 from the dynamic input tool

All comments

Pilsner

Hello @bubblyjai1982,

I've tried to tackle your problem, please see my method below.

1) I tried to follow your initial step of reading in the data using the dynamic input tool.

2) Then I created a new column to identify each sheet separately. To do this, I used a multirow. Looking at the data, I could see that Total was always the last record for each sheet; hence, I used this to identify the end/ beginning of each sheet.

3) From each sheet, we want the total and TSec records, hence I used filters to identify these records

4) Now I had the total and Tsec for each sheet, I joined them back on to each other, using the "Sheet" column I created earlier.

5) Finally, some of the sheets mentioned the TSec code twice so I used a summarise tool, to deduplicate the data.

I have attached the workflow below. Please let me know if you have any further questions.

Regards - Pilsner

New Workflow1.yxzp

bubblyjai1982

Thanks for the response Pilsner !!

The logic of Multi row will not work because the data is not constant, I mean the no.of files varies and its not always 3 files.

Today I had 3 files tomorrow it will differ - based on trading

Thank you.

dreldrel

Are you able to give the name range in your files? You can read data by name range.
https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Can-Alteryx-input-excel-dynamic-named-range/td-p/654217

If this helps, please like the comment and select it as a solution. Thanks in advance

binu_acs

@bubblyjai1982 Are File1, File2, and File3 separate tabs in a single Excel file, or are they three separate Excel files? I assumed they were tabs within one file and created the workflow accordingly. If they are individual files, then the workflow will need to be adjusted

Combine_File_22092025.yxzp

bubblyjai1982

Hi Binu

Thanks for the response !!

Actually the input files are not in same sheet.

Using Dir tool I have extracted the different files and the logic you used until filter is same as mine but only difference is I don't have column File Name.

Sorry don't want to share the data due to compliance issue, request you to please tweak your logic without file name.

Many Thanks

binu_acs

@bubblyjai1982 , for the file name, select option 5 from the dynamic input tool

bubblyjai1982

Hi Binu - Thank you so much, my issue is resolved now.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703