Excel - Generate Random Numbers, No Repeats (No Duplicates), Unique List

Excel - Generate Random Numbers, No Repeats (No Duplicates), Unique List.
#excel #excelformula #excelfunctions

[@davidfernandezbajo]

Congrats, great video! in google spreadsheet, INDEX function doesn't work as array so the implementation would be: =LAMBDA(low,high,total,ARRAY_CONSTRAIN(SORT({SEQUENCE(high-low+1)+low-1,RANDARRAY(high-low+1)},2,1),total,1))(1,60,5)

[@ExcelGoogleSheets]

Looks good! 👍

[@ExcelGoogleSheets]

Google Sheets implementation of INDEX & SUMIFS functions is super annoying.

[@teclartizvigone5638]

Thank you for sharing

[@thomasortiz6916]

Great video, pretty much what I needed instead I wanted the unique random number in a row not in a column. I had to adjust the formula not to be in columns but in rows. Took me a long time to figure it out but if anyone wants this formula in rows here it is. =LAMBDA(low,high,limit,INDEX(SORT(VSTACK(SEQUENCE(,high-low+1),RANDARRAY(,high-low+1)),2,,TRUE),,SEQUENCE(,limit))+low-1)

[@sarahbohmann384]

This is great! I was curious, is there a way to create two columns filled with Random values using the Randunique function created (randomized list of 1-15 for example in two separate columns) without repeats on the same row?

[@richardhay645]

Good use of LAMBDA. Too many creatiors make complicated (that's ok), often inefficient (not ok), SINGLE USE(???) LAMBDAs! BTW I use Advanced Formula Environment to take care of naming etc. Good video!.

[@ExcelGoogleSheets]

What is "Advanced Formula Environment"?

[@richardhay645]

I tried twice to answer but my longer comment keeps getting taken down! (Twice!) In (very) short it is an Add in from MS that is a more robust version of Name Manages that makes much easier to create LAMBDAs. Leila Gharani has the best video (made about 6 mo ago).

[@ExcelGoogleSheets]

That makes sense. I pretty much avoid all addins.

[@richardhay645]

@@ExcelGoogleSheets Since my third attempt to answer is still OK. I will add a bit more. All named fuctions from Nane Manager are available in this feature. The Add In ia available by going to add ins on the ribbon and searching for it. When you click add it will appear on the over-crowded! right side of the Home tab. You can build your core function inside the dialog box, bu I find it a bit easier to build it on the spreadsheet and paste it in the dialog box. You then convert it to a LAMBDA aftervyou paste it and add parameters, give it a name,etc. in the dialog box. Much more user friendly than Name Manager. I think Microsoft made it an add in insteD of an installed festure only because most users do not (yet) create LAMBDAs.

[@richardhay645]

@@ExcelGoogleSheets It seems to be a stable add in and was created by Microsoft. I also generally avoid add ins. Watch Leila's video. That's what convinced me to use it. I think it will make it to the ribbon at some point.

[@blackfire2574]

Wouldn't it be easier to use the third argument in the SEQUENCE function to generate from desired number. It would look like this SEQUENCE(high-low+1,1,low)

[@ExcelGoogleSheets]

That would have been better 👍

[@eddieabbott370]

​@@ExcelGoogleSheets Can you pick the pick 4 Lottery for WashingtonDC

[@KarolKarasiewicz]

Great and smart. The biggest cons - it wouldn't work with Google Sheets. Would You mind to take am analog video in Sheets? Thanks on advanced

[@ExcelGoogleSheets]

Will do when I have time.

[@davidfernandezbajo]

=LAMBDA(low,high,total,ARRAY_CONSTRAIN(SORT({SEQUENCE(high-low+1)+low-1,RANDARRAY(high-low+1)},2,1),total,1))(1,60,5)

[@husamkhraiwesh9648]

Thanks for the lovely video

[@MISComputerNET]

Hi, Thanks for the lovely video. As things are going to the cloud recently, I have started to move my excel files to Google Drive. I have been using a small software that I had made in Libreoffice Calc to generate my business invoices. In my business, I have custom formatting to indicate currency. For instance, if I issue the invoice in USD, all the costs or prices are shown with USD sign. Likewise, if I choose Euro, everything is shown with Euro sign. To do that, I made two separate styles and apply these styles thanks to the conditional formatting. Unfortunately, in Google Sheets, it is not that simple. In fact, I couldn't find it. Do you know any workaround for this? Thanks for your help in advance and wish you a nice day.

[@SRDFTM]

omg i love your videos :) Thank you

[@دراساتاجتماعية-ز9ل]

Many Thanks ..so great🌹🌹🌹

[@YuvenMarsel]

Great ways, but i have question Is possible to make random numbers not change when update other cells (without macro or script) ?

[@onlinemoneynow247-xg1tm]

Efing marvelous

[@sergheyzaporojanu6872]

thanks for pointing out this video to me. But how would you do that with a range of text/values instead of just numbers?

[@ExcelGoogleSheets]

use this combined with INDEX function to convert numbers to text.

[@sergheyzaporojanu6872]

@@ExcelGoogleSheets nice idea thanks! how would it be in Google Sheets?

[@JCEurovisionFan1996]

Great video, but how can I use that without HSTACK and LAMBDA? How can I do the similar thing but using only the LET function? I don't have HSTACK and LAMBDA at the moment.

[@ExcelGoogleSheets]

you should be able use CHOOSE function. instead of HSTACK(array1,array2) use CHOOSE({1,2},array1,array2)

[@mungunbayar.bat-ochir]

=LET( min;B2; max;B3; size;B4; population;max-min+1; is_valid;AND(max>=min+1;population>=size); result;IF(is_valid;TAKE(SORTBY(SEQUENCE(population;;min);RANDARRAY(population));size);"invalid input"); result )

[@Happy4ever]

Which formula use for swap number as below example: 123 to 132, 213, 231, 321, 312 help please

[@djl8710]

Nice

[@10degreerule]

Hi I was wondering if you could help me with generating 28 players in foursomes playing 7 weeks and each player in each group will not meet any of the players that he played each week throughout the seven weeks. I did it kinda manually and got through for 5 weeks and during the last two weeks there was instances that one player will meet once or twice during the seven weeks I just was wondering if there was a formula basically we looking on 7 columns and 7 Rows and place 4 players in each group Thanks in advance for any help

[@kotvas923]

Is there any way, after generating the numbers, somewhere in the sheet to have them ascending?

[@anpham9505]

🎉 thank

[@bumpersmith]

How could you create a single unique random number such as the random number that Microsoft Access Creates to use as unique ID,s

[@thomasortiz6916]

need help how can I use this formula but to generate random numbers from least to greatest?

[@timmax4048]

9:23 to cont...

[@jeteastar3603]

can you use take instead of sequence

[@ExcelGoogleSheets]

Instead of INDEX you mean? Yes, you could.

[@Jfresh55]

Since when did you start making excel videos?

[@ExcelGoogleSheets]

Been making them before I even knew what Google Sheets was.