Announcement

Collapse
No announcement yet.

Add Columns for Polynomic Markers

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • #16
    Originally posted by prairielad View Post
    above will not work if there are other - in the document as it will mess up spacing by separating whats before and after - into adjacent columns

    ie in b.c.1775-1795 Tyrone, Ireland of kit holders description it will separate 1775 and 1795 shifting all values in that row right taking them out of alignment.

    So it depends on what your csv consists of to whether editing is feasible.
    Yes, that is a real problem that needs to be avoided.

    A moment ago, I have asked for a Christmas gift of a macro in Excel that would split columns. Maybe it would come sooner, at most five more days..., and I will post it here.

    In the meantime, I would like to improve on my previous post about importing into Excel. I have now tested it from Excel 2003 to Excel 2013. When importing data, select the first column, scroll to the column DYS413 (the last, the 7th column with multi-copy markers), press shift and click. Change the type of the entire selected area to Text. Continue with importing.

    Also let me present a rather heavy, but a foolproof workaround for imports when using Windows. Use free LibreOffice Portable http://portableapps.com/apps/office/...ffice_portable , just unpack it into your Documents folder or on your Desktop and run. Then drop your CSV file on the initial screen and answer simple questions about the import. Save as XLS and enjoy.

    Mac user could install a full version of LibreOffice from http://www.libreoffice.org/download/..._64&lang=en-US

    Mr W

    P.S.
    https://en.wikipedia.org/wiki/LibreOffice is not the only free game in town, another one is https://en.wikipedia.org/wiki/Apache_OpenOffice

    Comment


    • #17
      Originally posted by dna View Post
      Yes, that is a real problem that needs to be avoided.

      When importing data, select the first column, scroll to the column DYS413 (the last, the 7th column with multi-copy markers), press shift and click. Change the type of the entire selected area to Text. Continue with importing.

      []

      That worked! Thanks a lot,

      Jim

      Comment


      • #18
        Excel macro to split columns

        Code:
        Rem Downloaded from FTDNA forum.
        Rem This macro will split the 7 columns with multicopy markers.
        Rem 2015-12-25
        Rem Attribute VBA_ModuleType=VBAModule
        
        Sub splitCells()
            Dim lastRow As Long, lastColumn As Long, i As Long, j As Long, k As Long, cellValue As String, itemCount As Long, cellValues() As String
            
            With Sheets(ActiveSheet.Name)
            
                lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
                lastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
                
                For i = 2 To lastRow
                    For j = 7 To lastColumn
                         cellValue = Cells(i, j)
                         If InStr(cellValue, "-") > 0 Then
                            itemCount = UBound(Split(cellValue, "-"))
                            ReDim cellValues(itemCount)
                            cellValues = Split(cellValue, "-")
                            
                            If Cells(1, j) = Cells(1, j + 1) And IsEmpty(Cells(i, j + 1)) Then
                                Cells(i, j) = cellValues(0)
                                For k = 1 To itemCount
                                    Cells(i, j + k) = cellValues(k)
                                Next k
                            Else
                                Cells(i, j) = cellValues(0)
                                For k = 1 To itemCount
                                    .Columns((j + k - 1)).Offset(0, 1).Insert
                                    Cells(1, j + k) = Cells(1, j)
                                    Cells(i, j + k) = cellValues(k)
                                Next k
                            End If
                         End If
                    Next j
                Next i
        
            End With
        
        End Sub
        Enjoy this Christmas gift

        Mr W

        Comment


        • #19
          Version of the splitCells macro to be used in LibreOffice

          Code:
          Rem Downloaded from the FTDNA forum.
          Rem This macro will split the 7 columns containing multicopy markers.
          Rem 2015-12-25
          Rem Attribute VBA_ModuleType=VBAModule
          
          Rem For use in LibreOffice.
          Option VBASupport 1
          Option Compatible
          
          Sub splitCells()
              Dim lastRow As Long, lastColumn As Long, i As Long, j As Long, k As Long, cellValue As String, itemCount As Long, cellValues() As String
              
              With Sheets(ActiveSheet.Name)
              
                  lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
                  lastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
                  
                  For i = 2 To lastRow
                      For j = 7 To lastColumn
                           cellValue = Cells(i, j)
                           If InStr(cellValue, "-") > 0 Then
                              itemCount = UBound(Split(cellValue, "-"))
                              ReDim cellValues(itemCount)
                              cellValues = Split(cellValue, "-")
                              
                              If Cells(1, j) = Cells(1, j + 1) And IsEmpty(Cells(i, j + 1)) Then
                                  Cells(i, j) = cellValues(0)
                                  For k = 1 To itemCount
                                      Cells(i, j + k) = cellValues(k)
                                  Next k
                              Else
                                  Cells(i, j) = cellValues(0)
                                  For k = 1 To itemCount
                                      .Columns((j + k - 1)).Offset(0, 1).Insert
                                      Cells(1, j + k) = Cells(1, j)
                                      Cells(i, j + k) = cellValues(k)
                                  Next k
                              End If
                           End If
                      Next j
                  Next i
          
              End With
          
          End Sub
          Enjoy this Christmas gift

          Mr W

          Comment


          • #20
            A comment to the splitCells macro

            It is Christmas, so I had performed limited testing on Microsoft Excel 2013 and LibreOffice 5.0.2.

            You can save the code in a file with an extension BAS, as that is a standard extension for Excel (and LibreOffice) macros.

            Duplicated columns should be renamed..., for example instead of two columns DYS385, there should be one DYS385a and one DYS385b.

            I am aware that there could be different numbers of copies, and this macro only handles the most common scenario. But... I have received a gift and I am not complaining. Maybe I will ask for another gift later.

            Merry Christmas - Mr W

            Comment


            • #21
              Thanks! I'll give it a try later.

              Comment


              • #22
                Bummer!

                Well, thanks for the macro but I just discovered that Excel for Mac 2008 has no Visual Basic editor! Guess I will finally have to upgrade, as soon as the Xmas bills are paid off. Hope they have resolved the issues with El Capitan.

                Thanks a lot for your efforts and I'll just use your workaround in the meantime.

                Jim

                Comment


                • #23
                  Originally posted by jbarry6899 View Post
                  Well, thanks for the macro but I just discovered that Excel for Mac 2008 has no Visual Basic editor! Guess I will finally have to upgrade, as soon as the Xmas bills are paid off. Hope they have resolved the issues with El Capitan.

                  Thanks a lot for your efforts and I'll just use your workaround in the meantime.

                  Jim
                  Office 2008 for Mac OS X is the only version without Visual Basic for Applications...

                  You may want to try LibreOffice.

                  Mr W

                  Comment


                  • #24
                    Yep, I'll explore that as well. Using your workaround I was able earlier today to get a usable file, copy to the McGee utility, and confirm that the cluster assignments I had made in our project were reasonable with only one change needed. So I really thank you for the effort you have made to help out.

                    Jim

                    Comment


                    • #25
                      Originally posted by jbarry6899 View Post
                      Yep, I'll explore that as well. Using your workaround I was able earlier today to get a usable file, copy to the McGee utility, and confirm that the cluster assignments I had made in our project were reasonable with only one change needed. So I really thank you for the effort you have made to help out.

                      Jim
                      You are welcome. Glad to be able to assist.

                      Mr W

                      Comment

                      Working...
                      X