Excel 2007 : Merging data horizontally vs. vertically

**enhydra** · 12-12-2010, 03:17 PM

Hello Everyone,

I have been tasked to modify an excel output of performance data on a weekly basis. The data is sampled every minute from a computer that has multiple processors. It will display this data twice, once for each processor vertically. Currently I’m completing the task of reformatting the data in a horizontal format with a few changes to the headers manually. I’ve tried working out a macro to complete this process automatically but it is beyond my understanding of excel. I’m sure I’m not explaining this very well so I have attached two sample workbooks showing the original workbook and the modified workbook. Hopefully some can help or at least give me a clue where to start. I’m so tired of doing this; I’m just about ready to quit my job.

Thanks much,
E

**Richard Buttrey** · 12-12-2010, 03:47 PM

Hi,

One way would be to use the following formulae in the Original and copy down for half the number of data rows.

G1: =OFFSET(A$1,ROW()*2-3,0)
H1: =OFFSET(B$1,ROW()*2-3,0)
I1: =OFFSET(C$1,ROW()*2-3,0)
J1: =OFFSET(B$1,ROW()*2-2,0)
K1: =OFFSET(C$1,ROW()*2-2,0)

HTH

**enhydra** · 12-12-2010, 04:13 PM

Thanks for the reply Richard.

Maybe I wasn't very clear, I'm sure I wasn't clear. I'm not just trying to change headers, I'm trying to change the data as well. Maybe I don't understand your answer, can you elaborate more.

Thx
E

**davesexcel** · 12-12-2010, 05:59 PM

Try this,
Click the button go to sheet 2 to see the results

**Richard Buttrey** · 12-12-2010, 06:16 PM

Hi,

Small typo, the formulae should have been placed in G2:K2 not G1:K1

If you put those formulae in the G2:K2 cells on Sheet1 of the original workbook and then copy G2:K2 down as far as row 26, you'll find the data has been split as you originally requested.

Rgds