Using the WHACO! Database in MS Excel

Tutorial on Searching and Sorting

 

  1. Open the whaco.xls file with MS Excel.
  2. To make more of the file and each record visible on your screen, select the Pull Down Tab on the Zoom window as shown in Figure 1 and select 75% as shown in Figure 2. The screen will look something like Figure 3 when you are done.

 

Figure 1

 

Figure 2

 

Figure 3

 

Sorting the WHACO! Database.

  1. The WHACO! Database is sorted by Autograph-Category in alphabetic order. Within Autograph-Category it is sorted by Company-Document and then by date of the auction or catalog (Date). All three items are sorted in ascending order. For example, a Ben-Hur Motor Company (DE) certificate with in the Automobile category will have the most recent prices at the end of the list. In Figure 3 on the previous page the Stock Search International Auction #11 appears below the Auction #9 because it occurred a year later.
  2. To sort the database in the order of your choice. Select Data, Sort from the main menu as shown in Figure 4.

 

 

Figure 4

 

  1. The resulting criteria selection menu appears as in Figure 5 on the next page.

 

Figure 5

  1. If, for example, you want to look at a specific auction, select AUCT_CAT (auction/catalog) from the top pull-down tab in the “Sort by” block as shown in Figure 6. Be sure to leave the Ascending radio button on and the “My list has Header row” radio button on at the bottom.

 

Figure 6

  1. If you want to sort the auction or catalog by item number, select ITEM in the “Then by” block instead of DATE and leave the Ascending radio button on as shown in Figure 7 on the next page.
  2. In the last box also labeled “Then by” select “(none) because Auction and Item are enough to show unique records in order. Figure 8 shows “(none) being selected. [Note: In the normal sort order, by Autograph-Category, Company-Document, and Date — date is necessary because items are the focus of the database and will contain many different dates of sale or offer.]

Figure 7

 

Figure 8

  1. The resulting sort criteria would be as shown in Figure 9 and the WHACO! Database would look like Figure 10. Note that R.M. Smythe auction #181 is sorted in the order of the items. No sale and very inexpensive items are excluded. There are several items that do not appear in Figure 10 because they are off the screen to the right and the screen has to be scrolled to the right to see them. These items include the Estimate, Hammer, and Cost prices as well as Remarks and the Commission (also called buyers premium).

 

Figure 9

 

Figure 10

Freezing Database Frames.

  1. Since several columns of information are off to the right or the screen and thousands of records are off the screen below the last row, Freeze Panes will ensure that the column titles remain in sight as you scroll down through the database. Freeze Panes will also ensure that the Autograph-Category will remain in sight as you scroll to the right to check prices.
  2. First, select cell B2. This is the first column to the right of the Autograph-Category and the first column below the heading row. If you want to keep both Autograph-Category and Company-Document in view, select cell C2.
  3. With cell B2 selected, from the main menu select Window, Freeze Panes as shown in Figure 11.

Figure 11

  1. The result in Figure 12 only shows a highlighting above the second row and to the left of the second column (B).

 

Figure 12

 

  1. Figure 13 on the next page shows the result of scrolling down to the 6,865th record, Keep, Henry…the headings still appear at the top.

 

 

 

 

Figure 13

 

Using the FIND command with the WHACO! Database.

  1.  To Find information in the WHACO! Database, select the find command. There are three common ways to select find. The simplest way is to press the Control key on the keyboard and at the same time press “F” (ctrl-F).
  2.   On the main menu select the Edit command (Figure 14) and on the subsequent menu select Find (with the field glass icon as show in Figure 15).

 

Figure 14

 

Figure 15

  1.  If the Find command is on the tool bar it will appear as in Figure 16 and can be selected by clicking on it.

 

                       

                       Figure 16

 

  1.  The Find window appears as shown in Figure 17. In Figure 17, the Find is set to search “By Rows”. When it is set by rows and the search is for “Knox” it will find

Figure 17

Knox Tire & Rubber Company (Company-Document column (B)) in the Automobile category before it finds Henry Knox under the Autograph-Category column (A)).

  1. Figure 18 shows the “Match Case” checked. Figure 19 shows a search for “Knox” with the emphasis on the capital “K”. A search for “Knox” would not find the Hardknox Café, but would find Knoxville Gas & Electric.

Figure 18

 

Figure 19

 

  1. Figure 20 shows the first Find in the database: Knox Tire & Ruber Co. (OH).

 

Figure 20

  1.  Selecting Find Next goes to line 6,879 to find the first instance of Henry Knox as shown in Figure 21. The next six selections of Find Next would move down one line, while the seventh would find Knoxville Gas & Electric.

 

