Jim Tiffin, Jr.
Oneida Indian Nation
Problem
Trying to keep track of what each wire does within a building`s infrastructure can be difficult and time-consuming.
Solution
Use Microsoft Excel to maintain a list of vital information and massage the data to provide quick insights on everything from specific devices to locations and user names.
Procedure
Imagine that you have a 200-pair backbone cable between the equipment room and a closet, and you need to manage its use. In Excel, use the first row to type (in bold) the headings of each column of information that the installer needs for tracking pair usage.
Include a border line beneath each heading to help Excel separate the header row from the list rows.
Type in about 10 to 15 pair numbers in the corresponding column, then enter all the other information in their corresponding columns. (Be consistent with word use; for example, Excel considers abbreviations as separate words.)
Before entering the rest of your other pair numbers, try out Excel`s filtering and sorting features to make sure it`s providing the information you need.
For Excel to recognize your list, you must have an "active" cell within the list. Move your cursor to the first column and the first row beneath the header row. Go to "Data" on the menu bar and choose "Filter." Choose "AutoFilter" from the new window.
Your header row should now have small down-arrows next to each column title. Click on one of them. In the "Device" column, for example, you`ll see another window that shows "All," "Top 10," "Custom," and then an alphabetized list of all the entries in that column. By picking one of these entries, Excel will filter your list so that it only contains the rows that match your selection--handy if your boss wants a printout of all the modem numbers at a certain facility. Or if your boss wants just the fax numbers for the finance department, you can do a double-filter by choosing "Fax" from the "Device" column and "Finance Department" from the "Location" column.
Experiment with other filtering features. "Top 10" lets you choose the highest or lowest values; for example, to find cables with the highest scores from a list of test results obtained from a scanner. "Custom" lets you find all entries that contain a certain word; for example, when you want to show all the pairs that terminated on the white-blue (W/Bl) cable pair.
Experiment with the sorting features. Go to "Data" on the menu bar and choose "Sort," then the type of sorting you want--multiple or singular. For instance, instead of sorting your cable based on pair number, you can see it alphabetically by user, or even numerically by phone.
If you`re happy with Excel`s filtering and sorting features as they pertain to your cable-management task, enter the remaining pair numbers and their associated information. Save the file.
Print out the entire spreadsheet of your data to be kept on-site for everyone to write down moves, adds, and changes. Incorporate updates regularly on your Excel program. (And, as always, make a backup copy!)
By using Excel`s "Sort" feature, you can determine the length, headroom, or even the cable number for all of your Category 5 cable in a certain building. You can also "filter" out the best-performing cables by using the "Top 10" filter feature.
Jim Tiffin, Jr. is a technician for the Oneida Indian Nation (Oneida, NY) telecommunications department.