Moving Row-Level Output data into Columns

**burghman787** · 02-07-2017, 03:02 PM

I have a scenario where I need to move additional data in rows with the same primary key into additional columns. I've pulled data from our internal database via SQL Server Mgmt. Studio, and need to roll up certain data points respective to each primary key (candidate ID).

For instance: each candidate ID may have multiple work history or education history data sets--I need to roll up each change in the data into new, separate columns (See attached for current format & desired format).

Each candidate ID (roughly 100,000 unique IDs) may have more than 2 sets of education history and work history--it could be any number, so the VBA script will need to account for those possibilities (i.e. run until candidate ID changes).
I'm hoping this is possible--the case for this is for a data migration between our legacy system & a new system that needs the data rolled up into additional columns.

**dflak** · 02-07-2017, 05:02 PM

Are these the only two tables you need converted?

**burghman787** · 02-08-2017, 09:10 AM

Yes, these are the only two tables (fortunately).

**dflak** · 02-08-2017, 10:44 AM

I tried posting this last night, but the site was down.

Here is something built for the two tables, but is expandable.

It makes use of excel tables, pivot tables and dynamic named ranges.

I have two pivot tables for each data set. One is simply a unique list of names (Pivot 1), the other is the data that results when you use this name in the filter (Pivot 2).

There are named ranges that overlay the Pivot 1s: EDU_Ids and WH_IDs. This is one way of doing it.

The code loops through the IDs, sets the pivot table filters, and copies and pastes the data into the results page. The data is appended to the end of the current working row.

A new row is started for each new ID.

You can copy / paste your actual data into the tables I set up and everything should work.

The macro to run is MakeDataMaster.