NAQCC Sprint Log cross-checking 1. Copy log from autologger into a text file in EditPad Lite 2. If necessary, QSO lines in a log are converted to the correct format 3. K3WWP 20 0036 WA8REI MI 0557 1 2 Which is: CALL BAND TIME WKD STATE NAQCC# NEW_MULT POINTS 4. When all logs are received and converted, the text file is imported into an Excel spreadsheet and a column for QSO Number is added. (Column C) 5. Columns A-I are copied into the Log Checking shell spreadsheet and the columns there are as follows: A - YourCall B - Band C - QSO Number D - QTIM E - WKD F - ST G - INFO H - SNR I - PT J - Field(s) containing errors K - Erroneous info L - L here means WKD submitted a log M - x here means QSO in both logs 6. Do 'Make Log Checking Sheet' Query in Access with the NAQCC database. 7. Export object as 'Make Log Checking Sheet.xlsx' in the NAQCC\Sprints folder 8. Load into Excel and copy to Memberlist page in Current_Log_Checking_Shell 9. Convert column B to numbers by copying values from CONVERT to NR columns ------------------------------------------------------ A. BE SURE NAME 'CALL' AREA ON MEMBERLIST PAGE INCLUDES ALL CALLS (FORMULAS - NAME MANAGER) SORT COLUMNS A-M ON A C CHECK: CALLSIGNS THAT DON'T MATCH MEMBER NUMBER and/or STATE R2: =VLOOKUP(E2,New_LC_Shell.xls!CALL,2,FALSE) S2: =VLOOKUP(E2,New_LC_Shell.xls!CALL,3,FALSE) A 1 IN COLUMN T and/or U NDICATES A POSSIBLE STATE/NUMBER ERROR A #NA IN COLUMN R INDICATES A CALLSIGN ERROR OR A NON-MEMBER QSO ------------------------------------------------------ B. CHECK FOR CORRECT POINT VALUE AFTER CORRECTING ABOVE INFO SORT COLUMNS A-M ON G E ALL MEMBER QSO'S (2 PT) SHOULD NOW BE AT THE TOP, AND NON-MEMBER QSO'S (1 PT) AT THE BOTTOM ------------------------------------------------------ C. SORT COLUMNS A-M ON A B E CHECK: DUPLICATE QSO'S N2: =IF(AND(A2=A1,B2=B1,E2=E1),1,"") A 1 IN COLUMN N INDICATES A DUPLICATE QSO. ------------------------------------------------------ D. SORT COLUMNS A-M ON A F C CHECK: MULTIPLIER COUNT O2: =IF(F2=F1,1,"") P2: =IF(ISNUMBER(H2),1,"") Q2: =IF(AND(O2=P2,A2=A1),1,"") A 1 IN COLUMN Q MEANS AN ERROR IN MULTIPLIER COUNT SORT COLUMNS A-M ON A C AND FIX MULTIPLIER COUNTS IF NEEDED ------------------------------------------------------ E. SORT COLUMNS A-M ON D BE SURE ALL TIMES ARE IN THE CORRECT TIME FRAME. ------------------------------------------------------ F. COPY RESULTS FROM WEB PAGE TO EDITPAD LITE REMOVE LEADING '*', ETC. AND BLANK AND HEADING LINES BE SURE TO REMOVE ALL PORTABLE INDICATORS - /9 /VE etc. COPY ALL TO SHEET4 (Results), CELL A1.... BE SURE FORMULA AND 'L' IN COLUMNS B AND C ARE PRESENT FOR ALL RESULT LINES COPY FORMULA =VLOOKUP(E2,Results!B$1:C$200,2,FALSE) TO CELL L2 AND FILL DOWN THE COLUMN THROUGH THE LAST QSO LINE. SORT COLUMNS A-K ON E AN L IN COLUMN L INDICATES THE STATION WKD TURNED IN A LOG HIGHLIGHT COLUMN L AND CONVERT TO VALUES. SORT COLUMNS A-M ON L M D COPY VALUES FROM COLUMN W TO COLUMN M REPEAT PREVIOUS TWO STEPS UNTIL MOST ALL QSO'S ARE CHECKED AN x INDICATES A QSO SHOWS UP IN BOTH LOGS CHECK THOSE THAT SHOW A blank MANUALLY AND CHANGE TO AN x IF IN BOTH LOGS or nilc IF NOT IN LOG. ------------------------------------------------------ G. SORT A C RECOMPUTE ALL SCORES TO ACCOUNT FOR CHANGES MADE IN ABOVE PROCEDURES Created with Microsoft OneNote 2010 One place for all your notes and information