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
<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>
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
<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>
Excel 2012
<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>
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