copy and shift column(s)

Hi all, I am new to this forum, looking forward to learn a lot!

My first question is this:

I want to use a formula that copies text from a cell and shifts it by one column to the right in the next row.
It would continue copying as much as there are filled cells, so for instance:

A1 (a) B1 (b)

the formula would automatically add

A2 (b) B2 (a)

if the first row contains more cells filled:

A1 (a) B1 (b) C1 (c) D1 (d)

the formula goes on, like this:

A2 (b) B2 (c) C2 (d) D2 (a)
A3 (c) B3 (d) C3 (a) D3 (b)
A4 (d) B4 (a) C4 (b) D4 (c)

any help would be appreciated!

Looks like you just need: Cell A2 formula:

Formula:

Please Login or Register  to view this content.

copied across and down.

Sorry, slightly more complicated: A2:

Formula:

Please Login or Register  to view this content.

copied across and down.

Try changing the commas to semi-colons.

Put this formula in A2:

=IF(COLUMN()>COUNTA($1:$1),"",IF(ROWS($1:1)>COUNTA($1:$1),"",INDEX($1:$1,MOD(ROWS($1:1)-1+COLUMN(A:A),COUNTA($1:$1))+1)))

then copy across and down as far as you like.

Note: you may need to change the commas ( , ) to semicolons ( ; ), depending on your regional settings.

Hope this helps.

Pete

Oops, I didn't refresh the screen before posting. Anyway, it's an alternative, which allows you to copy as far as you like.

Pete