GENEALOGY-DNA-L Archives

Archiver > GENEALOGY-DNA > 2010-11 > 1289760806


From: Mark Jost <>
Subject: Re: [DNA] New FTDNA Project Format
Date: Sun, 14 Nov 2010 12:53:26 -0600


As the amateur I am, I have been playing around with a workaround method to
assist converting Multi-Copy (MC) markers concatenated to decatenate
spreadsheet columns that are displaying both actual hyphenated markers and
when some of those same type MC markers that appear as real Calendar Dates
for those who wish to copy and paste a public projects results into a Excel
spreadsheet. If something does not work, maybe some Excel experts can help
out.

Note:

As a project Administrator, downloading using Excel format may not require
some of these steps.

This process can save you much work in manually having to convert MC markers
from dozens or hundreds of fields and in multiple columns.

On the Colorized version of the webpage, individual alleles that were
Colorized showing off-modal values are not recreated when you save the page
as a new spreadsheet.

Using the Colorized version will help you add back deleted colored rows,
explained later.

Selecting entire sheet then using Font > Borders > All Borders


Process:

(Steps to copy a non-gap Public FtDNA Y-DNA project Results Classic or
Colorized webpage.)

Using IE8, open a YDNA project 'Y-DNA Results Colorized' Web page.

Change 'Page Size:' to fit all lines in project onto one page on the screen.

Choose: 'Save AS',

Select location to save file.

Customize the Default name of file 'Y-DNA Results Colorized' as needed.

Verify that the 'Save as type:' is set to: 'Webpage HTML ONLY'

Click 'Save' Button

Open Excel 2007 and click 'Open' and browse to your saved file location,
Change file type to all, and click on the saved MHTML Document (.mht) file
to open.

Go back to the original Webpage and Copy the Row containing the DYS STR
descriptions and paste back into the Spreadsheet and save.

Copy the original sheet an paste into a second sheet to work with. Clean up
the spurious junk in the top rows by deleting.

Delete all Label (merged cells) Rows making placement notes to returning
these rows later or just replace merged row with a new row and entering
lable data in a single (non-merged) cell. You can Colorize the entire Lable
row as needed. (These are merged cells and need to be removed for now
because the one of the final steps will not function if left as is.)

Select the Column you wish to convert.

Format cell, select Category= Date, then select 'Type' = '3/14' format.

Next select 'Find and Replace' using '/' and '&' respectively. Click
'Replace ALL'. (this changes the date issues)

You will notice that the field that previously showed the data as a Date is
now mm&dd&YYYY.

Now, next select 'Find and Replace' using '&2010' (or what year is shown)
and leave or change the 'Replace with:' box to show no characters (blank)
are entered. Click 'Replace ALL'.

Next select 'Find and Replace' using '-' and '&' respectively. Click
'Replace ALL'. (this changes the normal hypenated data)

The results in all fields in this column should now look like
"number&number".

Now format the entire column (or entire sheet) as "General" data.

Select the entire range of data (not the entire column as before) of the
above column you just performed the various replacements and using 'Text to
Columns' feature, make sure the radio button has selected "Delimited" and
click next.

Check the 'Other' box and enter the '&' and uncheck any other type of
delimiters. Click next and then click Finish. Click 'Ok' to the Replace the
contents of the destination cells question. This will replace the existing
data column and insert a new second (or more) column(s).

Repeat with all Multi-copy marker concatenated columns as needed.

Add back the deleted Color Merged Cell rows if desired or needed unless
further sorting options will be desired.

This sheet can be then be manually updated with new members as needed.


This thread: