Excel transpose problem
Author
Discussion

citizenmtb

Original Poster:

1,495 posts

209 months

Wednesday 19th December 2018
quotequote all
Sorry!

Pistonheads seems to be better than google when it comes to these type of stupid problems biggrin

I've a list of items in column A, they are a concat of strings from sheet 1 columns, runs for about 110 rows. I've then used transpose to copy that list across row 1 using "=TRANSPOSE(A2:A114)"

For some reason transpose has run off into infinity and created a bazillion columns which is choking the workbook. I can't delete them as it error's that the array can't be modified.

Does anyone know how i can fix this and get rid of the excess columns?

FunkyGibbon

3,864 posts

295 months

Wednesday 19th December 2018
quotequote all
Having just had a play with this function, when you create the array function you need to highlight a finite number of columns to transpose to that is the same (or slightly more) as the number of rows you are transposing. If you highlight the entire row it will populate all possible columns (16384 in my case) and when it does not match data from the original column you just get #NA.

Remember when editing an array formula you need to use Cntrl + Shift + Enter to execute

Hope that helps

FG

SidJames

1,399 posts

264 months

Wednesday 19th December 2018
quotequote all
Copy and paste the original row formula using the option "transpose"?

citizenmtb

Original Poster:

1,495 posts

209 months

Wednesday 19th December 2018
quotequote all
FunkyGibbon said:
Having just had a play with this function, when you create the array function you need to highlight a finite number of columns to transpose to that is the same (or slightly more) as the number of rows you are transposing. If you highlight the entire row it will populate all possible columns (16384 in my case) and when it does not match data from the original column you just get #NA.

Remember when editing an array formula you need to use Cntrl + Shift + Enter to execute

Hope that helps

FG
Thanks, that's solved it!

Gladly now some silly count equations don't borderline crash excel laugh