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:
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.
@may_biopharmax Or, if you are on a modern Excel version, this will work also:
=WRAPROWS(TOCOL(A1:F3),3)
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!
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]