From: "Richard Ferch" 
To: 
Subject: Re: [Trlog] [N1MM] Re: MASTER.DTA vs Call History in N1MM?
Date: Mon, 21 May 2007 19:54:51 -0400

Hi Kirk,

Your TRMASTER.ASC file can be converted into a Call History file for N1MM
using Excel.

First, open the file in Excel as a space-delimited file using the text
import wizard. You should end up with a file with cells that look something
like:

        A               B               C       D       E       F
1       PA3AAV  =C14            =H14    =I27    =QPA
2       OH7AAC  =H1
3       JA9AA           =C25            =H3
4       JA7YAA  =C25            =H6
5       JA3AA           =C25            =H6     =I45
6       JA1AAT  =H1
7       DJ7AA           =C14            =H3
8       7J1AAI  =C25            =H5     =I45
9       AA0CY           =ANv            =H36    =K63    =NBOB   =QPa
10      AA1CE           =NDAVE  =QRi
11      AA1MI           =NPAUL  =QNh

When you do this a workbook containing only one worksheet is created, and
the worksheet has the name of the file, i.e. TRMASTER, which will show up on
a tab at the bottom of the window.

Next, create a second worksheet in the same workbook. You do this from the
Insert menu by selecting the Worksheet menu item. This should create a new
blank worksheet called "Sheet1".

Into Cell A1 of Sheet1 enter the formula: =TRMASTER!A1

Into Cell B1 enter the formula: =LEFT(TRMASTER!B1,2)

Copy the formula from Cell B1 into cells C1 to R1 (this allows for up to 17
entries in a row, which is the maximum possible in a TRMASTER file).

Verify that Cell R1 of Sheet1 contains the formula =LEFT(TRMASTER!R1,2).

Copy the formulas from Row 1 (A1:R1) into the other rows of the sheet, down
just far enough to include every row in your TRMASTER.ASC file. If your
TRMASTER.ASC file is really big, you might want to use an editor to break it
up into shorter more manageable segments, convert them, and then put them
back together later, but that's just for convenience and to make sure you
don't overrun Excel's limitation on the number of rows, which I believe is
65536 (this may depend on the version of Excel).

OK, now create a third worksheet in the same workbook, which will be called
"Sheet2".

Into Cell A1 of Sheet2 enter the formula: =TRMASTER!A1

Into Cell B1 of enter the formula:
=IF(ISERROR(MATCH("=N", Sheet2!$B1:$R1,0)), "",
    RIGHT(INDEX(TRMASTER!$B1:$R1, , MATCH("=N", Sheet1!$B1:$R1, 0)),
      LEN(INDEX(TRMASTER!$B1:$R1, , MATCH("=N", Sheet1!$B1:$R1, 0)))-2)) (I
have spread this onto three lines for better readability - in Excel just
enter it all as one single formula in the formula field. If there is a name
field anywhere in Row 1 of the TRMASTER.ASC file, this should put the name
without the leading =N into cell B1 of Sheet2).

If you have any Grid fields (=G) in your TRMASTER.ASC file, copy the formula
from B1 into C1 and then edit all three occurrences of "=N" to read "=G"; if
you don't have any grid fields, just leave C1 blank.

Leave D1 blank.

Copy B1 into E1 and then edit all three occurrences of "=N" to read "=A"
(this should put the ARRL Section, if there is one, into E1).

Copy B1 into F1 and then edit all three occurrences of "=N" to read "=Q"
(this should put the State/QTH field, if there is one, into F1).

Copy B1 into G1 and then edit all three occurrences of "=N" to read "=K"
(this should put the Check, if there is one, into G1).

Copy the formulas from Row 1 (A1:G1) into the other rows of the sheet, down
just far enough to cover every row.

Hopefully, you will see call signs in column A, names in column B, ARRL
sections in column E, states/QTHs in column F and checks in column G. Cells
where there is no corresponding data in TRMASTER.ASC should be blank.

If this worked, all you have to do is save Sheet2 as a CSV (comma-delimited)
file. With a bit of luck, it will be in the proper format for N1MM (and
Thucydides).

73,
Rich VE3KI / VE3IAY