Excel 2007 : Macro to reformat data from 1 row into 2 rows

**Sazza** · 12-21-2011, 11:17 AM

Hi All,

I have a worksheet that looks like this :

---A ------- B ------- C -------- D ------ E --------- F --------
1 John ---- 1001------Day ----- 10.65 ---- Night ---- 3.33 ---
2 Mary ----1002 ----- Day ------11.55----- Night ---- 3.21 ---
3 Mark ----1003------ Day ------13.33 ---- Night ---- 1.23 ---
4 Pete ----1004 ------Day ----- 15.69 ---- Night ---- 5.66 ---
5 Nigel ----1005 ----- Day ----- 17.85 ---- Night ---- 5.22 ---

And i need to reformat it to look like this :

----A --------- B ----- C --------- D ----------- E --------- F --------
1 John ---- 1001------Day ----- 10.65
2 John ---- 1001------Night ---- 3.33
3 Mary ----1002 ----- Day ------11.55
4 Mary ----1002 ----- Night ---- 3.21
5 Mark ----1003------ Day ------13.33
6 Mark ----1003 ----- Night ---- 1.23
7 Pete ----1004 ------Day ----- 15.69
8 Pete ----1004 ----- Night ---- 5.66
9 Nigel----1005 ------ Day ----- 17.85
10 Nigel----1005 ---- Night ---- 5.22

I have thousands of rows and would like a macro to format these.
I have tried using the VBA located here :
http://stackoverflow.com/questions/3...-rows-in-excel

But this does not format them the way i need it and i cannot figure out how to change it correctly.

Can anyone help me here ?

Thanks,
Sarah

**JBeaucaire** · 12-21-2011, 01:50 PM

'ROWS TO COLUMNS
Here's a macro for expanding rows of data to multiple rows parsing out the values in the row in "groups" while duplicating the first few columns you indicate. There's a sample workbook too you could drop your data into and test it out.

Rows to Columns - Part 1B

When you run the macro, I'd expect the answers to the popups to be 3, then 2. Try that on a copy of your sheet.