I accidentally sent this to the wrong list. Sorry if you have had it twice. Allan points out that there are alternative methods using pivot tables. Also of course early handwritten and the typed two-column files typically have different numbers of entries - 40 and 160 instead of 375 if I recall. Jeff ----- Original Message ----- From: "Jeff Coleman" <[email protected]> To: <[email protected]> Sent: Thursday, November 25, 2004 9:47 PM Subject: Finding duplicate transcriptions from upload reports, etc. > In recent discussion of occasional double-keying by accident or > happenstance I offered details of how to find such occurrences in upload > reports, without accidentally including successive corrected uploads from > the same transcriber. Here it is. > ------ > Collect a downloaded upload report for a particular range. Simplest if you > keep Births, Marriages and Deaths in separate upload reports. > > Open it in Excel. > Sort in order of Year, Quarter, Page Number, Uploaded(date). > > Hide or shrink columns K to P if you prefer to see all of what is going > on. > > In Q1 put a suitable heading such as 'doubles?' > > In Q2 put the formula =IF(AND(F2=F3),(I2<>I3),"DUPLICATE","") > Fill down to last entry in upload report - let's call that line n . > > To count duplicates > > in cell Q (n+1) put formula =COUNTIF(Q1:Q(n),"DUPLICATE") > > Note that this formula does not work using n. It will be a number , like > 483 > or 975 or 1265 or whatever the last line is, in real use. > ========== > Other spreadsheet packages should allow similar functions. > ========== > To find gaps in the sequence of uploaded pages, use this formula in Q2 and > downwards instead. > =IF(AND((F3-F2<>1),(F3-F2<>0)),"page gap follows","") > ========== > I think Bob Phillips pointed out to me that you could use a function a bit > like this one, filled down a column on the right, to identify pages with > something other than 375 lines. > > =IF(G2<>375,375-G2,"") > > This version produces a positive result for short files - like those with > a changeover of surname letters, normally showing as 10, or 20 for XYZ, or > those with a number of two-line bracketed names. It will give a negative > result if there were handwritten additions. If it gives an unexpected > positive result there may be lines missing, and it may be worth looking at > by collecting a copy from Showfile and running through BMDVerify. > ============== > Hope this helps > > If other co-ordinators do other interesting things with upload reports, > they might be worth sharing. > > Jeff > > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.801 / Virus Database: 544 - Release Date: 24/11/2004