For step 3, do you mean that you (as a human being) are scanning the adjustment table and doing a copy-and-paste? It seems like there could be some formulas to automate that. Ann Turner On Tue, Oct 10, 2017 at 12:55 AM, Dennis Wright <[email protected]> wrote: > For those that have a spreadsheet with a large number of SNPs presently > showing hg19 positions, you are probably wondering what is involved in > producing hg38 positions. Here is how I went about it. > > I have been able to organise my SNP positions with the help of the > mutation page produced by Alex Williamson, http://www.ytree.net/hg19tohg3 > 8.html. > > 1. I took the list of my 695 SNPs and pasted the hg19 positions into > column A of a new spreadsheet. > > 2. Copy this list into Column D > > 2. Sort Column D from smallest to largest > > 3. In column E insert the adjustment specified in Alex's listing. As > these are ranges, copying in the adjustments is quite quick, and this is > the worst part of the whole procedure. > > 4. Calculate the new position in column F with a formula that adds or > subtracts column E from column D as appropriate. > > 5. In column B, Cell B1, insert the formula =VLOOKUP(A1,D:F, 3,FALSE) > and copy this formula to the bottom of your list of positions. > > 6. Column B now holds the hg38 positions of your SNPs in the order of > your original list. Copy this Column, > > 7. 'Paste Special' choosing 'Values' to your original SNP listing > spreadsheet and job is done! > > I hope this helps others do their conversions (Thanks Alex) > > Kind regards all! > > *Dennis Wright* > Donnchadh Mac an tSaoir > Irish Type III R-L226 > FGC12290 > /"We are merely the present-day custodians of our Ancestors genes." > > > / > > On 10/10/2017 9:09 AM, McDonald, J Douglas wrote: > >> I got a reply to a question about this. Its pretty clear. >> >> It says: >> >> >> "Remapped vcf and BED files will be generated from the remapped BAM >> files, though it may take a few days after the re-run is complete for them >> to be available. >> >> A remapped downloadable BAM file will be available on request at some >> point after the re-run. We do not yet know when that will be. >> Those who have a hg19 BAM file will have to request a hg38 BAM file. We >> won't automatically replace them. " >> >> That means that all the raw data, if you re-download all of it, will be >> coherent. This is very good news >> if its accurate. >> >> Doug McDonald >> ________________________________________ >> From: GENEALOGY-DNA >> Dear All, >> >> Earlier today I received an e-mail from FTDNA that they will be moving >> from >> Build 37 to Build 38 for the BigY. >> ------------------------------- >> To unsubscribe from the list, please send an email to >> [email protected] with the word 'unsubscribe' without >> the quotes in the subject and the body of the message >> >> > > ------------------------------- > To unsubscribe from the list, please send an email to > [email protected] with the word 'unsubscribe' without > the quotes in the subject and the body of the message >

I received a note off-list reminding me about the LiftOver tool at the UCSC Genome Browser website: https://genome.ucsc.edu/cgi-bin/hgLiftOver There has been some discussion on the mailing list for the ISOGG tree about whether there are some "gotchas" in that method, though. Dennis, could you review that method and see if it comes up with the same results? Ann Turner On Tue, Oct 10, 2017 at 4:06 AM, Ann Turner <[email protected]> wrote: > For step 3, do you mean that you (as a human being) are scanning the > adjustment table and doing a copy-and-paste? It seems like there could be > some formulas to automate that. > > Ann Turner > > On Tue, Oct 10, 2017 at 12:55 AM, Dennis Wright <[email protected]> > wrote: > >> For those that have a spreadsheet with a large number of SNPs presently >> showing hg19 positions, you are probably wondering what is involved in >> producing hg38 positions. Here is how I went about it. >> >> I have been able to organise my SNP positions with the help of the >> mutation page produced by Alex Williamson, http://www.ytree.net/hg19tohg3 >> 8.html. >> >> 1. I took the list of my 695 SNPs and pasted the hg19 positions into >> column A of a new spreadsheet. >> >> 2. Copy this list into Column D >> >> 2. Sort Column D from smallest to largest >> >> 3. In column E insert the adjustment specified in Alex's listing. As >> these are ranges, copying in the adjustments is quite quick, and this is >> the worst part of the whole procedure. >> >> 4. Calculate the new position in column F with a formula that adds or >> subtracts column E from column D as appropriate. >> >> 5. In column B, Cell B1, insert the formula =VLOOKUP(A1,D:F, 3,FALSE) >> and copy this formula to the bottom of your list of positions. >> >> 6. Column B now holds the hg38 positions of your SNPs in the order of >> your original list. Copy this Column, >> >> 7. 'Paste Special' choosing 'Values' to your original SNP listing >> spreadsheet and job is done! >> >> I hope this helps others do their conversions (Thanks Alex) >> >> Kind regards all! >> >> *Dennis Wright* >> Donnchadh Mac an tSaoir >> Irish Type III R-L226 > FGC12290 >> /"We are merely the present-day custodians of our Ancestors genes." >> >> >> / >> >> On 10/10/2017 9:09 AM, McDonald, J Douglas wrote: >> >>> I got a reply to a question about this. Its pretty clear. >>> >>> It says: >>> >>> >>> "Remapped vcf and BED files will be generated from the remapped BAM >>> files, though it may take a few days after the re-run is complete for them >>> to be available. >>> >>> A remapped downloadable BAM file will be available on request at some >>> point after the re-run. We do not yet know when that will be. >>> Those who have a hg19 BAM file will have to request a hg38 BAM file. We >>> won't automatically replace them. " >>> >>> That means that all the raw data, if you re-download all of it, will be >>> coherent. This is very good news >>> if its accurate. >>> >>> Doug McDonald >>> ________________________________________ >>> From: GENEALOGY-DNA >>> Dear All, >>> >>> Earlier today I received an e-mail from FTDNA that they will be moving >>> from >>> Build 37 to Build 38 for the BigY. >>> ------------------------------- >>> To unsubscribe from the list, please send an email to >>> [email protected] with the word 'unsubscribe' without >>> the quotes in the subject and the body of the message >>> >>> >> >> ------------------------------- >> To unsubscribe from the list, please send an email to >> [email protected] with the word 'unsubscribe' without >> the quotes in the subject and the body of the message >> > >

