Thread: Migrating data from columns to rows...

Hi! I'm working on a project that involves fixing a DBA's mistakes. This DBA must have been especially dense!

What I'm looking at is a list of lessons (Lesson ID, Lesson Date, etc.). There is a column in the sheet called Listed Participants. This field has MULTIPLE participant ID's in it. The DBA should have listed each participant with a lesson ID as a single record, rather than clumping them all together.

I'm at the point now, where I need to separate each Participant ID, insert some rows, and use the lesson ID as the first column and the participant ID as the second column. I'm not sure if I need a lookup. I've already separated the participant ID column into multiple columns (one for each participant ID), using the Text To Columns feature. I have a column that counts the number of participant columns (this number is used to tell the VBA how many rows to insert) I can (through VBA) insert rows in the spreadsheet to accomodate for the new records, but I'm having difficulty telling the system what to copy and where.

Here's what I HAVE:

Lesson ID ------------------------------------------------------ Listed Participants
-----------------------------------------------------------------------------------------------------
2594 ----------------------------------------------------------> 1525, 1425, 4487, 6698
4796 ----------------------------------------------------------> 3468, 1711, 5689

Here's what I NEED

Lesson ID -----------------------------------------------------Participant ID
----------------------------------------------------------------------------------------
2594 --------------------------------------------------------->1525
2594---------------------------------------------------------->1425
2594---------------------------------------------------------->4487
2594---------------------------------------------------------->6698
4796---------------------------------------------------------->3468
4796---------------------------------------------------------->1711
4796---------------------------------------------------------->5689


Now, you ask why I don't just do this manually. I have 4455!!!!!! lessons with multiple participants and need to speed this up.

Any recommendations? Thanks so much! I don't expect code answers (I think I can handle the code), but maybe some insight into how to approach this in a way that doesn't involve throwing the DBA in a lake. Thanks again!

KellyJo

KatBloom,

Welcome to the Excel Forum.

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

Hi KatBloom. If you're familiar with VBA code, you should be able to loop through the array of Participant IDs without much trouble. Does the data in your spreadsheet actually look like what you've posted, including the dashes and greater than signs? Do you need to have the data on the same sheet or could you put it in a new sheet? If it were me, I'd use a new sheet to put the new format and then use either a counting method or the cells(rows.count, [pastecolumn]).end(xlup) method to input to the next empty row.

Hello KatBloom,

With 2 helper columns. See the attached.

KatBloom,

I posted the sample data

By what you posted we can not tell what worksheet/columns/rows the data is in.

A macro solution would be very easy to create. The Listed Participants cells can be Split, and new rows inserted, to accomodate your request.