Excel transpose problem
Discussion
Sorry!
Pistonheads seems to be better than google when it comes to these type of stupid problems
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?
Pistonheads seems to be better than google when it comes to these type of stupid problems

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?
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
Remember when editing an array formula you need to use Cntrl + Shift + Enter to execute
Hope that helps
FG
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!Remember when editing an array formula you need to use Cntrl + Shift + Enter to execute
Hope that helps
FG
Gladly now some silly count equations don't borderline crash excel

Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff


