Random and unique number between 12345678 to 87654321

[faizee]

hello
i want to create list of random and unique numbers of 8 digits between 12345678 and 87654321
but any digit should not be repeat
for example
12345678 ok
21345678 ok
32145678 ok
12245678 not ok. due to repeate of 2
87654321 ok
87754321 not ok due to repeat of 7

pls help

 

[bebo021999]

Are the numbers always in increasing or decreasing order(except 1 number)?
what if :
16578243
or
84652137
?

 

[faizee]

no

Are the numbers always in increasing or decreasing order(except 1 number)?
what if :
16578243
or
84652137
?

not necessary requirement of increasing or decreasing

 

[bebo021999]

try to use UDF:
=RandomNumber ( n )
with n =8
=RandomNumber(8)

Option Explicit
Function RandomNumber(ByVal n As Integer) As Double
Dim r, i&, c&, st As String
Randomize
With CreateObject("Scripting.Dictionary")
    Do
        r = Int(Rnd * n) + 1
        If Not .exists(r) Then
            .Add r, ""
            st = IIf(st = "", "", st) & r
            c = c + 1
        End If
       
    Loop Until c = n
End With
RandomNumber = st
End Function

 

[faizee]

i need formula without VBA

 

[Herakles]

hello
i want to create list of random and unique numbers of 8 digits between 12345678 and 87654321
but any digit should not be repeat
for example
12345678 ok
21345678 ok
32145678 ok
12245678 not ok. due to repeate of 2
87654321 ok
87754321 not ok due to repeat of 7

pls help

What do you want to do with these numbers when they have been created?

Are you OK with running some code to create the list?

 

[faizee]

What do you want to do with these numbers when they have been created?

Are you OK with running some code to create the list?

i need to make a list, where no digits is repeating


.. i need some formula, preferably not codes

 

[Herakles]

Does this work:

=INT(12345678+((87654321-12345678)*RAND()))

 

[lrobbo314]

Not sure if this might need to be an array formula in 2016, but how about this?

COMPSTAT Template1
	A	B	C	D	E	F	G	H	I
