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

English
, , , , ,
#1

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.php%3Ft%3D947088+&cd=13&hl=en&ct=clnk&gl=us

http://webcache.googleusercontent.com/search?q=cache:Xtp4f7L--r4J:www.excelforum.com/showthread.php%3Ft%3D947088+&cd=13&hl=en&ct=clnk&gl=us

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

#2

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

#3

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.

#4

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

#5

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

#6

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

#7

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

#8

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

#9

(Editing again)
Being back to this thread by accident I want to attach a much simpler and more efficient (IMO) solution:
ask68180ComplicatedRearrangement002.ods
(End of edit)

See attached example.
Of course, you may also calculate the elements inside the range of transposed sections of the original column one by one using OFFSET (or INDEX) and thereby avoid the well known disadvantages of array formulae concerning maintenance/enhancements/scaling.
(I cannot imagine a somehow reaonable case of a data collection needing such a rearrangement. I would not expect much use of it, except, maybe, for printing.)
ask68180ComplicatedRearrangement001.ods

PS
I didn’t check the quoted formula.

Edit 1:
Meanwhile I had a look on the quoted formula. It simply is breaking and transposing the ‘Source’ in pieces assuming there are no blank cells and the length of each section is 6. The Source itself is given by a fix RangeAddress. This is covered by formulae for ‘Rearranging Rectangular Ranges’ which are to find here and there. This obviously does not match your much more complicated needs. Didn’t you see that?
The solution I proposed is adapted to your needs introducing some helper columns in a way I prefer because it allows for clearly structured solutions. However, it requires a bit of additional adaptions if the size of the job got scaled up.

Now we are in a dilemma:

Either you acquire a basic understanding of the solution and then are capable of doing the filling down of formulae as needed for your actual task. Of course, you also may find a solution better adapted to your needs, better structured, whatever… based on your understanding.
Or you try to get a “fool-proof” solution by someone doing a professional job.
If you get your solution the second way, it will be much more complicated and much less efficient because of the requirements of being fool-proof. No chance that a (kind of) beginner will understand it on a level to be able to maintain and to enhance it himself. And: One day you may urgently want a variant or an additional feature. Who should enhance the solution then? Do you want to depend on a specialist’s support in the long run? I won’t be that specialist.
Thus I want to emphatically advise you to choose the first way. It are users on the way of understand-and-do-it-yourself-then this site is made for.

Edit regarding additional comments.
Simply use the file I attached as an engine:

  1. Fill in your 1-column-data into column A of sheet ‘Solution’ beginning with A2.
  2. Fill down the formulae in the helper columns as far as data are present in column A.
  3. Fill down the array formulae in the H through P to get as many output rows as sections were found (I expect a maximum of about 3500 rows). Best you do that using a menu path to ‘Fill’ > ‘Down’. If you want to do it dragging the fill handle, you have to press and keep pressed the Ctrl-key in addition.

You may ‘Copy’/‘Paste Special…’ (values only) elswhere, of course.

#10

Lupp, I think the example you posted is what I need. Your example shows Column A which I call the source and it shows the array/ranges which I call “sets” grouped together then transposed to Columns H through N. I don’t need my results to show Columns C through G including the Helpers which I don’t know what they are).

How do I implement this to my workbook spreadsheet to reflect upon 20,000+ rows of data in Column A which contains many sets grouped together? Thank you very much!

#11

I copied my Column A into your ask68180ComplicatedRearrangement001.ods and it works! :slight_smile: but it only does it for 23 rows :frowning: I guess those Helper columns you added actually do something :slight_smile:

Is there a way to make this work for 20,000+ rows? since i will be given more data on spreadsheets like this in the future, a rearrangement with more or less rows to insert my source column and specify the amount of rows needed or make it infinite which would be preferred method so dont have to do it manually

#12

Would it help if I found out exactly how many rows my source Column A has in this project? Thank you for all your help!

#13

I know it may seem as though transposing this data would not help but to a noob it really does for comparison sorting and aesthetics on screen. I’m sure more advanced users have skills to do this and much more but I just need it so I can eventually sort the results in columns as needed. You are a big help and very much appreciated

#14

Well organised data should not require such a lot of processing to prepare a ‘PrettyPrint’. In your case a better concept of keeping your data should mainly require two additional measures

  1. No empty rows (blank cells in the ‘Source’ column here)! Data need be kept in a contiguous range.
  2. Each row (‘DataSet’ even if very short) should contain the section it is belonging to explicitly in a dedicated field (cell of an additional column). Ascending values!
#15

Due to the length of the sections not being constant, the task will still be nontrivial in a sense.

#16

i posted another question if there is a way to remove all empty rows except for one. then maybe i could find a macro or someone what could make a formula that transposes/moves every block of linear/contiguous set of grouped data to the new columns.

I’m was hoping for the easiest quickest solution. i don’t even need to keep formatting or cell equations just the alphanumeric characters, numbers & symbols in the cells.

i would like to know how to do this myself if possible too. any videos? ty

#17

the way i received the data is bad i know it’s horribly organized which makes it impossible to compare and sort unless the rows are transposed to like/similar columns for sorting purposes.

#18

i thought there may be some quick easy macro script i could run that would crawl the columns transposing all contiguous/linear grouped data sets, ignore any/all blank/empty rows then to loop and keep repeating itself until it intelligently finds no more data in source column OR reached spreadsheet maximum row limit OR certain amount of empty rows OR specified amount of rows

#19

I am going to use your instructions and step by step write how I follow them

1. Fill in your 1-column-data into column A of sheet ‘Solution’ beginning with A2.

If my data starts at A1 (w/o without a single header row) the last filled cell entry ends at A25959.
If it starts at A2 (with a single header row) then the last filled cell entry ends at A25960

I left clicked on A2 to select the cell then scrolled down using the scroll bars and shift left clicked on cell A25960

Copied, Pasted

#20
  1. Fill down the formulae in the helper columns as far as data are present in column A.

Do I continue where you left off and Left Click in C207 then drag right to cell F207 or G207?

OR

Do I start with a Left Click in C2 then drag right to F2 or G2?

THEN

from which cell do I drag down all the way until row 25960?

thank you

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.