convert MANY columns to 3 rows

[kowolo]

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

 

[pplstuff]

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)?

 

[kowolo]

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

 

[kowolo]

I am putting the number 2 in for the "rows to be inserted"

 

[pplstuff]

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]

OK, thanks for your help.

 

[kowolo]

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?