Was wondering if there is a simple way to re-arrange data with some VB code.
I have much more data than in the following example.
So this could actually save several hours of work.

Thanks in advance, Mark

Have data in this format:

BOB DAVE JIM HAROLD
1 33 31 15
19 23 22 99

I need it in this format:

BOB
1
19
DAVE
33
23
JIM
31
22
HAROLD
15
99

a formula would work just a nicely

=OFFSET(Sheet1!$A$1,MOD(ROW(),3),INT(ROW()/3))

copy down then copy /pastespecial values



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(Mark) wrote:

Was wondering if there is a simple way to re-arrange data with some VB
code. I have much more data than in the following example.
So this could actually save several hours of work.

Thanks in advance, Mark

Have data in this format:

BOB DAVE JIM HAROLD
1 33 31 15
19 23 22 99

I need it in this format:

BOB
1
19
DAVE
33
23
JIM
31
22
HAROLD
15
99

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook:

=ArrayReshape(A1:D3,12,1,1) array entered into a 12-cell column

Alan Beban

Mark wrote:
Was wondering if there is a simple way to re-arrange data with some VB code.
I have much more data than in the following example.
So this could actually save several hours of work.

Thanks in advance, Mark

Have data in this format:

BOB DAVE JIM HAROLD
1 33 31 15
19 23 22 99

I need it in this format:

BOB
1
19
DAVE
33
23
JIM
31
22
HAROLD
15
99

Thanks. That is so simple and cool. You don't have to understand
gravity to enjoy it!

I would like to know why the formula doesn't return the contents of cell
A1. It looks like it starts with A2.

Mark Roach
Technical Resources Manager
The Oil & Gas Asset Clearinghouse
Houston, Texas

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

=OFFSET(Sheet1!$A$1,MOD(ROW(),3),INT(ROW()/3))

the offset has to start somewhere and you cant up or left from a1 :)
row counts are 1 based, so I should have made a small adjustment

=OFFSET(Sheet1!$A$1;MOD(ROW()-1;3);INT((ROW()-1)/3))

cheerz!

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Mark Roach wrote:

Thanks. That is so simple and cool. You don't have to understand
gravity to enjoy it!

I would like to know why the formula doesn't return the contents of cell
A1. It looks like it starts with A2.

Mark Roach
Technical Resources Manager
The Oil & Gas Asset Clearinghouse
Houston, Texas

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!