I need some help with a macro to "fix" some data downloaded for me but
in a format my clients can't use. The first column is a label (county
names) in merged cells containing 2 rows, one column. I'd like to put
them into a single unmerged cell, still in Column A.
The second column (B) contains labels for the rows that I want to be
labels for the Columns, "Count" & "Col %", alternating every other row.

Col C contains first the data count, then the percentage of the state
total below it, then the next county count, then percentage, etc in
alternating rows. I'd like the count in column B next to the percentage
in Col C. Columns D and E have the same situation as column C, and I
want the percentages "pulled out" and put next to the counts. So it
looks like this:

Col A Col B Col C Col D Col E A (2
vert. merged "Count" xxx zzz bbb cells)
"Col %" yyy aaa ccc

and so forth.

What I want is:

Col A Col B Col C Col D Col E Col F Col G
"Count" "Col%" "Count" "Col %" "Total" "%"
A (in single xxx yyy zzz aaa bbb ccc
unmerged cell)

and so forth for the rest of the counties.

I hope this makes some sense. Can anyone help me with the code to move
the data into straight columns?
Thanks in advance.

I would turn on the macro recorder and do the following:

1. Turn on R1C1 reference style at Tools/Options
2. Unmerge all cells
3. Insert new columns where you the percentages are to be copied
4. Assuming that column A is now blank in 2nd row of each pair, in the new
columns put this R1C1 formula:

=IF(RC1="","",R[+1]C[-1])

Copy it down through the last row of data.

5. Select the entire used range and Copy it, then Edit/Paste Special,
and select Values
6. Select Column A
7. Edit/Goto/Special and select Blanks
8. Delete the selected rows.
9. Switch back to A1 reference style.

Then you can modify the code to locate the bottom row with code and fill the
formulas down the correct number of rows.

On 8 Apr 2005 10:25:50 -0700, "davegb" wrote:

I need some help with a macro to "fix" some data downloaded for me but
in a format my clients can't use. The first column is a label (county
names) in merged cells containing 2 rows, one column. I'd like to put
them into a single unmerged cell, still in Column A.
The second column (B) contains labels for the rows that I want to be
labels for the Columns, "Count" & "Col %", alternating every other row.

Col C contains first the data count, then the percentage of the state
total below it, then the next county count, then percentage, etc in
alternating rows. I'd like the count in column B next to the percentage
in Col C. Columns D and E have the same situation as column C, and I
want the percentages "pulled out" and put next to the counts. So it
looks like this:

Col A Col B Col C Col D Col E A (2
vert. merged "Count" xxx zzz bbb cells)
"Col %" yyy aaa ccc

and so forth.

What I want is:

Col A Col B Col C Col D Col E Col F Col G
"Count" "Col%" "Count" "Col %" "Total" "%"
A (in single xxx yyy zzz aaa bbb ccc
unmerged cell)

and so forth for the rest of the counties.

I hope this makes some sense. Can anyone help me with the code to move
the data into straight columns?
Thanks in advance.

Thanks, Myrna, worked great!