Rolling Row-Level Output data up into Columns

[burghman787]

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.

[Keebellah]

Hi there,
I know a person can ask whatever he/she wants and has his/her special reason for wanting it in a particular manner.

I am just wondering why you want to transpose a quite normal table to something which is unmanageable and very difficult to oversee.
If a candidate has had 10 jobs and each job has 5 fields of data you are looking at 50 columns excluding the Candidate ID and name.

I am sure that what you want can be done, maybe somebody will pick it up but I am really curious to know why this particular and in my honest opinion unmanageable lay-out?

[burghman787]

Hi Keebellah,

I can't disagree with you. Unfortunately, the import file for our new system requires that each candidate have one row of data, and it will read the column header to determine what field the data will be migrated into in the new database. It's messy, and I would prefer not to, but in order to bring over the data this is the way they require it.

I could certainly limit it to 3 jobs if it would make this actually feasible.

I'm trying to avoid doing manual concatenating/manipulation within Excel, so I was hoping I could find a VBA module to use to do this.

[Keebellah]

Any number of jobs is feasable but I was just curious as to the reason.
I'll see what I can do for you.
Probably something like SAP or other ...... database

[Keebellah]

I think this will do it.

Place the code in the worksheet's VBA

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[burghman787]

Thank you for your help with this!
Unless I'm doing something incorrectly, when running within VBA it puts all of the data in the worksheet into just the 2nd row, not onto each row respective of the candidate ID.

I've attached a sample workbook with 100 rows of data for each of work history & education history. Could you try and let me know if you're getting the same results?

[Keebellah]

Okay, I'll look, yes, I did NOT take into account the diffrent ID's, my mistake. I assumed just one cadidate at a time.
This (I think) will be easily solved.

Will download the file and get to it.
Sorry again.

[Keebellah]

Okay, got it (it think)

The same for both worksheets again.
Just let it run an keep the status bar in sight it shows the progress, even it excel says "Not responding"