Jim Reisert : DX4WIN | Counting QSOs by Entity |
by
updated 17 April 2011
Interested in how many times you have 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:
Here are the instructions for Excel 2010 (2007 may be the same):
Once this is done, you'll have two columns containing the results: Prefix and Total. But you can't sort it, so:
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.).