Transforming Multiple Rows into 2 columns

[wilsonds]

I have a range of data in rows that I want to transpose into two columns
in Excel 2000. The first row is the header row that I want to repeat in
the left-hand column. Any ideas how to do this where I can specify
number of original rows to be converted (3 rows or 300 rows) I but can
use the same formula/macro with say a variable adjustment?

I thought about Edit->Paste Special - > Transpose – but I need the data
in 2 columns and I need the first row to repeat each time on the left.


An example of what I’m looking to do is below

ORIGINAL
ID FIRST LAST ZIP
1 Bob Smith 63101
2 Jane Doe 63105

NEW TRANSFORMED DATA

ID 1
FIRST Bob
LAST Smith
ZIP 63101
ID 2
FIRST Jane
LAST Doe
ZIP 63105

Thanks in advance,
Darryl

 

[Harlan Grove]

wilsonds wrote...
....

An example of what I'm looking to do is below

ORIGINAL
ID FIRST LAST ZIP
1 Bob Smith 63101
2 Jane Doe 63105

NEW TRANSFORMED DATA

ID 1
FIRST Bob
LAST Smith
ZIP 63101
ID 2
FIRST Jane
LAST Doe
ZIP 63105

Uncrosstabbing. The general approach uses INDEX, INT, MOD, COLUMNS and
ROWS functions. For my conventience, I'll assume your original data
range is named D and the top-left cell of the result range is G1.

G1:
=INDEX(D,1,1+MOD(ROWS(G$1:G1)-1,COLUMNS(D)))

H1:
=INDEX(D,2+INT((ROWS(H$1:H1)-1)/COLUMNS(D)),
1+MOD(ROWS(H$1:H1)-1,COLUMNS(D)))

Select G1:H1 and fill down as far as needed.

 

[wilsonds]

This worked!! - Great! one follow-up question...
I want to create a third column (it's the key for the transformed row
for import to Access table – forgot this in original request) that is
based upon column A value for each row - So I want to create a third
column (first of the three) that has a static value in a column for all
entries of the uncrosstabbed row - value comes from ID column in each
row. The static value for column is added to the sample data below.

ORIGINAL DATA - REVISED

ID FIRST LAST ZIP
1 Bob Smith 63101
2 Jane Doe 63105

TRANSFORMED DATA WITH THIRD COLUMN – STATIC DATA

1 ID 1
1 FIRST Bob
1 LAST Smith
1 ZIP 63101
2 ID 2
2 FIRST Jane
2 LAST Doe
2 ZIP 63105

Thank you,
Darryl

 

[Harlan Grove]

I want to create a third column (it's the key for the transformed row
for import to Access table – forgot this in original request) that is
based upon column A value for each row - So I want to create a third
column (first of the three) that has a static value in a column for all
entries of the uncrosstabbed row - value comes from ID column in each
row. The static value for column is added to the sample data below.

ORIGINAL DATA - REVISED

ID FIRST LAST ZIP
1 Bob Smith 63101
2 Jane Doe 63105

TRANSFORMED DATA WITH THIRD COLUMN – STATIC DATA

1 ID 1
1 FIRST Bob
1 LAST Smith
1 ZIP 63101
2 ID 2
2 FIRST Jane
2 LAST Doe
2 ZIP 63105

Same assumptions/setup as before.

G1:
=1+INT((ROWS(G$1:G1)-1)/COLUMNS(D))

H1:
=INDEX(D,1,1+MOD(ROWS(H$1:H1)-1,COLUMNS(D)))

I1:
=INDEX(D,G1+1,1+MOD(ROWS(I$1:I1)-1,COLUMNS(D)))

Select G1:H1 and fill down.