Arranging single-row records across multiple rows

G

Greg Spencer

My toddler's playgroup needs to arrange worksheet data in a bizarre fashion
(one record per every 4 lines) from a conventional worksheet table with one
record per row.

Specifically... we need to take firstname, middle names, surname and
preferred name from consecutive columns of our main worksheet (e.g.
B2,C2,D2,E2)... and produce a sheet formatted so that the first column reads
as follows:

firstname (from B2)
middlename (from C2)
surname (from D2)
preferred name (from E2)
firstname (from B3)
middlename (from C3)
surname (from D3)
preferred name (from E3)

Repeat for up to 50 records...

Sadly, that's just the start of it: I've then got to get an address in one
of the adjacent columns (again over 4 rows per record) and calculated data in
other columns.

Pivot table doesn't really seem to help... and whilst I'm happy to work my
way through help files... I don't even know where to start looking!

Greg.
 
S

Sheeloo

What you need is a formula which returns when entered in a cell and copied
down... assuming you have your names in Sheet1
Sheet1!B2
Sheet1!C2
Sheet1!D2
Sheet1!E2
Sheet1!B3
Sheet1!C3
Sheet1!D3
Sheet1!E3

You can put an INDIRECT around it and get what you want...

One crude way is to enter on Sheet2
B in E1
C in E2
D in E3
E in E4 and repeat the pattern

Then enter this in F1
=I1&(ROUNDDOWN((ROW()-1)/4,0)+2) and copy down

Then in B2 you can have
=INDIRECT("Sheet1!" & J1) and copy down

I am sure someone else will come up with an elegant formula...sot hat you
can put everything in INDIRECT ...
 
S

Sheeloo

Thanks for your feedback...

DO post back if you comeup with an elegant solution.

You may try this macro
This will work upto 65K/4 rows of data
'-------------------------------
Sub copyname()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim LastRow

LastRow = Sheets("Sheet2").Cells(65536, "B").End(xlUp).Row
MsgBox LastRow
k = 2
For i = 2 To 100
For j = 2 To 5
Sheets("sheet1").Cells(k, 2).Value = Sheets("Sheet2").Cells(i, j).Value
k = k + 1
Next j
Next i
End Sub
'-------------------------------
 
S

Sheeloo

It is not my day today...

It will work for 100 rows unless your replace 100 by LastRow.

You can delete the MsgBox row
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Turning rows of equal number of columns into 1 continuous row 1
copy a formula within a row from a column on another worksheet 4
Extract duplicate rows to another worksheet 7
Need help with a multiple criteria formula 7
Populate formula results in cells to next unique result. VBA or Fu 1
Multiple rows converted to one row 3
named range row offset 5
need post-fix for data source jumbling row data in a consistent wa 1

Top