I have a bunch of spreadsheets with info listed in multiple rows and columns, similar to the attached file. Each sheet represents a box with multiple files in it. The box number is listed 1 time in column A. Then column D starts the specific data for a file. Some rows have data in columns E, F and/or G. Some files have 1 or 2 rows of info, others may have 3, 4 or 5. (See attached). The last row of data for each file contains "Attorney Initial(s)" in column D and "Destroy Date" in column G .
What I'm trying to do is develop a process that can read the info and import it into a table where each "File" is in one row. Column Headers are:
Client # (the data in column G with numbers)