Excel - Generate Random Numbers, No Repeats (No Duplicates), Unique List. #excel #excelformula #excelfunctions
Пікірлер: 43
@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.
@teclartizvigone563823 күн бұрын
Thank you for sharing
@thomasortiz69169 ай бұрын
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
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
@10degreerule7 ай бұрын
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
@kotvas9238 ай бұрын
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
@thomasortiz69169 ай бұрын
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.