to find missing serial numbers in randomly generated numbers

Discussion in 'Microsoft Excel Worksheet Functions' started by Guest, Dec 1, 2005.

  1. Guest

    Guest 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
     
    Guest, Dec 1, 2005
    #1
    1. Advertisements

  2. Guest

    Guest Guest

    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:

    > 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
     
    Guest, Dec 1, 2005
    #2
    1. Advertisements

  3. 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
     
    Herbert Seidenberg, Dec 1, 2005
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jim S

    inserting randomly generated numbers into a table

    Jim S, Feb 10, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    134
    Frank Kabel
    Feb 10, 2004
  2. Guest

    Randomly Generated List / Macro

    Guest, Apr 8, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    145
    Richard Buttrey
    Apr 9, 2006
  3. Johncobb

    I want random numbers generated without repeating numbers

    Johncobb, Sep 7, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    1,630
    Gord Dibben
    Sep 7, 2006
  4. Johncobb

    I want random numbers generated without repeating numbers

    Johncobb, Sep 7, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    186
    Johncobb
    Sep 7, 2006
  5. Guest

    Corrosponding excel generated numbers with 1st row numbers

    Guest, May 31, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    158
    Guest
    May 31, 2007
Loading...

Share This Page