to find missing serial numbers in randomly generated numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a set of randomly generated four digit numbersin ascending order. I
want to find out and make a list of all the left out 4 digits. Can you help
me
 
Try this solution:
Assume that your 4 digit No's are in range(A2:An)!

Enter in B2:
=A3-A2 and fill it down as necessary
in C2:
=IF($B2=1,"",IF($A2+CELL("column",C$1)-2<$A3,$A2+CELL("column",C$1)-2,""))
and fill it right to last column (IV), and down as necessary!

In columns C:IV you get the left out . To say the truth there is a limit of
this solution: if more then 254 4 digits miss between two random No, then
those above 254 will not appear in the list!

Regards,
Stefi

„B.H. Hadi†ezt írta:
 
Set up your data as shown:
set_rand set_all Criteria
10 10 11 test
13 11 12 TRUE
14 12 15
18 13 16
20 14 17
22 15 19
.... ... ...

For simplicity only 2 digits are used.
The first column contains your random numbers.
Fill in all the numbers from 10 to 99 in the second column.
Select the headers and the data below it for
set_rand, set_all and Criteria and
Insert > Name > Create > Top Row
Enter this formula into the TRUE cell:
=COUNTIF(set_rand,set_all)=0
Data > Filter > Advanced Filter
Copy to another location
List Range: select set_all and its header
Criteria Range: =Criteria
Copy to: anywhere or column 3 as shown
 
Back
Top