rearrange table

Copper Contributor

hi I'm trying to rearrange a few tables

I need to make the table from the data table every few column for new row

like this example:

 

may_biopharmax_0-1677072360226.png

 

4 Replies

@may_biopharmax 

An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can select any cell of the right table and right-click with the mouse and refresh the query. 

rearrange table.JPG

@may_biopharmax Or, if you are on a modern Excel version, this will work also:

Riny_van_Eekelen_0-1677075906631.png

=WRAPROWS(TOCOL(A1:F3),3)

 

Thank you very much! @Riny_van_Eekelen 

@Riny_van_Eekelen 

Hi Riny.  Naturally this is the solution I prefer.  I do use PQ but mainly linked to bringing data into Excel, not processing it once it is there.  I find it somewhat non-intuitive what the various combination of TOROW/COL and WRAPROWS/COLS will yield, sometimes it is second go lucky!

 

image.png

I came to the conclusion that the choice of the inner TOROW/COL makes no difference (I have settled on TOCOL because it is not limited by size).  What does make a difference is whether the selected function is set to scan by row or by column.  At least that simplifies the options.  I recently used the 3rd formula recursively within a single function that split even and odd rows of a column and restack them within varying block sizes as a step of a Fast Fourier Transform

[Note: other settings gave a simple TRANSPOSE or returned the original unaltered]