SOLVED

How can I merge 6 columns??

%3CLINGO-SUB%20id%3D%22lingo-sub-2728542%22%20slang%3D%22en-US%22%3EHow%20can%20I%20merge%206%20columns%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2728542%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20normalising%20data%20to%20third%20normal%20form%20and%20I%20want%20to%20merge%20six%20columns%20of%20data%20into%20one%20(SkillsIDFK)%20column%20but%20the%20merged%20data%20has%20to%20be%20on%20the%20next%20line%20with%20the%20StaffIDFK%20repeated%20for%20each%20skill.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20code%20below%20only%20works%20for%203%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20MergePhone()%0A%20%20%20%20Dim%20rng%20As%20Range%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20Set%20rng%20%3D%20Range(%22C%3AC%22).Find(What%3A%3D%22*%22%2C%20SearchDirection%3A%3DxlPrevious)%0A%20%20%20%20Do%0A%20%20%20%20%20%20%20%20rng.Offset(1).EntireRow.Insert%0A%20%20%20%20%20%20%20%20rng.Offset(1%2C%20-2).Value%20%3D%20rng.Offset(0%2C%20-2).Value%0A%20%20%20%20%20%20%20%20rng.Offset(1%2C%20-1).Value%20%3D%20%22'%22%20%26amp%3B%20rng.Value%0A%20%20%20%20%20%20%20%20Set%20rng%20%3D%20Range(%22C%3AC%22).Find(What%3A%3D%22*%22%2C%20After%3A%3Drng%2C%20SearchDirection%3A%3DxlPrevious)%0A%20%20%20%20%20%20%20%20If%20rng.Row%20%3D%201%20Then%20Exit%20Do%0A%20%20%20%20Loop%0A%20%20%20%20Range(%22C%3AC%22).ClearContents%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2728542%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2728728%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20merge%206%20columns%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2728728%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1148076%22%20target%3D%22_blank%22%3E%40Deerg65%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20new%20code.%20It%20is%20slow%2C%20because%20it%20has%20to%20do%20a%20lot%20of%20checking.%3C%2FP%3E%0A%3CP%3EThe%20code%20can%20easily%20be%20modified%20if%20you%20need%20to%20merge%20more%20than%206%20columns.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20MergeColumns()%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Dim%20c%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20m%20%3D%20Cells(Rows.Count%2C%201).End(xlUp).Row%0A%20%20%20%20For%20r%20%3D%20m%20To%202%20Step%20-1%0A%20%20%20%20%20%20%20%20For%20c%20%3D%207%20To%203%20Step%20-1%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20Cells(r%2C%20c).Value%20%26lt%3B%26gt%3B%20%22%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Cells(r%20%2B%201%2C%201).Resize(1%2C%207).Insert%20Shift%3A%3DxlShiftDown%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Cells(r%20%2B%201%2C%201).Value%20%3D%20Cells(r%2C%201).Value%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Cells(r%20%2B%201%2C%202).Value%20%3D%20Cells(r%2C%20c).Value%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20Next%20c%0A%20%20%20%20%20%20%20%20If%20Cells(r%2C%202).Value%20%3D%20%22%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Cells(r%2C%201).Resize(1%2C%207).Delete%20Shift%3A%3DxlShiftUp%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20r%0A%20%20%20%20Range(%22C%3AG%22).ClearContents%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I'm normalising data to third normal form and I want to merge six columns of data into one (SkillsIDFK) column but the merged data has to be on the next line with the StaffIDFK repeated for each skill. 

The code below only works for 3 columns.

 

Sub MergePhone()
    Dim rng As Range
    Application.ScreenUpdating = False
    Set rng = Range("C:C").Find(What:="*", SearchDirection:=xlPrevious)
    Do
        rng.Offset(1).EntireRow.Insert
        rng.Offset(1, -2).Value = rng.Offset(0, -2).Value
        rng.Offset(1, -1).Value = "'" & rng.Value
        Set rng = Range("C:C").Find(What:="*", After:=rng, SearchDirection:=xlPrevious)
        If rng.Row = 1 Then Exit Do
    Loop
    Range("C:C").ClearContents
    Application.ScreenUpdating = True
End Sub

 

 

4 Replies
best response confirmed by Deerg65 (Occasional Contributor)
Solution

@Deerg65 

Here is new code. It is slow, because it has to do a lot of checking.

The code can easily be modified if you need to merge more than 6 columns.

Sub MergeColumns()
    Dim r As Long
    Dim m As Long
    Dim c As Long
    Application.ScreenUpdating = False
    m = Cells(Rows.Count, 1).End(xlUp).Row
    For r = m To 2 Step -1
        For c = 7 To 3 Step -1
            If Cells(r, c).Value <> "" Then
                Cells(r + 1, 1).Resize(1, 7).Insert Shift:=xlShiftDown
                Cells(r + 1, 1).Value = Cells(r, 1).Value
                Cells(r + 1, 2).Value = Cells(r, c).Value
            End If
        Next c
        If Cells(r, 2).Value = "" Then
            Cells(r, 1).Resize(1, 7).Delete Shift:=xlShiftUp
        End If
    Next r
    Range("C:G").ClearContents
    Application.ScreenUpdating = True
End Sub

@Deerg65 

 

You can do it by using Power Query unpivoting the other columns.

 

 

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {
      {"StaffID FK", Int64.Type}, 
      {"Col1", Int64.Type}, 
      {"Col2", Int64.Type}, 
      {"Col3", Int64.Type}, 
      {"Col4", Int64.Type}, 
      {"Col5", Int64.Type}, 
      {"Col6", Int64.Type}
    }
  ), 
  #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
    #"Changed Type", 
    {"StaffID FK"}, 
    "Attribute", 
    "Value"
  ), 
  #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns", {"Attribute"})
in
  #"Removed Columns"

 

 

Thanks
Thanks