Excel

Occasional Visitor

hi i have a table with a lot of column and i need an efficient way to re organiz the data form a number of column in to a new column while maintainig the order (the same way as the pic

or24_1-1651570862634.png

 

5 Replies

@or-24 

That could be like

image.png

in C7

=IFERROR(INDEX(Table1, MOD( ROW()-ROW($C$6)-1, ROWS(Table1))+1, INT( (ROW()-ROW($C$6)-1)/ROWS(Table1))+1 ), "")

and drag it down

@or-24 

In P9:

 

=INDEX(Table1,MOD(ROW(P9)-ROW($P$9),ROWS(Table1))+1,MOD(QUOTIENT(ROW(P9)-ROW($P$9),ROWS(Table1)),COLUMNS(Table1))+1)

 

where Table1 is the name of the table. Fill down to as many rows as the number of cells in the table.

If you have Microsoft 365 or Office 2021, you can use

 

=LET(
    numrows,ROWS(Table1),
    numcols,COLUMNS(Table1),
    numcells,numrows*numcols,
    seq,SEQUENCE(numcells)-1,
    rownum, MOD(seq, numrows)+1,
    coloff, QUOTIENT(seq, numrows),
    colnum, MOD(coloff, numcols)+1,
    INDEX(Table1, rownum, colnum)
   )

@Hans Vogelaar 

And even more modern

=TOCOL(Table1,,1)

@Sergei Baklan 

Excellent, but is that out of beta yet?

@Hans Vogelaar 

Nope as I know.