convert MANY columns to 3 rows

kowolo

New Member
Joined
Sep 28, 2009
Messages
39
I have a very large spread sheet with employee information. There are 18 columns and hundreds of row - one for each individual employee. I would like to break each row of information into three rows of six columns for each individual.

So insted of having to scroll across a long document for each person's information, I could see all their information on one screen and then would just scroll down to see each person's entire data.

For instance, instead of:
firstname lastname address city state zip phone gender DOB DOH CellPh BenPlan etc

I would have three rows for each person:

firstname lastname address city state zip
phone gender DOB DOH cellPh BenPlan spouse
etc etc etc etc etc etc

Can anyone help me with this. I know how to copy/pasteSpecial/transpose to convert columns to single rows, but how can I break the columns into multiple rows for each person.

Thanks,
Kowolo
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

pplstuff

Well-known Member
Joined
Mar 9, 2012
Messages
951
You could do this quite a few ways. A few thoughts off the top off my head would be:

1) Run a macro that inserts 2 rows between each record. Use a relative reference "= somecell" to pull down items 7-12 and items 13-18. Then "go to" -> blanks and paste the formulas.
2) a vlookup using pipes (this would need some helper cells that would look like =$a2&"|"&b$1... depending on the data)
3) using =index(,match(),match())

The question is WHY would you want your data to have one record in 3 rows? Simply because you don't want to scroll? It would make data analysis much more difficult.

I guess if you're not doing anything with the data except looking at it, 3 rows would be fine. But it makes for poor data structure...


Does your data have a primary key (a unique number identifying each employee)?
 
Last edited:

kowolo

New Member
Joined
Sep 28, 2009
Messages
39
I can't figure out the macro. I'm trying the code below, but it only inserts the rows two at at time instead of putting two rows between all 3000 rows at once. Do you know what might be wrong with this? I really appreciate your help.


Sub test()
Dim j As Long, r As Range
j = InputBox("type the number of rows to be insered")
Set r = Range("A2")
Do
Range(r.Offset(1, 0), r.Offset(j, 0)).EntireRow.Insert
Set r = Cells(r.Row + j + 1, 1)
MsgBox r.Address
If r.Offset(1, 0) = "" Then Exit Do
Loop

End Sub
 

pplstuff

Well-known Member
Joined
Mar 9, 2012
Messages
951

ADVERTISEMENT

I'm sorry, I'm advanced in formulas and how excel works, but a novice with writing VBA off the top of my head. :(

Hopefully, with a *bump* or two a VBA guy will see the post. I'm sure it's an easy script to write.
 

kowolo

New Member
Joined
Sep 28, 2009
Messages
39
Still trying to figure this out:

To make things easier, lets just say I want to change this:
FNAME LNAME TITLE ADDRESS CITY ST ZIP HIRE DATE GENDER
Jane Smith Director 123 Main Cleveland OH 44236 1/2/2011 Male

to this:
FNAME LNAME TITLE
ADDRESS CITY ST
ZIP
HIRE DATE GENDER

Jane Smith Director
123 Main Cleveland OH
44236 1/2/2011 Male

The actual number of columns I have is 18 and I want to put that data into three rows of six.

Any ideas?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,329
Members
414,445
Latest member
walramgo02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top