evenly distribute data over a number of columns?

[Tom Ogilvy]

Is this a one-time shot or are you going to work with the 100 pieces in a
single column and you want the multiple column version to exist as well and
automatically update when the source column is changed?

If so, will the two versions of the data be located on the same sheet. What
is the address/range of the single column data and will it always stay at
100 items or will the list grow? If it grows, will this include inserting
and deleting rows?

microsoft.public.excel.datamap??

Regards,
Tom Ogilvy
MVP Excel

Jason Clamp wrote in message <38354B7C...@powerup.com.au>...
>Heres the scenario: I have 100 pieces of data all in one column, ordered
>the way I want them. I want to have excel evenly distibute that data
>over 5 columns and keep it in order. So column A would run down to the
>20th row then the list of data would be continued at the top of column
>B. I could do it by hand, but I want excel to do this even column
>distribution automaticaly because I will be inserting data in various
>places quite often and I dont want to have to shift loads of data around
>just to even things out.
>
>For intest's sake, the sheet will contain a table of part numbers that
>are linked to an adobe pagemaker file and it will all need to be updated
>automaticaly and look neat.
>
>thanks for your help,
>
>_.·´¯(_.·´¯(_ JASE _.·´¯(_.·´¯(_
>
>

[David McRitchie]

Hi Jason,

Excel does not provide such a feature. You have to program it.

Please DO NOT POST to MULTIPLE newsgroups. The group you
should have posted to was microsoft.public.excel.programming if
you really must have equal length columns
which is where people post questions for macros. Multiple posting
is poor netiquette and reduces focus on your problem.
http://members.aol.com/dmcritchie/excel/posting.htm

Since you have only 100 lines to snake into 5 columns they
will fit on one page and macro could do this for you. The following
was for much more data.
http://members.aol.com/rexx03/excel/snakecol.htm

You could record a macro by cutting the multiple columns back
down to one column, sorting the data, adding 4 to the number of
rows and dividing by 5, then pasting or copying the rows into columns.
But what I would do would be to leave the data intact in one column,
sort if necessary. Then creating another spreadsheet and copying the
data into it. You must run the macro it is not automatic. If it were
automatic it would be with a lot of overhead.

On the otherhand, if you do not really require equal length columns,
but you know they will remain at 100 or fewer entries and will fit on one
page, it would be a lot easier to simpler
to have a second spreadsheet and print from it. Then you don't need a
macro solution.

=OFFSET(snakeC100!$A$1,0,0) =OFFSET(snakeC100!$A$1,20,0) etc.
=OFFSET(snakeC100!$A$1,1,0) =OFFSET(snakeC100!$A$1,21,0) etc.
=OFFSET(snakeC100!$A$1,2,0) =OFFSET(snakeC100!$A$1,22,0) etc.
=OFFSET(snakeC100!$A$1,3,0) =OFFSET(snakeC100!$A$1,23,0) etc.

HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://members.aol.com/dmcritchie/excel/excel.htm

Jason Clamp <jcl...@powerup.com.au> wrote in message news:38354B7C...@powerup.com.au...

[Jason Clamp]

[Denny Campbell]

Jason,

I think the following will do what you want subject to the following
limitations: Your data must start in row 1 and must be contiguous (no
blanks).

Begin by defining a dynamic named range. From the menu, Insert>Name>Define

In the name box type:
MyData

In the refers to box type:
=OFFSET(Sheet1!$G:$G,0,0,COUNTA(Sheet1!$G:$G),1)

Substituting the Name of the sheet containing your data for "Sheet1" and the
Column the data is in for "G". Be sure to keep them absolute with $.

Then in the upper left hand cell of your distribution area (it can be on any
sheet in the workbook), Paste the following formula:

=IF(OR(ROW(A1)>ROUNDUP(ROWS(MyData)/5,0),ROW(A1)-ROW($A$1)+((COLUMN(A1)-COLU
MN($A$1))*ROUNDUP(ROWS(MyData)/5,0))>ROWS(MyData)-1),"",OFFSET(MyData,ROW(A1
)-ROW($A$1)+((COLUMN(A1)-COLUMN($A$1))*ROUNDUP(ROWS(MyData)/5,0)),0,1,1))

Finally copy this cell 4 columns to the right and down enough rows to assure
that it will handle the maximum number of rows of data you expect. For
example if you copy it into 100 rows it will handle 500 rows of data.

With any kind of luck you should now see the columns as you want them.

HTH
Denny Campbell
Grand Rapids, Michigan

Jason Clamp wrote in message <38354B7C...@powerup.com.au>...