One column into three

[Andy Ward]

We have a table with about 40 columns and 120 Rows and we
need to split each column into 3 columns only for certain
rows - I'm guessing that we can't spilt a column only for
certain rows as this would mess-up the entire column/row
naming convention - so is there a way to automate the
spliting of a column into 3 rows (i.e column A becomes A,B
& C) and then merging each trio of cells (i.e. A1, B1, &
C1) into one cell in the row and retaining the data that
was in A1 and moving the data that was in B1 into D1 - for
all 120 rows and then repeating the process for each column

We can then go back and un-merge the cells where necessary

A B C D E . . .
1 X Y Z
2 U V W
3
4

Becomes

ABC DEF GHI JKL . . .
1 X Y Z
2 U V W
3
4

Hope that this makes sense

Many Thanks

Andy Ward

 

[Frank Kabel]

Hi Andy
though this is possible using a macro if you can define which rows you
want to change you may explain what your ultimate goal is?. I suggest
to avoid merge cells as they will cause problems (e.g. sorting,
filtering, etc)

 

[Andy Ward]

Currently each column in the chart refers to a show and
the rows contain many different things relating to each
show (i.e. name of director, designer, dates of production
etc) the majority of which are text based - it is unlikely
that the table will need to sorted or filtered.

We have about 5 or 6 rows which currently contain
allocated hours of time in each of our workshops and we
would like to expand this to show the hours that were
originally budgeted, the hours which we currently expect
to use and the number of hours used to date - hence
needing 3 columns under the main column for the show

Hope that this explains

Many Thanks

Andy