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

Al Del

Board Regular
Joined
Jan 9, 2007
Messages
112
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
BCDEFGHIJK
Depart 1StatusStatusStatus
Arnie JonesRejectedMark RuskSubmittedMark RuskNo Respones
Frank BrownRejectedJim ZuckSubmiittedAlex SmithSubmitted
Joe ThompsonNo ResponseNick StevesSubmittedArnie JonesSubmitted
Mark RuskOffer madeAlex SmithSubmitted
Hank Benco
Andy MarksSubmitted
Depart 2
Don CarrInterestedRoland CorpOffer made
Lou PancoNo ResponseEvan SpruceSubmitted
Pete DromeInterested
Depart 3
Depart 4
Sam BrightRejectedAlex Smith
Andy FruitNo 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
MNO
Search by name:Mark Rusk
Company A
Company B
Search by Status:No Response
Joe ThompsonCompany ADepart 1
Mark RuskCompany CDepart 1
Lou PancoCompany BDepart 2
Andy FruitCompany BDepart 4
Search by Depart:Depart 2
Don CarrCompany BInterested
Lou PancoCompany BNo Response
Pete DromeCompany BInterested
Roland CorpCompany COffer made
Evan SpruceCompany CSubmitted

<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
MNOPQ
CompanyDepartmentSelNameStatus
Company ADepart 1Arnie JonesRejected
Company AFrank BrownRejected
Company AJoe ThompsonNo Response
Company AMark RuskOffer made
Company ADepart 4Sam BrightRejected
Company BDepart 1Mark RuskSubmitted
Company BJim ZuckSubmiitted
Company BNick StevesSubmitted
Company BAlex SmithSubmitted
Company BHank Benco
Company BAndy MarksSubmitted
Company BDepart 2Don CarrInterested
Company BLou PancoNo Response
Company BPete DromeInterested
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
 

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
Back
Top