Request = random generator from 000 to 999
That has at least one of the 3 digits = 3,6,9
Example
026
023
029
119 .... 348
Can not Have Two-Digit Example 360 Has 3.6 Can not
It has only one digit of the 3 possible = or 3 or 6 or 9
Generate all formations from 000 to 999
Why not just list the values 000 to 999 and write a formula to see if they are valid, then filter and remove those that are not?
Entia non sunt multiplicanda sine necessitate
Your last line seems to contradict the "random generator" requirement... But iof you do actually want to generate suitable numbers at random, you could use something like:
Please Login or Register to view this content.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
shg=Yes it's correct
You can create the formula to filter
From 000 to 999?
yes olly.The trio, must have only one digit
Or 3 or 6 or 9 numca two three
Only one digit
Yes you can create the 000 to 999 and filter
How to create macro that filters? please
Still not sure I understand. Are you trying to create a list of numbers between 1 and 999 which have either one 3, one 6 or one 9 in them, but no more than one of 3, 6 or 9?
If that's what you want, then try:
This will list such numbers in Column A of the active sheet.Please Login or Register to view this content.
Why do you need a macro at all?
003 835 D2: =INDEX($A$2:$A$442, RANDBETWEEN(1, 441)) 006 985 009 516 013 987 016 684 019 922 023 019 026 059 029 173 030 910 031 958 032 034 035 037 038 043 046 972 974 975 977 978 980 981 982 984 985 987 988
Last edited by shg; 07-27-2017 at 10:27 AM.
This formula should do what you want
=IF(RAND()<1/3, 3*RANDBETWEEN(1,3)&RANDBETWEEN(0,99), IF(RAND()<0.5, RANDBETWEEN(0,9)&(3*RANDBETWEEN(1,3))&RANDBETWEEN(0,9), RANDBETWEEN(0,99)&(3*RANDBETWEEN(1,3))))
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
That generates, for example, 997, Mike, which I think violates the "only one" rule.
Shg, ok you're correct I need to generate all the formations from 000 to 999 as shown in your example
Ok shg perfect ,, can not have 997
mike, Because two digits, the rule is only one of 3,6,9,
Not to generate two or three only one digit of the group 3,6,9
Another way:
=REPLACE(MID(
"000100200400500700801101201401501701802102202402502702804104204404504" &
"704805105205405505705807107207407507707808108208408508708811121141151" &
"171181221241251271281421441451471481521541551571581721741751771781821" &
"841851871882224225227228244245247248254255257258274275277278284285287" &
"288444544744845545745847547747848548748855575585775785875887778788800",
RANDBETWEEN(1, 343), 3), RANDBETWEEN(1, 3), 1, MID("369", RANDBETWEEN(1, 3), 1))
Last edited by shg; 07-27-2017 at 11:39 AM. Reason: more legible
More simply,
=REPLACE(MID("00102040507081121415171822425272844547485575877880",
RANDBETWEEN(1, 49), 2), RANDBETWEEN(1, 3), 0, MID("369", RANDBETWEEN(1, 3), 1))
Last edited by shg; 07-28-2017 at 12:14 PM.
Okay, good work. Is this a formula?
What happens when you enter it in a cell?Is this a formula?
Here is the code for UDF For Rand between Given numbers with only one number out of 3 or 6 or 9.
Please Login or Register to view this content.
Last edited by kvsrinivasamurthy; 07-28-2017 at 11:56 AM.
Ok, perfect kvsrini, but how do i create all the ooo formations to 999? Only two at a time?
I need all training in 000 to 999
Just enter numbers 0 & 999 in formula. Pl see file. You will get numbers in text format. As like 001 numbers cannot displayed if cell is in number format.
Are you interested in creating all numbers From 000 to 999.
Hi all- a different approach:
=SUBSTITUTE(SUBSTITUTE(MID("XY"&INT(1.4*RANDBETWEEN(0,6))&"XY", RANDBETWEEN(1,3),3),"X",RANDBETWEEN(1,3)*3),"Y",INT(1.4*RANDBETWEEN(0,6)))
=MID(REPT(INT(1.4*RANDBETWEEN(0,6))&INT(1.4*RANDBETWEEN(0,6))&RANDBETWEEN(1,3)*3,2),RANDBETWEEN(1,3),3)
Last edited by leelnich; 07-29-2017 at 11:48 PM.
Hello kvsrini, can you put all the formations from 000 to 999?
Please, I'm not getting this missing formulas
You can do all the formations within the criterion 3,6,9
And then put to download!!!
003 126 237 340 459 568 670 790 900 006 129 238 341 460 573 671 791 901 009 130 243 342 461 576 672 792 902 013 131 246 344 462 579 674 794 904 016 132 249 345 464 583 675 795 905 019 134 253 347 465 586 677 797 907 023 135 256 348 467 589 678 798 908 026 137 259 350 468 590 680 803 910 029 138 260 351 473 591 681 806 911 030 143 261 352 476 592 682 809 912 031 146 262 354 479 594 684 813 914 032 149 264 355 483 595 685 816 915 034 153 265 357 486 597 687 819 917 035 156 267 358 489 598 688 823 918 037 159 268 370 490 600 703 826 920 038 160 273 371 491 601 706 829 921 043 161 276 372 492 602 709 830 922 046 162 279 374 494 604 713 831 924 049 164 283 375 495 605 716 832 925 053 165 286 377 497 607 719 834 927 056 167 289 378 498 608 723 835 928 059 168 290 380 503 610 726 837 940 060 173 291 381 506 611 729 838 941 061 176 292 382 509 612 730 843 942 062 179 294 384 513 614 731 846 944 064 183 295 385 516 615 732 849 945 065 186 297 387 519 617 734 853 947 067 189 298 388 523 618 735 856 948 068 190 300 403 526 620 737 859 950 073 191 301 406 529 621 738 860 951 076 192 302 409 530 622 743 861 952 079 194 304 413 531 624 746 862 954 083 195 305 416 532 625 749 864 955 086 197 307 419 534 627 753 865 957 089 198 308 423 535 628 756 867 958 090 203 310 426 537 640 759 868 970 091 206 311 429 538 641 760 873 971 092 209 312 430 543 642 761 876 972 094 213 314 431 546 644 762 879 974 095 216 315 432 549 645 764 883 975 097 219 317 434 553 647 765 886 977 098 223 318 435 556 648 767 889 978 103 226 320 437 559 650 768 890 980 106 229 321 438 560 651 773 891 981 109 230 322 443 561 652 776 892 982 113 231 324 446 562 654 779 894 984 116 232 325 449 564 655 783 895 985 119 234 327 453 565 657 786 897 987 123 235 328 456 567 658 789 898 988
Hello shg, perfect, good job, congratulations, thank you !!
...And 1 last formula to randomly generate the specified 3-digit configuration:
=MID(REPT(INT(1.4*RANDBETWEEN(0,6))&INT(1.4*RANDBETWEEN(0,6))&RANDBETWEEN(1,3)*3,2),RANDBETWEEN(1,3),3)
Last edited by leelnich; 07-30-2017 at 10:48 PM.
Hello leelnich, perfect work, congratulations, thank you
If Leelnich has taken care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Okay, but the filters are still missing
example
Just stay a training
example
941 = 194, 914, 491, 499, 941
I just want a form 941 the other five have to filter
Another example = 003 = 030 300
I just need 003.
So go on the shg table is filter only leave a formation
Because I played in the inverted I do not need the others in the same group
Another example
456 = 654, 564 ... I just need a form of this group
Can be 456, or another of the groups and so from each base number, the shg table can stay with 70 to 80
Have watch
But I need this filter, please.
003 049 135 234 347 479 006 056 137 235 348 489 009 059 138 237 355 556 013 067 146 238 357 559 016 068 149 246 358 567 019 079 156 249 377 568 023 089 159 256 378 579 026 113 167 259 388 589 029 116 168 267 446 677 034 119 179 268 449 678 035 123 189 279 456 688 037 126 223 289 459 779 038 129 226 344 467 789 046 134 229 345 468 889
Last edited by shg; 07-30-2017 at 07:40 PM.
Hello shg, very good job, congratulations, magnificent
, Congratulations, thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks