|
VolunteerToday.com ~~ The Electronic Gazette for Volunteerism |
|
 |
|
|
~
May 2004 ~ Topics
|
Excelling With Excel: Finding Missing Numbers In A List
Of Numbers And Other Excel Tips
A co-worker (Betty) and I stumbled into this tip recently. We assign volunteers a number so they can log into VolunteerWorks and track their hours. We currently have over 1500 active volunteers. Imagine the number of persons that are inactive, prospects and applicants. We try to keep the number four digits in length and try to reuse inactive numbers. So we needed a way to sort through a list of active numbers and identify what numbers were missing. With over 1500 names, looking through the list and pulling each number out individually was a daunting task. This tip would not have happened if my co-worker and I had not collaborated. We each had a piece to the puzzle and a bit of blind luck to get the results we needed. Why it works is something neither of us can fully explain. But hey, you go with what works sometimes, eh? Start by opening Microsoft Excel, then follow the steps below.
-
Step One: In column A, enter the list of numbers you need to search. Example:
|
A
|
B
|
C
|
|
|
1
|
1
|
||
|
2
|
3
|
||
|
3
|
5
|
||
|
4
|
7
|
||
|
5
|
9
|
||
|
6
|
|||
|
7
|
|||
|
8
|
|||
|
9
|
This example represents the list that has the missing numbers you are looking for. Please note; these should be unique numbers, not repeated. In our case we were able to print the list of active volunteer numbers from VolunteerWorks into Excel. Cutting and pasting from other programs may work as well.
-
Step Two: In column B, enter the complete range of consecutive numbers to search. Example:
|
A
|
B
|
C
|
|
|
1
|
1
|
1
|
|
|
2
|
3
|
2
|
|
|
3
|
5
|
3
|
|
|
4
|
7
|
4
|
|
|
5
|
9
|
5
|
|
|
6
|
6
|
||
|
7
|
7
|
||
|
8
|
8
|
||
|
9
|
9
|
These are numbers you want to use, including the missing numbers. So for example, we know that 1, 5, 7, and 9 are in use. We want to find 2, 4, 6, and 8.
Tip: One way to enter a complete range of numbers in Excel is to enter the first three numbers, highlight them together with the cursor and then grab the little square dot in the lower right corner and drag it down until you have all the numbers you need. This is an Autofill feature.
-
Step Three: Enter the magical formula that took both Betty and I to create. That it works is our bit of luck. Don’t ask us to fully explain it. (Editor's note: the COUNTIF function is a Boolean operation that uses 0's and 1's for "yes" and "no" answers.)=COUNTIF(A$1:A$5,B$1:B$9)
|
A
|
B
|
C
|
|
|
1
|
1
|
1
|
=COUNTIF(A$1:A$5,B$1:B$9)
|
|
2
|
3
|
2
|
=COUNTIF(A$1:A$5,B$1:B$9)
|
|
3
|
5
|
3
|
=COUNTIF(A$1:A$5,B$1:B$9)
|
|
4
|
7
|
4
|
=COUNTIF(A$1:A$5,B$1:B$9)
|
|
5
|
9
|
5
|
=COUNTIF(A$1:A$5,B$1:B$9)
|
|
6
|
6
|
=COUNTIF(A$1:A$5,B$1:B$9)
|
|
|
7
|
7
|
=COUNTIF(A$1:A$5,B$1:B$9)
|
|
|
8
|
8
|
=COUNTIF(A$1:A$5,B$1:B$9)
|
|
|
9
|
9
|
=COUNTIF(A$1:A$5,B$1:B$9)
|
A$1:A$5 = the column where we entered the numbers we are going to search through. Notice that the numbers go from 1 to 5. Thus we are searching from cell 1 to cell 5 in column A. The $ sign is a way to freeze the number or letter that follows it. Otherwise Excel will advance the number or letter as it moves to the next consecutive cell. Try it without the $ signs and you will see what I mean.
B$1:B$9 = the column where we entered the range of consecutive numbers we are looking for.
Tip: Normally the COUNTIF formula looks through a range of numbers and counts how many times it appears and gives you a total. So if we had = COUNTIF(A$1:A$5,3) Excel would look through column A and count how many times the number 3 appears. In our case - only once. So we would get the number 1 in column C where we entered the formula. If there were no number 3 we would get a 0 and if there were two 3’s we would get 2.
In our example we get the following result:
|
A
|
B
|
C
|
|
|
1
|
1
|
1
|
1
|
|
2
|
3
|
2
|
0
|
|
3
|
5
|
3
|
1
|
|
4
|
7
|
4
|
0
|
|
5
|
9
|
5
|
1
|
|
6
|
6
|
0
|
|
|
7
|
7
|
1
|
|
|
8
|
8
|
0
|
|
|
9
|
9
|
1
|
Where the 0 appears in column C, the number next to it in column B is not to be found in column A. Thus these are the missing numbers we are looking for.
In our example it is easy to see what numbers we need, proof that it works. But with 1500 numbers, it is still a daunting task to gather them together.
To gather the numbers, perform a sort on columns B and C. Choose C as the Sort By field and choose Ascending. The Sort Feature can be found in the top Menu. Click on Data>Sort.
After the Sort, you will end up with the following:
|
A
|
B
|
C
|
|
|
1
|
1
|
2
|
0
|
|
2
|
3
|
4
|
0
|
|
3
|
5
|
6
|
0
|
|
4
|
7
|
8
|
0
|
|
5
|
9
|
1
|
1
|
|
6
|
3
|
1
|
|
|
7
|
5
|
1
|
|
|
8
|
7
|
1
|
|
|
9
|
9
|
1
|
So, we have discovered that volunteer numbers 2, 4, 6, and 8 are not being used. That is, they cannot be found in column A and are not in the list of Active volunteer numbers. The 0 indicates this and the Sort feature gathers them together in consecutive order. If we copy and paste them into a new Excel table we can readily track what numbers we use and what numbers are still available for use.
If you take the time to learn all the steps to do this, you will truly be "Excel"ing.
QUICK TIP: Adusting the View on Your Monitor
Today’s Monitors usually have a few buttons on them to adjust the view on your monitor. One of the most reoccurring problems when you get a new computer is being able to what’s on the screen. (This is actually two tips in one.)
In MS Windows Choose: Start > Settings > Control Panel.
Next,
find and click on the Display icon and click on the Settings
tab. Adjust the Screen Resolution downward. Less Resolution.
This usually makes the window appear bigger.
Choose OK.
Next (here's the second tip), if you have black areas near the edges of your monitor you need to adjust the screen. Read your monitor’s manual or if daring, play around with the buttons on the front of the monitor. These buttons usually include icons that show a monitor stretching horizontally or vertically and showing the window moving left or right and up and down. These are the buttons that will make the window fit the screen.
Drop me a note at Michael@MichaelStills.com.
Return to Top
A Service of MBA
Publishing-A subsidiary of Macduff/Bunt Associates All materials copyright
protected ©2007
925 "E" Street Walla Walla, WA 99362 (509) 529-0244 FAX: (509) 529-8865
EMAIL: editor@volunteertoday.com
The content of all linked sites are beyond the
control Volunteer Today and the newsletter assumes no responsibility for their
content.