10									Output
11	0.28991068	0.326458	0.028968	0.901024	0.097501	0.744982	0.888935	0.380314	65817324
12	0.30500802	0.194092	0.941329	0.911442	0.091013	0.554538	0.964055	0.505912	67238415
13	0.96774541	0.879585	0.53385	0.088714	0.039374	0.522002	0.742267	0.63966	12578634
14	0.07413724	0.504181	0.351516	0.842438	0.456548	0.509609	0.740465	0.259401	84615327
15	0.51996302	0.344058	0.877289	0.847822	0.969275	0.093989	0.43398	0.008155	46231758
16	0.23344029	0.934876	0.952338	0.437091	0.676456	0.764255	0.802193	0.203281	72165438
17	0.37022268	0.641698	0.514665	0.475142	0.079563	0.162846	0.113302	0.166917	41238675
18	0.62442625	0.071866	0.30132	0.183064	0.302814	0.169088	0.663359	0.774547	38564721
19	0.20325004	0.865927	0.449016	0.790918	0.186718	0.273843	0.99147	0.92834	73548612
20	0.28077246	0.082455	0.486556	0.110116	0.460887	0.376318	0.132593	0.432896	58172463
21	0.09243841	0.260006	0.470108	0.796745	0.802478	0.137261	0.694281	0.785455	86521743
22	0.71320074	0.728996	0.282798	0.567451	0.746657	0.720018	0.418491	0.795481	53862471
23	0.07979369	0.136717	0.151689	0.114104	0.795145	0.184721	0.098494	0.81532	85462371
24	0.34368385	0.784056	0.117147	0.061943	0.193133	0.1952	0.096609	0.305406	21685473
25	0.77148585	0.487683	0.228786	0.595063	0.215255	0.041055	0.75703	0.581466	15637824
26	0.3126047	0.454616	0.720125	0.809075	0.46787	0.770366	0.828319	0.785466	87526413
27	0.0887238	0.151153	0.709736	0.864979	0.541481	0.143503	0.851567	0.359927	86314725
28	0.10459679	0.833641	0.848978	0.236192	0.140603	0.25574	0.910471	0.867083	84367512
29	0.92105446	0.300469	0.33838	0.926927	0.920946	0.793678	0.151901	0.745965	27613485
30	0.86370156	0.332699	0.781093	0.333092	0.928218	0.498691	0.671536	0.294026	27361548
31	0.6005599	0.492945	0.147944	0.491091	0.422987	0.802381	0.174743	0.123952	23745168
32	0.19805887	0.142972	0.494659	0.930655	0.817525	0.608301	0.249723	0.16544	68412357
33	0.93782095	0.3376	0.410613	0.332525	0.227465	0.500048	0.900872	0.121612	15467328
34	0.74311858	0.456332	0.074981	0.63266	0.756236	0.509909	0.024123	0.218717	25731486
35	0.92510041	0.564042	0.309899	0.433309	0.280949	0.142254	0.574067	0.801603	14657832
36	0.40808568	0.955698	0.968252	0.288117	0.30433	0.022316	0.307158	0.019908	32165748
37	0.14594247	0.100315	0.577465	0.211356	0.036821	0.95893	0.266285	0.461544	67258143
38	0.0613537	0.27984	0.812844	0.434191	0.385798	0.418604	0.548476	0.329362	87135426
39	0.07975434	0.043542	0.764143	0.293412	0.177468	0.947405	0.979779	0.479867	78356214
40	0.20508102	0.574643	0.428217	0.976501	0.649918	0.068775	0.567705	0.047475	63512748
41	0.46141256	0.164097	0.204912	0.902734	0.432012	0.180252	0.263871	0.211582	28613745
42	0.31619642	0.118351	0.141225	0.462669	0.166056	0.734705	0.65561	0.825312	58746231
43	0.53229855	0.09268	0.966332	0.626555	0.268775	0.09559	0.007576	0.248598	37124685
44	0.46697024	0.15518	0.551548	0.793872	0.782598	0.106913	0.608165	0.834233	67523841
45	0.13283615	0.746539	0.710295	0.624993	0.664696	0.022854	0.385108	0.281317	71243856
46	0.63842773	0.304059	0.696165	0.920507	0.056617	0.439571	0.860012	0.499152	47318625
47	0.2370419	0.761137	0.964823	0.807609	0.699955	0.41431	0.590447	0.354464	83124657
48	0.08275111	0.885823	0.448601	0.624893	0.953688	0.406304	0.961445	0.66225	83652714
49	0.9752219	0.575637	0.702736	0.629236	0.133433	0.981215	0.241539	0.709081	26458173
50	0.27396951	0.394773	0.342527	0.141913	0.205086	0.894552	0.869968	0.394756	63587124
51	0.03386029	0.83055	0.367692	0.828902	0.205025	0.842448	0.256461	0.338064	82437165
52	0.19337344	0.747468	0.330471	0.876726	0.59874	0.310525	0.492731	0.967989	83624751
53	0.54583639	0.323125	0.226676	0.454859	0.405307	0.696795	0.28632	0.217763	25734168
54	0.58499764	0.231601	0.825726	0.186191	0.820778	0.017597	0.664458	0.550246	46172835
55	0.02170546	0.364869	0.539608	0.14863	0.639783	0.946712	0.059548	0.785145	85463172
56	0.30696648	0.734753	0.294931	0.329038	0.772831	0.83328	0.352475	0.685557	73862154
57	0.30110535	0.438414	0.170193	0.661961	0.953294	0.615941	0.894999	0.179034	65831427
58	0.2644183	0.970664	0.59823	0.470373	0.267674	0.087813	0.828804	0.244431	61345827
59	0.2595903	0.422614	0.446241	0.571379	0.050683	0.798343	0.241389	0.591279	65438172
60	0.38326981	0.343354	0.263145	0.666123	0.471582	0.687348	0.977201	0.855367	67845312
61	0.40827359	0.071535	0.966644	0.838811	0.656395	0.540019	0.408099	0.54771	68123574
62	0.49647289	0.676841	0.920963	0.166952	0.334008	0.565899	0.383712	0.272416	42186357
63	0.7614338	0.870207	0.724063	0.191715	0.624994	0.861322	0.173594	0.00013	31465278
64	0.21467578	0.78137	0.630669	0.496811	0.651052	0.029558	0.002691	0.440264	61342785
65	0.45831962	0.155932	0.220235	0.432785	0.460355	0.814117	0.281824	0.368121	38742165
66	0.14869695	0.942753	0.150751	0.864991	0.50596	0.083819	0.850969	0.695621	71625834
67	0.48032715	0.426068	0.582102	0.830286	0.891105	0.440241	0.187088	0.663542	57421683
68	0.98102873	0.756857	0.44825	0.294768	0.242033	0.565499	0.425124	0.784555	13578462
Data

Cell Formulas
Range	Formula
A11:H68	A11	=RAND()
I11:I68	I11	=CONCAT(RANK.EQ(A11:H11,A11:H11))

 

[faizee]

Does this work:

=INT(12345678+((87654321-12345678)*RAND()))

no. this is no ok
this shows following values, which are not ok

75017999​

21702855 we dont need 0 and any digits duplication​