Excel: How to randomly pick a number of items from a list using only 1 formula

⚡⚡ This is a tutorial in Dynamic Array Excel, LET and LAMBDA ⚡⚡
The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. accessanalytic.com.au/
00:00 Introduction
00:46 The Basic formulas in separate columns
03:17 Bringing it together in a single formula using LET
07:36 Naming the formula using LAMBDA
11:05 Storing your LAMBDAS in a GIST
🔢The Formula ( I realised I could AVOID the HSTACK by using SORTBY )
=LET(
SelectedList,B3:B12,
NumberOfItemsToReturn,4,
TAKE(
SORTBY(
SelectedList, RANDARRAY(ROWS(SelectedList))
),
NumberOfItemsToReturn,1
)
🖥️ My GIST
gist.github.com/wynhopkins/2e...
⏬Download my file
aasolutions.sharepoint.com/:x...
📕 BOOK
Did you know I've written a book "Power BI for the Excel Analyst"?
pbi.guide/book/
Connect with me
wyn.bio.link/
( apologies to Tahlia for accidentally missing her name out 😩 )

[@chrism9037]

Thanks Wyn, this is a great video!

[@AccessAnalytic]

Cheers Chris

[@daXcel7448]

Thanks for the video!. How about this one without a Lambda. =CHOOSEROWS(A1:A10,SORTBY(SEQUENCE(C1), RANDARRAY(C1)))

[@AccessAnalytic]

Nice one. Another option: =TAKE( SORTBY(A1:A10,RANDARRAY(ROWS(A1:A10) ) ), C1)

[@barttitulaerexcelbart9400]

Well very nice to see so many stuff in one video! as for the brackets, the last one should always be black...😉😉

[@AccessAnalytic]

Cheers, and good tip!

[@yulinliu850]

❤❤

[@brianxyz]

Is there a way to add tooltips to parameters? Also, what's the easiest way to make a Lambda global?

[@AccessAnalytic]

No to tooltips. You could share centrally held Excel templates and set up to open by default when opening excel ( there’s a templates file path under file - options - ( advanced I think

[@grahamc5531]

The battle I've had to get the Advanced Formula Editor enabled in our corporate Excel....a year for them to say no....oh well, keep to the old method of adding LAMBDAs.... 😒😒

[@AccessAnalytic]

😩 why no?

[@grahamc5531]

No reason given, just no - I assume they think because it is an add-in there may be a security risk....even though it comes from MS themselves...here's hoping it gets added as a standard button at some point so everyone gets it

[@ExcelWithChris]

Please please help. Power Query. Somewhere I saw you can get a result in a step, then export that result to another query, come back to this query and ref the step before "exporting" and carry on with the current query.

[@AccessAnalytic]

I think I saw Chandeep ( Goodly KZbin channel ) do that a while back. Not sure I’d recommend it from a process understandability point of view.

[@ExcelWithChris]

Think that is where I also saw it, but cannot find the video again. Left comment on one of his videos as well, but he seldom comes back. Do you by any chance know how to do it? (as a in between step and then carry on)@@AccessAnalytic

[@AccessAnalytic]

@ExcelWithChris kzbin.info/www/bejne/rZbEl6N9Z956Z9ksi=mcjxVeA7uAUeu7Na