excel

Question

arrange one to 100 serial number in one page for printout. actually I have 1 to 1800 Serial number in A and B collum in single row .. how can i fit in C and D and E and F like screen shot

hansvogelaar · Answer

vijaysbhati&nbsp;
Adapted from WRAPCOLS with more than one column of data?&nbsp;
=LET(
   orig, A2:B1801, 
   onr, ROWS(orig), 
   onc, COLUMNS(orig), 
   resr, 25, 
   resc, onc*ROUNDUP(onr/resr, 0), 
   MAKEARRAY(
     resr, resc, 
     LAMBDA(r, c, 
       IF(
         r+resr*INT((c-1)/onc)&lt;=onr, 
         INDEX(orig, r+resr*INT((c-1)/onc), 1+MOD(c-1,onc)), 
         ""
       )
     )
   )
 )

lorenzo · Answer

vijaysbhati&nbsp;Alternatively, with Power Query:let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TableSplit = Table.Split(Source, 25),
    TablesToColumns = List.Transform(TableSplit, Table.ToColumns),
    Names = List.TransformMany(
        {1..List.Count(TableSplit)}, each Table.ColumnNames(Source),
        (i,name) =&gt; Text.Combine({name, Text.From(i)}, " #")
    ),
    TableFromColumns = Table.FromColumns( List.Combine(TablesToColumns), Names )
in
    TableFromColumns

Forum Discussion

excel

Share

Resources