How to make one column data to rows in excel (google sheet)-CodePudding

want to know how to preprocessing data form in excel.

How do I easily make this left(one column data) to right(pretty well structured dataframe) transform via excel(or google sheet)? It's difficult to do manual work because of the data size.

CodePudding user response：

try:

=INDEX(IFNA(QUERY(A1:A10, "skipping 3", )&
            QUERY(A2:A10, "skipping 3", )&
            QUERY(A3:A10, "skipping 3", )))

CodePudding user response：

See my important additional comments to your original post.

Those comments notwithstanding, if your spreadsheet is set up exactly as shown in your image, and if your data types for the final layout will be the same per column, and if there are no gaps in your data (i.e., the data cycles every four rows without exception), and if your international locale is one that uses a comma to separate formula parameters... then delete everything shown in your image for Cols D, E, F and G and then place the following formula in D2:

=ArrayFormula(QUERY({A:A,{A2:A;""},{A3:A;"";""},{A4:A;"";"";""}},"Select * WHERE Col1 Is Not Null SKIPPING 4"))

The four sub-arrays { __,{__},{__},{__} } that form the QUERY have null pseudo-rows added to the end of each except the first (i.e. adding 0, 1, 2 and 3 null rows respectively), in order to keep the four array lengths parallel despite their having different starting points.