Extract Data from table with multiple ranges (edited from previous post)

[Al Del]

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.

Excel 2012

	B	C	D	E	F	G	H	I	J	K
Depart 1	Status	Status	Status
Arnie Jones	Rejected	Mark Rusk	Submitted	Mark Rusk	No Respones
Frank Brown	Rejected	Jim Zuck	Submiitted	Alex Smith	Submitted
Joe Thompson	No Response	Nick Steves	Submitted	Arnie Jones	Submitted
Mark Rusk	Offer made	Alex Smith	Submitted
Hank Benco
Andy Marks	Submitted
Depart 2
	Don Carr	Interested	Roland Corp	Offer made
	Lou Panco	No Response	Evan Spruce	Submitted
	Pete Drome	Interested
Depart 3
Depart 4
Sam Bright	Rejected	Alex Smith
	Andy Fruit	No Response
	Gareth Pugh
	Graham Herald

<tbody>
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Company A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Company B[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Company C[/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]Sel[/TD]
[TD="align: right"][/TD]

[TD="align: center"]Sel[/TD]
[TD="align: right"][/TD]

[TD="align: center"]Sel[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]

[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

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.

Excel 2012

	M	N	O
Search by name:	Mark Rusk
Company A
Company B
Search by Status:	No Response
Joe Thompson	Company A	Depart 1
Mark Rusk	Company C	Depart 1
Lou Panco	Company B	Depart 2
Andy Fruit	Company B	Depart 4
Search by Depart:	Depart 2
Don Carr	Company B	Interested
Lou Panco	Company B	No Response
Pete Drome	Company B	Interested
Roland Corp	Company C	Offer made
Evan Spruce	Company C	Submitted

<tbody>
[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

</tbody>

Sheet1​

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.

Excel 2012

	M	N	O	P	Q
Company	Department	Sel	Name	Status
Company A	Depart 1	Arnie Jones	Rejected
Company A	Frank Brown	Rejected
Company A	Joe Thompson	No Response
Company A	Mark Rusk	Offer made
Company A	Depart 4	Sam Bright	Rejected
Company B	Depart 1	Mark Rusk	Submitted
Company B	Jim Zuck	Submiitted
Company B	Nick Steves	Submitted
Company B	Alex Smith	Submitted
Company B	Hank Benco
Company B	Andy Marks	Submitted
Company B	Depart 2	Don Carr	Interested
Company B	Lou Panco	No Response
Company B	Pete Drome	Interested
etc.

<tbody>
[TD="align: center"]28[/TD]

[TD="align: center"]29[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]30[/TD]

[TD="align: right"][/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]31[/TD]

[TD="align: right"][/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]32[/TD]

[TD="align: right"][/TD]
[TD="align: center"]5[/TD]

[TD="align: center"]33[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]34[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]35[/TD]

[TD="align: right"][/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]36[/TD]

[TD="align: right"][/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]37[/TD]

[TD="align: right"][/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]38[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]39[/TD]

[TD="align: right"][/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]40[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]41[/TD]

[TD="align: right"][/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]42[/TD]

[TD="align: right"][/TD]
[TD="align: center"]7[/TD]

[TD="align: center"]43[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​