I'm sure there's an easy way to do this. It might not even involve a
macro. I'm receiving data in the following crosstab format:

CountynameX acount bcount total
acount% bcount% 100%
CountynameY acount bcount total
acount% bcount% 100
and so forth.

I want the data in columns:

CountynameX acount acount% bcount bcount%
CountynameY acount acount% bcount bcount%

I don't need the totals or the 100% in my table at all.
What's the easiest way to accomplish this? The more automated the
better. I'll be doing it for several files every quarter.
Thanks!

This is what I would do.

Say your data is in A1:D999
Select A1:A999 (last row of data)
edit|goto|special|blank cells
type an equal sign (=) and hit the up arrow key
hit ctrl-enter

This should fill the empty cells with a formula that evaluates to the cell
above.

Now select column A.
edit|copy
followed by
edit|paste special|values

Now select column B.
data|filter|autofilter

Filter to show the "acount" text.
select those visible cells (including the header)
and right click on one of them|Delete Row|(yes to delete entire row prompt).

I'd add headers to row 1 and not put those text values in column B, but you
could insert a new column B and a new column D and fill them with the text you
want (just type in one value and drag down the column).

And then delete the total column.

davegb wrote:

I'm sure there's an easy way to do this. It might not even involve a
macro. I'm receiving data in the following crosstab format:

CountynameX acount bcount total
acount% bcount% 100%
CountynameY acount bcount total
acount% bcount% 100
and so forth.

I want the data in columns:

CountynameX acount acount% bcount bcount%
CountynameY acount acount% bcount bcount%

I don't need the totals or the 100% in my table at all.
What's the easiest way to accomplish this? The more automated the
better. I'll be doing it for several files every quarter.
Thanks!

--

Dave Peterson