Random and unique number between 12345678 to 87654321

faizee

Board Regular
Joined
Jan 28, 2009
Messages
214
Office Version
  1. 2016
Platform
  1. Windows
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
 
Not sure if this might need to be an array formula in 2016, but how about this?

COMPSTAT Template1
ABCDEFGHI
10Output
110.289910680.3264580.0289680.9010240.0975010.7449820.8889350.38031465817324
120.305008020.1940920.9413290.9114420.0910130.5545380.9640550.50591267238415
130.967745410.8795850.533850.0887140.0393740.5220020.7422670.6396612578634
140.074137240.5041810.3515160.8424380.4565480.5096090.7404650.25940184615327
150.519963020.3440580.8772890.8478220.9692750.0939890.433980.00815546231758
160.233440290.9348760.9523380.4370910.6764560.7642550.8021930.20328172165438
170.370222680.6416980.5146650.4751420.0795630.1628460.1133020.16691741238675
180.624426250.0718660.301320.1830640.3028140.1690880.6633590.77454738564721
190.203250040.8659270.4490160.7909180.1867180.2738430.991470.9283473548612
200.280772460.0824550.4865560.1101160.4608870.3763180.1325930.43289658172463
210.092438410.2600060.4701080.7967450.8024780.1372610.6942810.78545586521743
220.713200740.7289960.2827980.5674510.7466570.7200180.4184910.79548153862471
230.079793690.1367170.1516890.1141040.7951450.1847210.0984940.8153285462371
240.343683850.7840560.1171470.0619430.1931330.19520.0966090.30540621685473
250.771485850.4876830.2287860.5950630.2152550.0410550.757030.58146615637824
260.31260470.4546160.7201250.8090750.467870.7703660.8283190.78546687526413
270.08872380.1511530.7097360.8649790.5414810.1435030.8515670.35992786314725
280.104596790.8336410.8489780.2361920.1406030.255740.9104710.86708384367512
290.921054460.3004690.338380.9269270.9209460.7936780.1519010.74596527613485
300.863701560.3326990.7810930.3330920.9282180.4986910.6715360.29402627361548
310.60055990.4929450.1479440.4910910.4229870.8023810.1747430.12395223745168
320.198058870.1429720.4946590.9306550.8175250.6083010.2497230.1654468412357
330.937820950.33760.4106130.3325250.2274650.5000480.9008720.12161215467328
340.743118580.4563320.0749810.632660.7562360.5099090.0241230.21871725731486
350.925100410.5640420.3098990.4333090.2809490.1422540.5740670.80160314657832
360.408085680.9556980.9682520.2881170.304330.0223160.3071580.01990832165748
370.145942470.1003150.5774650.2113560.0368210.958930.2662850.46154467258143
380.06135370.279840.8128440.4341910.3857980.4186040.5484760.32936287135426
390.079754340.0435420.7641430.2934120.1774680.9474050.9797790.47986778356214
400.205081020.5746430.4282170.9765010.6499180.0687750.5677050.04747563512748
410.461412560.1640970.2049120.9027340.4320120.1802520.2638710.21158228613745
420.316196420.1183510.1412250.4626690.1660560.7347050.655610.82531258746231
430.532298550.092680.9663320.6265550.2687750.095590.0075760.24859837124685
440.466970240.155180.5515480.7938720.7825980.1069130.6081650.83423367523841
450.132836150.7465390.7102950.6249930.6646960.0228540.3851080.28131771243856
460.638427730.3040590.6961650.9205070.0566170.4395710.8600120.49915247318625
470.23704190.7611370.9648230.8076090.6999550.414310.5904470.35446483124657
480.082751110.8858230.4486010.6248930.9536880.4063040.9614450.6622583652714
490.97522190.5756370.7027360.6292360.1334330.9812150.2415390.70908126458173
500.273969510.3947730.3425270.1419130.2050860.8945520.8699680.39475663587124
510.033860290.830550.3676920.8289020.2050250.8424480.2564610.33806482437165
520.193373440.7474680.3304710.8767260.598740.3105250.4927310.96798983624751
530.545836390.3231250.2266760.4548590.4053070.6967950.286320.21776325734168
540.584997640.2316010.8257260.1861910.8207780.0175970.6644580.55024646172835
550.021705460.3648690.5396080.148630.6397830.9467120.0595480.78514585463172
560.306966480.7347530.2949310.3290380.7728310.833280.3524750.68555773862154
570.301105350.4384140.1701930.6619610.9532940.6159410.8949990.17903465831427
580.26441830.9706640.598230.4703730.2676740.0878130.8288040.24443161345827
590.25959030.4226140.4462410.5713790.0506830.7983430.2413890.59127965438172
600.383269810.3433540.2631450.6661230.4715820.6873480.9772010.85536767845312
610.408273590.0715350.9666440.8388110.6563950.5400190.4080990.5477168123574
620.496472890.6768410.9209630.1669520.3340080.5658990.3837120.27241642186357
630.76143380.8702070.7240630.1917150.6249940.8613220.1735940.0001331465278
640.214675780.781370.6306690.4968110.6510520.0295580.0026910.44026461342785
650.458319620.1559320.2202350.4327850.4603550.8141170.2818240.36812138742165
660.148696950.9427530.1507510.8649910.505960.0838190.8509690.69562171625834
670.480327150.4260680.5821020.8302860.8911050.4402410.1870880.66354257421683
680.981028730.7568570.448250.2947680.2420330.5654990.4251240.78455513578462
Data
Cell Formulas
RangeFormula
A11:H68A11=RAND()
I11:I68I11=CONCAT(RANK.EQ(A11:H11,A11:H11))
thanks,, it worked!

I dont know. how it does?
but thank you
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top