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

Подписаться 91 тыс.

Просмотров 2,3 тыс.

50% 1

⚡⚡ 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 😩 )

Хобби

Опубликовано:

5 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 16

@chrism9037 5 месяцев назад

Thanks Wyn, this is a great video!

@AccessAnalytic 5 месяцев назад

Cheers Chris

@daXcel7448 2 месяца назад

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

@AccessAnalytic 2 месяца назад

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

@barttitulaerexcelbart9400 5 месяцев назад

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

@AccessAnalytic 5 месяцев назад

Cheers, and good tip!

@yulinliu850 5 месяцев назад

❤❤

@brianxyz 5 месяцев назад

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

@AccessAnalytic 4 месяца назад

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 5 месяцев назад

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 5 месяцев назад

😩 why no?

@grahamc5531 5 месяцев назад

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 4 месяца назад

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 4 месяца назад

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

@ExcelWithChris 4 месяца назад

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 4 месяца назад

@ExcelWithChris ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-weafpG2yG1w.htmlsi=mcjxVeA7uAUeu7Na