Extract Data from table with multiple ranges

[Al Delgado]

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.

See sample file.

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.
Search by name: Mark Rusk
Company A
Company B

Search by Status: 2 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

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.

 

[jeffreyweir]

Hi Al. Re:

15 sets of company data and 8 departments

Is all the source data in the one table?

I think the best way of doing this is with a macro, which can then create your data dump. You can then point a PivotTable at the data, and filter it any way you like...including the example you've give above.

 

[Al Delgado]

Any suggestions on how to combine the 15 or so different ranges into one range of 5 columns, the length of the tables will vary as data is inputted?

 

[Al Delgado]

The source data is all in a single range, not a Excel Table.

 

[jeffreyweir]

Any suggestions on how to combine the 15 or so different ranges into one range of 5 columns, the length of the tables will vary as data is inputted?

yes...I have some code that should be able to be tweaked.

Just need a chance to look at it. You know any VBA? I could give you what I've got to date, so you can take a look at the approach if you want.

 

[Al Delgado]

I know enough to be dangerous, lets give it a shot.

 

[jeffreyweir]

Hi Al. Sorry for the delay...got sidetracked. The VBA that I would use to crack this challenge is at http://dailydoseofexcel.com/archives/2013/11/21/unpivot-shootout/ but it's going to require quite a bit of amending to fit your needs.

Question: in your sample file you don't have any department names, and just have some cell borders to denote one department vs the next.

What does your actual file look like?

Can you upload your actual input sheet - or at least a subset of it with some dummy data in it? Otherwise it's impossible to code up a macro to do this based on the information to hand.

 

[Al Delgado]

The matrix sheet is the input form. This is a subset of 4 ranges.