Convert multiple rows to multiple columns

[Rob]

I need to analyze results of blood tests in patients for my research.
The lab has given me the dataset in an Excel file, but the results are
presented in a way that makes analysis impossible (for me) because
there are about 2000 patients' results. Can anyone suggest a solution?

The lab's file lists the results in this way:
A1= Name; B1= Date; C1= Result
A2= Smith (for example); B2= Date 1 (e.g. 3/12/2003); C2= 100mg/l
A3= Smith; B3= Date 2 (e.g. 3/13/2003); C3= 115mg/l
A4= Smith; B4= Date 3 (e.g. 3/14/2003); C4= 134mg/l
A5= Smith; B5= Date 4 (e.g 3/15/2003); C5= 102mg/l
A6= Jones; B6= Date1 (e.g. 5/1/2003); C6= 99mg/l
A7= Jones; B7= Date 2 (e.g. 5/2/2003); C7= 176mg/l
A8= Jones; B8= Date 3 (e.g. 5/3/2003); C8= 200mg/l
A9= Jones; B9= Date 4 (e.g 5/4/2003); C9= 150mg/l
etc..etc...

I need to have the results presented in this format:
A1= Name; B1= Date 1 result; C1=Date 2 result; D1=Date 3 result; E1=
Date 4 result; F1= Date 5 etc.
A2= Smith; B2= 100mg/l; C2= 115mg/l; D2= 134mg/l; E2= 102mg/l etc...
A3= Jones; B3= 99mg/l; C3= 176 mg/l; D3= 200mg/l; E3= 150 mg/l etc...

In other words, the actual date is not as important as Date1, Date 2
and Date 3 are in chronological order - I am looking at the change in
the blood result over the subsequent days after the baseline (Date 1).
Each patient has 5 to 7 associated blood results for the test done on
consecutive days. The lab's file has about 12,000 rows and 3 columns.
I need the same data in about 2000 rows and 8 columns.

Hope you can help.

Rob

 

[Fredrik Wahlgren]

Hmm. Unless you're good at writing macros, I can think of two solutions
1) Select cells C3:C5
2) Press ctrl C
3) Select cell A10 or similar
4) Select "Paste Special"
5) Use the transpose option
6) Press paste

Do the same with all the other patients. You want to select a enough to the
right to make sure you don't confuse the pasted entries with the real ones.
Some patients have 8 entries, right? Afterwards, you will need to clear
columns A2 :A5 and A7:A9. This is a manual approach which will take a fair
amount of time to complete

My other solution is this: Ask the laboratory how they created the Excel
file. Maybe it was created as a SQL query. If it was and if someone at the
lab knows SQL they may be able to make a different query that will give you
the result as you want it to appear

Best Regards,
Fredrik

 

[Rob]

Thanks for the suggestion Fredrik. I'll see how I get on. But I'll get
back to the lab again as well.

Robert

 

[Fredrik Wahlgren]

Thanks for the suggestion Fredrik. I'll see how I get on. But I'll get
back to the lab again as well.

Robert

Hi

I should have put the second solution first. Getting back to the lab to see
if thet can give you the result the way you want it is obviously the best.
Having to rearrange the data takes long and is potentially error prone.

/ Fredrik