Extract dynamic table from multiple Excels

brocq_18

New Member
Joined
May 21, 2021
Messages
8
Office Version
  1. 2007
Platform
  1. Windows
I have 30+ Excel files in a folder, each containing various data and texts and one table at the bottom located around row 30.

Each table is preceded 2 rows above by the text "The list below displays the transaction level details based on the filters selected above".
The header is constant for all tables and has 18 columns but row numbers vary. Sometimes there is no table but the text "The list below displays the transaction level details based on the filters selected above" is still present.

All tables are ended with text below the last row stating "Report Run Date: DD MMM YYYY HH:SS" even when a table is not present.

How can I extract all these tables into one sheet?
Thanks in advance.
 

Attachments

  • delete.png
    delete.png
    7.7 KB · Views: 3
Last edited:

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Radoslaw Poprawski

Active Member
Joined
Jun 19, 2021
Messages
326
Office Version
  1. 365
Platform
  1. Windows
depends on ur layout,
if all the tables have the same headers then power query will do it.
but no sample provided so playing guessing game here
 

Forum statistics

Threads
1,141,073
Messages
5,704,138
Members
421,328
Latest member
mippy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top