Column to row

[BME_Guy]

Hi,

I have a list of patients. Each patient has at least 2 scores (each in their own cell) in a row next to the patient ID. They can also have more scores listed below this row but all the scores are in the same collumn.

For example:
A1: pt 1 A2: score 1 A3: score 2

It can also be (more commonly):

A1: pt 1 A2: score 1a A3: score 2a
B1: --- B2: score 1b B3: score 2b
etc.

I need these scores listed in rows next to the patient.

Using the second example, I need it to be like this:

A1: pt 1 A2: score 1a A3: score 2a A4: score 1b A5: score 2b

It is important to note that each patient can have a variable number of rows with scores below their ID. So one patient might need to have 10 new cells (5 sets of scores) listed in their row. How would I do this?

Thanks for your help

[BME_Guy]

Anyone? Do i need to clarify anything?

[JBeaucaire]

Post up an actual set of sample data showing 3-4 "sets" of data, however many you need to properly represent ALL the problems you need to overcome. In other words, don't dumb it down, make sure there's enough sample data that a solution works for your whole data set.

Then add a second sheet showing your actual desired results from the sample data.

Must this be a formula solution only, or would an on-demand macro be ok to create the newly formatted set of data?

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

[BME_Guy]

Thanks for the reply! They are attached. Either macro or formula will work.

[JBeaucaire]

I have a macro that may be "ready to use" for merging rows of data to one row matching for column A.

Columns to Rows - Part 2

I just removed the first two blank rows from your data sheet so the data starts in A1, then adjusted the macro to put in the missing codes in column A so the consolidation routine works as is.

Please Login or Register  to view this content.

Download the sample and run the Consolidation ,macro on the data in the first sheet.

[stanleydgromjr]

BME_Guy,

Detach/open workbook BME_Guy - EF745376 - SDG12.xls and run macro TransposeScores.

The macro creates a new worksheet Results with the data transposed.

Sorry, having a problem attaching the workbook.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Then run the "TransposeScores" macro.