DX4WIN

App. Note 12

Counting Entities in DX4WIN

by - 17 January 2007

The recent VU7 activity got me wondering how many times I had worked each DXCC entity. You can use a Pivot Table in Excel to find the answer.

The first thing to know is that Excel spreadsheets are limited to 65,535 lines. If you logbook contains more than that many QSOs, you will have to filter it in some way. In my case, I eliminated QSOs with my home country using an F8 search:

Prefix = !K

This reduced my log of almost 80K QSOs down to a more manageable 51K.

Next, open the Logbook window (Window -> Logbook), ensure that it includes the Prefix field, and save it to a .CSV file.

Now open the .CSV file in Excel. For the purposes of this note, I'm using Excel 2003. I found the instructions for creating the Pivot Table in this part of the on-line help:

Count how often multiple values occur by using a PivotTable report

The instructions from the Help are as follows:

1. Select the column containing the data (in this case, Prefix).
2. On the Data menu, click PivotTable and PivotChart Report.
3. Click Finish.
4. Drag the Prefix column label from PivotTable Field List to Drop Row Fields Here.
5. Drag the same column label from PivotTable Field List to Drop Data Items Here.

Once this is done, you'll have two columns containing the results: Prefix and Total. But you can't sort it, so:

1. Select the cell containing the word Prefix.
2. Scroll down to the bottom of the spreadsheet, where it says Grand Total.
3. Holding down the SHIFT key, select the cell containing the last count value in the Total column. This will select all the Prefix/Total cells in your worksheet.
4. ^C to copy these values to the clipboard.
5. Open a blank spreadsheet or worksheet.
6. Click on a blank cell and then paste the copied values into the new worksheet (^V).

Now you can use the sorting function in Excel to put the values in numerical order.

You can filter your logbook any way you wish, and sort any data value you choose (callsign, CQ Zone, etc.).