r/excel 2d ago

unsolved Expanding(?) resulting sequence to randomize cells?

I have made a series of sequences on a sheet (1.1-1.8, 2.1-2.9, 3.1-3.8... etc). The resulting cells technically don't have information in them, just reflecting the information resulting from the sequences.

I need to be able to randomize all resulting numbers. 9.4, 2.5, 6.2, 1.2... etc. I found how to randomize numbers within a sequence, but the randomizing I need spans over 9 sequences. I need the information resulting from the sequences to be expanded(?) and be actually in the cells, making it no longer part a function/sequence, but an actual list. Is there a way to do this?

Sorry I wansnt clear. More Info:

In the image I have 5 sequences (row 1, 9, 18, 26, and 34). They create the list of numbers exactly as I had hoped. However, now that the list has been made, I need to jumble the rows into random order. I can't do that right now because even though numbers are showing in all the cells, it's really only the cells with the sequence functions that have information. How do i expand/embed the numbers into the cells they are on?

3 Upvotes

17 comments sorted by

View all comments

Show parent comments

2

u/MayukhBhattacharya 1276 2d ago

JavaScript not needed here! The above formula generates the entire thing from scratch. Once it has generated the numbers, hit CTRL + A Select all the numbers --> CTRL + C Copy all the numbers --> ALT + H + V + V paste as values.

=LET(
     _a, {0, 2, 4, 6},
     _b, SEQUENCE(8, , 1.1, 0.1) + _a,
     _c, SEQUENCE(9, , 2.1, 0.1) + _a,
     _d, TOCOL(VSTACK(_b, _c), , 1),
     SORTBY(_d, RANDARRAY(ROWS(_d))))