I have six columns on a worksheet that need to generate another worksheet with
the data in the columns placed in a particular order. The columns or fields in
the data worksheet are Heading, R-Squared, No. of Observations, Variables, Coef
and StdErr from left to right.

The report worksheet will have a heading in cell A1 taken from the Heading
column of the data worksheet, skip a row, then the term R-Squared will appear in
cell A3 with each unique value from R-Squared appearing in cell A4, A5, A6, etc.
Then in cell B3 will appear the term No. of Observations and from that data


column each unique value will appear in cell B4, B5, B6, etc. R-Squared and No.
of Observations will have the same number of unique values in their column per
Heading so they will line up in the same number of columns.

Cell A5 will have the first variable (e.g. Wealth "control" group)with its
corresponding Coeff and Stderr values lying under its related R-Squared/No. of
Observations column in Cell B5 and B6 then in Cell C5 and C6 then Cell D5 and
D6 and so on if necessary. The next variable will occur two cells below in
Column A at Cell A7 with its corresponding Coeff and Std Err values for the 1st
R-Squared/No. of Observations column occurring at B7 and B8 then the next Coeff
and Std Err at C7 and C8 and so on.

So if the data sheet columns are aligned Heading->R-Squared->No. of
Observations->Variables->Coef->StdErr then the report version is aligned as
below:

Heading (A1)

R-Squared(A3)->0.0378(B3)->0.0526(C3)->0.0743(D3)
No. of Observations(A4)->4065(B4)->4026(C4)->4018(D4)
Variable 1(A5)->0.083*(B5)->0.035(C5)->0.028(D5)
blank cell(A6)->0.042(B6)->0.043(C6)->0.041(D6)
Variable 2(A7)-> and so on...

I've attached a file called Remap.xls which will more
closely show the remapping of the values to the report format.

If done for each unique heading, these should result in about 20 different
reports or worksheets.

An important note is that the same number of variables will not exist for all
R-Squared/No. of Observations values so you may have 19 variables listed in a
report but only the first two columns for R-Squared/No. of Observations will
have values for all 19 while the third column may only have 11 values.

I tried doing this in a pivot table but it doesn't quite capture the look though
it can come close. If anyone has a methodology for doing this in VBA, I would
love to see what they'd recommend.

Thanks,
Reinaldo

Attached Files

Remap.xlsx (75.1 KB, 8 views)