# Transpose row arrays/ranges/groups/sets multiple mini tables to several columns respectively

Need to transpose many one (1) column arrays/ranges/groups/sets multiple mini tables consisting of varying contiguous rows to several columns respectively omitting empty rows but including extra column in transposed results if original column set contained extra row

Spreadsheet contains one (1) column starting at A2 with over 20,000+ (twenty thousand) rows which needs to be transposed preserving existing order in multiple columns

If the data in ranges was consistent (every 6 cells) a quick modification of the solution in the following example would work

=IF(INDEX($A$1:$A$17328,ROW(A1)*6-6+COLUMN(A1))=0,"",INDEX($A$1:$A$17328,ROW(A1)*6-6+COLUMN(A1)))

SOURCE(S)

http://www.excelforum.com/showthread....

http://webcache.googleusercontent.com...

HOWEVER

from quick observation the mini table arrays are grouped in varying sets of 6 (six) or 7 (seven) *COULD BE MORE OR LESS AND THERE IS NO CONSISTENT PATTERN* contiguous row ranges separated by various empty rows which can be four (4) or two (2) rows *COULD BE MORE OR LESS AND THERE IS NO CONSISTENT PATTERN* and using the above formula works fine up to the point that the original data in the 1st column contained an extra row such as Set1Data7 (again this could be MORE OR LESS and would need to include all grouped data separated by a space)

keywords tags calc transpose varying multiple rows array range column between empty

You cannot transpose 20000 (+) rows because the result would be 20000 (+) columms wide. The maximum number of columns in a Calc spreadsheet is 1024.

Something like this can't be achieved using spreadsheets (which are row-based databases with a GUI on top) nor row-based databases, so you need column-based softwares.

Addendum

Dynamic column container (allowing for higher numer of columns) advertised for V5.2 to come. See bug discussion tdf#50916

Hello Lupp thank you so much for replying quickly, I may have used the wrong terminology or need to explain myself a bit better.

the spreadsheet contains 1 column, in that column there are groups of data (some are 6 rows some are 7 rows from what ive seen could be more could be less). each group is separated by 2 or 4 empty rows. what i need is to take each group in the original column and have them transposed into columns which would total 6 or 7 columns then start over for each set of data

the formula i found & modified a bit actually worked well until it got to several rows down then shifted the results cell 1 (one) column to the right because the original/source (hope i'm using this term correctly) contained a group of data (they are about 7 rows max from what i saw but could be more or less somewhere buried in 20,000+ rows of data). i would like to visualize this so will add a comment trying to structure this hopefully it comes out, if not will try to attach or link an image

tried to copy & paste example from spreadsheet into this comment box but it came out screwed up so here is a link to a screenshot, hope it helps and sorry if i'm using the wrong terminology and if so please feel free to correct me so i know. thank you!

http://www.ge.tt/3tOpBeZ2

Sorry forgot to mention that the code I quoted above in the link worked when I removed all blank rows from the Source/Original Column and it was used as an example. The formula to make this work may have to be done from scratch to ignore/omit the empty rows/lines in the Source Column to place respective data in the Transposed Columns