Maybe there is an ‘if-then’ formula for that step, however it took perhaps five minutes for my list of nearly 700 SNPs to read the table and enter the adjustments into the spreadsheet. Dennis Wright Irish Type III > On 10 Oct 2017, at 10:06 pm, Ann Turner <[email protected]> wrote: > > For step 3, do you mean that you (as a human being) are scanning the > adjustment table and doing a copy-and-paste? It seems like there could be > some formulas to automate that. > > Ann Turner > > On Tue, Oct 10, 2017 at 12:55 AM, Dennis Wright <[email protected]> > wrote: > >> For those that have a spreadsheet with a large number of SNPs presently >> showing hg19 positions, you are probably wondering what is involved in >> producing hg38 positions. Here is how I went about it. >> >> I have been able to organise my SNP positions with the help of the >> mutation page produced by Alex Williamson, http://www.ytree.net/hg19tohg3 >> 8.html. >> >> 1. I took the list of my 695 SNPs and pasted the hg19 positions into >> column A of a new spreadsheet. >> >> 2. Copy this list into Column D >> >> 2. Sort Column D from smallest to largest >> >> 3. In column E insert the adjustment specified in Alex's listing. As >> these are ranges, copying in the adjustments is quite quick, and this is >> the worst part of the whole procedure. >> >> 4. Calculate the new position in column F with a formula that adds or >> subtracts column E from column D as appropriate. >> >> 5. In column B, Cell B1, insert the formula =VLOOKUP(A1,D:F, 3,FALSE) >> and copy this formula to the bottom of your list of positions. >> >> 6. Column B now holds the hg38 positions of your SNPs in the order of >> your original list. Copy this Column, >> >> 7. 'Paste Special' choosing 'Values' to your original SNP listing >> spreadsheet and job is done! >> >> I hope this helps others do their conversions (Thanks Alex) >> >> Kind regards all! >> >> *Dennis Wright* >> Donnchadh Mac an tSaoir >> Irish Type III R-L226 > FGC12290 >> /"We are merely the present-day custodians of our Ancestors genes." >> >> >> / >> >>> On 10/10/2017 9:09 AM, McDonald, J Douglas wrote: >>> >>> I got a reply to a question about this. Its pretty clear. >>> >>> It says: >>> >>> >>> "Remapped vcf and BED files will be generated from the remapped BAM >>> files, though it may take a few days after the re-run is complete for them >>> to be available. >>> >>> A remapped downloadable BAM file will be available on request at some >>> point after the re-run. We do not yet know when that will be. >>> Those who have a hg19 BAM file will have to request a hg38 BAM file. We >>> won't automatically replace them. " >>> >>> That means that all the raw data, if you re-download all of it, will be >>> coherent. This is very good news >>> if its accurate. >>> >>> Doug McDonald >>> ________________________________________ >>> From: GENEALOGY-DNA >>> Dear All, >>> >>> Earlier today I received an e-mail from FTDNA that they will be moving >>> from >>> Build 37 to Build 38 for the BigY. >>> ------------------------------- >>> To unsubscribe from the list, please send an email to >>> [email protected] with the word 'unsubscribe' without >>> the quotes in the subject and the body of the message >>> >>> >> >> ------------------------------- >> To unsubscribe from the list, please send an email to >> [email protected] with the word 'unsubscribe' without >> the quotes in the subject and the body of the message >> > > ------------------------------- > To unsubscribe from the list, please send an email to [email protected] with the word 'unsubscribe' without the quotes in the subject and the body of the message