+ Reply to Thread
Results 1 to 7 of 7

how to find missing numbers in an excel spreadsheet

  1. #1
    Registered User
    Join Date
    12-24-2012
    Location
    melbourne
    MS-Off Ver
    Excel 2003
    Posts
    10

    how to find missing numbers in an excel spreadsheet

    hey guys, i have one quick question. i need to find the missing numbers from 12 rows of 20 numbers.

    ie, i have these numbers (only between 1-80) and need to find the *normally* 2 numbers that are not in that list between 1-80. there are 14 rows in this example

    1 3 4 8 10 13 14 17 18 28 32 46 48 55 56 57 58 59 64 75
    4 12 13 14 17 21 22 23 30 32 35 37 40 44 54 58 61 70 78 80
    5 7 10 11 17 20 25 28 30 34 35 36 39 40 49 51 55 60 62 64
    3 5 12 18 23 25 41 48 49 50 51 56 58 60 64 66 70 76 78 79
    3 11 16 17 19 21 22 25 26 28 29 40 44 47 58 60 63 69 75 78
    1 2 6 7 13 16 23 26 35 36 38 39 46 52 53 58 63 70 76 78
    7 10 17 20 21 27 29 30 41 44 45 46 47 50 51 55 69 72 73 74
    1 3 5 6 7 8 12 16 17 22 28 32 40 47 50 63 65 70 77 80
    2 3 6 7 9 11 14 25 30 41 44 48 50 61 63 64 71 78 79 80
    5 10 11 16 18 19 20 26 38 41 47 49 51 54 58 66 67 70 73 80
    4 10 17 18 23 28 30 31 34 35 37 38 39 42 43 49 51 52 57 63
    2 3 14 15 19 20 22 28 29 31 38 41 48 51 52 62 64 69 70 77
    11 17 22 28 29 32 33 37 44 46 48 49 51 52 54 58 60 63 66 77
    1 2 4 12 25 34 35 36 40 41 42 44 47 49 62 66 69 72 74 78

    these numbers wont be the same every time, but in theory after i enter 12 rows there should be only 5/6 numbers left max.

    i need a script so that when i copy/paste 12 rows, i can be presented with the numbers not in that field. or even if i have a field of 1-80 displayed, then i copy paste my numbers in the fields below and can somehow highlight which numbers between 1-80 aren't in my pasted fields on the 1-80 field.

    hopefully you guys can help me - btw the numbers missing here are 24-68

    cheers, brad

    ps im using excel starter 10 if that makes a difference
    Last edited by brmiau; 12-24-2012 at 02:18 AM.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: how to find missing numbers in an excel spreadsheet

    Hi brmiau welcome to the forum

    It can be achieved in many ways... but expecting an excel file with the expected way of the output.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: how to find missing numbers in an excel spreadsheet

    hi brmiau, welcome to the forum & a merry Christmas eve to you. 1 way is to use a helper column & count how many times each number appears. then do an INDEX to find which ones are 0 counts. attached an example for you
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Registered User
    Join Date
    12-24-2012
    Location
    melbourne
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: how to find missing numbers in an excel spreadsheet

    Thank you so much for that attached file.

    Its perfect and exactly what I was after.

    Hope you had a great Xmas and have a happy new year!

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,410

    Re: how to find missing numbers in an excel spreadsheet

    With example file from benishiryo, another way without helper columns:
    D18:
    Please Login or Register  to view this content.
    Confirmed with Ctrl-Shift-Enter
    Drag down till getting errors.
    Quang PT

  6. #6
    Registered User
    Join Date
    12-24-2012
    Location
    melbourne
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: how to find missing numbers in an excel spreadsheet

    just wondering if someone could create a form where i would enter 2 numbers into 2 seperate boxes, and those numbers would be highlighted/bolded/shown in red from a list of 30 rows of numbers x 20 numbers in each row

    ie. number: ... (5) number: ... (59)

    1 28 19 20 5 9 80 72 12 38 50 53 59 83 ...

    so picture something like that, but with 30 rows of numbers x 20 numbers in each row

    hope that makes sense

    thanks, brad

  7. #7
    Registered User
    Join Date
    12-24-2012
    Location
    melbourne
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: how to find missing numbers in an excel spreadsheet

    bump .... ^^^

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1