how to convert one vertical alpha column to three columns alpha left to right

Hi all,

I have an alphabetized vertical column that I need to change to a three column alphabetized list reading left to right. Any ideas how to do this? Right now, I have transposed the vertical column to one horizontal row and am cutting it after every third name and pasting it on the next row, but this is time consuming and tedious. I also have to update this list periodically, so something that did this automatically would be awesome.

I would appreciate any help and ideas.

Thanks,
Danielle

You can do it with an INDEX formula. Can you post an example workbook? Then I'll be able to come up with a formula that suits your particular set-up.

Pete

Thanks Pete. I ended up using this formula =IF(INDIRECT(ADDRESS((ROW()-1)*3+COLUMN()+1,1,1,1,"Sheet1"))=0,"",INDIRECT(ADDRESS((ROW()-1)*3+COLUMN()+1,1,1,1,"Sheet1"))).

The important part being =INDIRECT(ADDRESS((ROW()-1)*3+COLUMN()+1,1,1,1,"Sheet1"))

Thanks for the quick reply and help!
-Danielle

Hi Danielle,

while I was waiting I put some names in column A starting with A1 and put this formula in C1:

=IF(INDEX($A:$A,(ROWS($A$1:$A1)-1)*3+COLUMNS($A$1:A$1))="","",INDEX($A:$A,(ROWS($A$1:$A1)-1)*3+COLUMNS($A$1:A$1)))

The formula was then copied across to D1 and E1, and then C1:E1 was copied down.

INDIRECT is a volatile function, which means it recalculates whenever any other cell needs to be calculated, so this could slow your workbook down if you have a lot of formulae which makes use of that function (and you have 2 in each formula).

Hope this helps.

Pete