Forum Discussion

dkent1982's avatar
dkent1982
Copper Contributor
Dec 02, 2022

combine long list to fill a page at a time

I was given a long list provided with information containing three columns in order. I need to combine this list to where it stays in order but fill a page at a time. Below is a picture of what i hav...
excel
mtarler's avatar
mtarler
Silver Contributor
Dec 02, 2022

dkent1982 I'm not sure I understand, but if you want to 'wrap' your list and you have excel 365 then I created a LAMBDA function in the attached to do that.  The formula is:

 

ReshapeXrows = lambda(in,rows,LET(i, EXPAND(in,ROUNDUP(ROWS(in)/rows,0)*rows,,""),
                   DROP(REDUCE("", SEQUENCE(ROUNDUP(ROWS(i)/rows,0),,0,rows), 
                      LAMBDA(u,v, HSTACK(u, CHOOSEROWS(i, SEQUENCE(rows)+v)))),,1)))

so do a print preview of the existing list and zoom in/out to the size you want and see how many rows are shown.  Then on new worksheet use above lambda function to create the wrapped version:

=ReshapeXrows( [originalData], [numberRows] )

Then print this new sheet (make sure to set your zoom accordingly or just set to 'fit all rows'

 

Patrick2788 , HansVogelaar  is there an easier way to make this 'wrap around' function?  Am I missing something that already exists?

 

Patrick2788's avatar
Patrick2788
Silver Contributor
Dec 03, 2022

mtarler 

This one may be more trouble than it's worth with a LAMBDA.  I say this because the solution is not elegant. It's the ol' join/split/wrap approach.  My sample is pulling 100 records before wrapping (I realize things go haywire if 110 is selected but I share this Lambda to give an idea of what it takes.).  This task would be a lot easier if a matrix could be wrapped:

 

'HyperWrap
=LAMBDA(arr,records,LET(
    w, ROWS(arr) * 3 / records,
    joined, BYROW(arr, LAMBDA(row, TEXTJOIN("|", , row))),
    bundled, TEXTJOIN("|", , WRAPCOLS(joined, records, "")),
    split, TEXTSPLIT(bundled, , "|", , , ""),
    WRAPROWS(split, w, "")
))

 

  • mtarler's avatar
    mtarler
    Silver Contributor
    Dec 04, 2022
    ah yes, in row 1 of my solution I converted the "in" range using EXPAND to make sure it is an even multiple of the N number of rows 🙂

Resources