Extract Data from table with multiple ranges

Al Del

Board Regular
Joined
Jan 9, 2007
Messages
112
[TABLE="width: 110"]
<tbody>[TR]
[TD][TABLE="width: 110"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
I thought I had entered the HTML correctly, using MrExcelHTML, but apparently not, the spreadsheet showed up in my preview, any thoughts/

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 staus.

HTML:
Excel 2012BCDEFGHIJK3Company ACompany BCompany C4Depart 1SelStatusSelStatusSelStatus54Arnie JonesRejected3Mark RuskSubmitted2Mark RuskNo Respones64Frank BrownRejected3Jim ZuckSubmiitted3Alex SmithSubmitted72Joe ThompsonNo Response3Nick StevesSubmitted3Arnie JonesSubmitted85Mark RuskOffer made3Alex SmithSubmitted9Hank Benco103Andy MarksSubmitted111213Depart 2147Don CarrInterested5Roland CorpOffer made152Lou PancoNo Response3Evan SpruceSubmitted167Pete DromeInterested171819Depart 3202122Depart 4234Sam BrightRejectedAlex Smith242Andy FruitNo Response25Gareth Pugh26Graham Herald2728[CENTER][COLOR=#161120][B]Sheet2[/B][/COLOR][/CENTER]

I would like to generate the following results.

HTML:
Excel 2012PQR4Search by name:Mark Rusk 5Company A6Company B78Search by Status:2No Response9Joe ThompsonCompany ADepart 110Mark RuskCompany CDepart 111Lou PancoCompany BDepart 212Andy FruitCompany BDepart 41314Search by Depart:Depart 215Don CarrCompany BInterested16Lou PancoCompany BNo Response17Pete DromeCompany BInterested18Roland CorpCompany COffer made19Evan SpruceCompany CSubmitted[CENTER][COLOR=#161120][B]Sheet1[/B][/COLOR][/CENTER]

There are actually 15 sets of company data and 8 departments
The Sel column is for a look-up 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 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.

HTML:
Excel 2012UVWXY28Company ADepart 14Arnie JonesRejected29Company A4Frank BrownRejected30Company A2Joe ThompsonNo Response31Company A5Mark RuskOffer made32Company ADepart 44Sam BrightRejected33Company BDepart 13Mark RuskSubmitted34Company B3Jim ZuckSubmiitted35Company B3Nick StevesSubmitted36Company B3Alex SmithSubmitted37Company BHank Benco38Company B3Andy MarksSubmitted39Company BDepart 27Don CarrInterested40Company B2Lou PancoNo Response41Company B7Pete DromeInterested42etc.[CENTER][COLOR=#161120][B]Sheet1[/B][/COLOR][/CENTER]
 
Last edited:

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