Figure 21

  1.  Select the control key and the home key simultaneously (ctrl-Home) to return to the A1 cell as shown in Figure 22.

Figure 22

  1.  Figure 23 shows the same Find, but with “Search: By Columns” selected. In this case, the first record selected will be 6,879 Henry Knox. This command forces Excel to look through the entire Autograph-Category column (A) before looking in the Company-Document column (B). The eighth Find Next would stop on record 620, the Knox Tire & Rubber Company in the Company-Document column.

 

Figure 23

  1.  Figure 24 shows “Look in: Values” selected instead of “Look in: Formulas”. For a text search, it doesn’t matter which is selected.

 

Figure 24

 

Figure 25

  1.  If “Search: By Columns” is selected as in Figure 24 and a cell in column B is selected as in Figure 25, the Find will stop first at the Knox Tire & Rubber Company.
  2.  Further selection of Find Next will step through the Company-Document column (B). The Knox & Lincoln Railroad is shown in the Find in Figure 26.

Figure 26

 

Using the AutoFilter command with the WHACO! Database.

  1.  A convenient way to search the WHACO! Database is with the AutoFilter command. Place the cursor anywhere below the heading line (B2 was used here and would be automatically selected with ctrl-Home is Freeze Pane has been set). From the main menu, select Data, Filter, AutoFilter as shown in Figure 27.

                 

Figure 27

  1.  Figure 28 shows the pull-down tabs that appear in each column.

Figure 28

  1.  As an example, the items in the WAHCO! Database from the R. M. Smythe auction #186 in April of 1999 will be displayed. Select the pull-down tab next to AUCT-CAT in column D as shown in Figure 29. A list of available auctions and catalogs appears. [Note the “(All)” and “(Custom…)” selections at the top of the list. They will be discussed later.]
  2.  Select “SM#186”.

                    

Figure 29

  1.  Figure 30 on the next page shows the beginning of the listing for the April 1999, auction #186. The listing will appear in database order: by Autograph-Category, then by Company-Document. If you had sorted, as shown in Paragraph 6 above, by AUCT_CAT, then by ITEM, the list would appear by ITEM number in the catalog.
  2.  To select another auction or catalog use the pull-down tab again and select the new auction/catalog.

 

 

 

 

 

 

 

 

Figure 30

  1.   To see the entire list again, use the pull-down tab and select “(All)” as shown in Figure 31.
  2.  To turn of the filter completely, use the main menu and select Data, Filter, AutoFilter again.

Figure 31

 

Using the Custom Filter commands with the WHACO! Database.

  1.  Figure 31 shows the “(Custom…)” selection as the third item in the pull-down list. The custom command allows tailored filtering that can include or exclude specific items. For this example the pull-down tab for the Company-Document column will be used. Figure 32 shows the pull down menu for the Company-Document column. Select it.

 

Figure 32

  1.  Figure 33 shows the Custom window as it first appears.

Figure 33

 

 

  1.  Figure 34 shows some of the options available to customize your search. For example, rather than the find command, a Filter could be set to “contains” and “Knox” in the right window on the same line. The query would have selected all the same items as the Find command, but put them all together.

Figure 34

  1.  Select “begins with from the pull-down menu and enter “sunflower” in the adjacent right hand window as shown in Figure 35.

 

Figure 35

  1.  As soon as you select “Show rows where: COMPANY-DOCUMENT begins with” the pull-down list will disappear and after entering “sunflower” the window will look like Figure 36 on the next page.

Figure 36

  1.  A second criterion can be selected. For example, in the second row select “contains” and use “arrow” as the criteria in the right window. Select the “Or” radio button. Figure 37 shows the second criterion being selected and the “Or” radio button selected.

Figure 37

  1.  Figure 38 shows the completed criteria.

 

Figure 38

  1.  Figure 39 shows the results. “Arrow” included Pierce-Arrow, Sharp Arrow, Arrow Gold, Lancaster & Reading Narrow Gauge, and Sparrow’s Point Athletic Assn. “Sunflower” included the Sunflower Gold Mining Co.

Figure 39

  1.  If the “And” radio button had been selected no items would have been selected because no items contained both “sunflower” and “arrow”.
  2.  If the “arrow” criteria had been “Arrow” the Narrow Gauge and Sparrow’s Point certificates would not have been found.
  3.  Another custom query might use all items sold for over $10,000 and within the last four years. [Note the “And” radio button. “Or” would have captured all items in the last four years and any older items selling for over $10,000.