One Excel File, One Tab, Seven Tables

Question

Hi Everyone,

The input file I'm found with is an Excel sheet that has seven tables. Attached is a sample but here's what I'm struggling to figure out:

(1) I want to read the FX Rate table that's on G6:H8

(2) I want to read Table Two but only need Table Two data that in this sample will end on row 55 but will change every day

(3) Table will not always start on row 40 but will always start with the title 'Title of Table Two'

(4) I do not need any other data outside of FX Rate table and Table Two -- I do not need any of the little table of data like in B57:G59

I'm struggling to figure out how to bring in this file every day with so many variables. If anyone has any ideas I would deeply appreciate it.

Thank You,

Jessica

Sample Data_AC.xlsx

Gaurav_Dhama_ · Answer

In cases where your data is inconsistent with position, I would bring all the data into a single column and then work on identifying the pattern. Simply put what i am doing is, identifying where the new table starts, picking the table 2 position both start and end and leveraging that to get the data from table 2. Same for Fx Table.

Since I am working only with the position, it is dynamic in picking the correct table even if the position changes.

I have attached a workflow that will work with your data.

Dynamic Table Filter.yxmd