I have data in a tabular format, that I am using to track applicant information, that I would like to analyze. What is the best way to extract the needed data? The data is the same every 3 columns, after the first column and will have duplicate names and status.

Table.png

There are actually 15 sets of company data and 8 departments
The Sel column is for a lookup that enters the Status.
The table will be adding departments vertically and companies horizontally, so the solution needs to be dynamic
New entries will also be added to the existing structure.

I would like to generate the following results.

Table Search.png

I gotten as far as creating company ranges without blank rows, but can't figure out how to append the 3 ranges into one continuous range. Also don't know how to add the department and company name to the records.

Table Transform.png