Microsoft Excel - Find missing or repeat number from a range

Asked By John on 21-Jan-13 11:44 AM
I would like to find missing and repeat number from a range.
1 4 5
2 22 12
3 29 14
6 30 15
7 31 50
8 32 52
9 49 53
10 43 54
11 44 55
13 49 66
15 51 67
13 56 68
17 57 69
18 58 70
19 59 71
20 60 72
21 61 73
23 62 74
24 63 75
27 64 76
28 65 77
Harry Boughen replied to John on 21-Jan-13 06:26 PM
Hello John,
This is a bit crude but I was not sure how you are planning to use the results.  The intermediate calculations can be hidden or moved to a hidden sheet.
john2.zip
Regards
Harry
Donald Ross replied to John on 22-Jan-13 11:59 PM
John.

are your range of numbers set to bebetween 1 - 100? 
you can accomplish this using conditional formating with 2 rules.

First look for duplicates in your data and in a second row or over several rows where you list all numbers between 1-100 like Harry did in his example.

second look for duplicate in your original data using a different color.

missing duplicate.zip

hope this gives you another way to accomplish what you are going for.

Don
John replied to Donald Ross on 23-Jan-13 12:06 PM
There is no formula or macro in your zip file
Donald Ross replied to John on 23-Jan-13 09:45 PM
it was set up as conditional formatting,  there is no formula or macro..
does that work for you? 

Sorry for the confusion.
Don

John replied to Donald Ross on 23-Jan-13 10:06 PM
It is working good. Thanks