Excel transpose row to column and preserves columns and replicate them in row

Question

Hello,

Next, I have a CSV as follows:

A;B;C;D;1;2;3;4;5

E;F;G;H;6;7;8;9;10

The columns of letters have a regular structure, whereas the columns of numbers do not but you could blow them up and put them in columns but you won’t be able to create the desired metric

But when I put it into my imagination I’d be like this:

A;B;C;D;2

A;B;C;D;3

And so on and so forth!

Someone knows how to do it?

Answer 1

I created the code below by the example you gave.

Change the Worksheets(1) and Worksheets(2) according to the worksheet with the origin and the worksheet that will serve as the destination.

Public Sub Replica()
Dim linha As Range
Dim Celula As Range
Dim Atributos As String
Dim Metricas As String
NumeroLinha = 1
For Each linha In Worksheets(1).UsedRange.Rows
    Atributos = ""
    Metricas = ""
    For Each Celula In linha.Cells
        If TypeName(Celula.Value) = "String" And Celula.Value <> "" Then
        Atributos = IIf(Atributos = "", Celula.Value, Atributos & ";" & Celula.Value)
    Else
        Metricas = IIf(Metricas = "", Celula.Value, Metricas & ";" & Celula.Value)
    End If
Next Celula
AtributosArray = Split(Atributos, ";")
MetricasArray = Split(Metricas, ";")
For i = 0 To UBound(MetricasArray)
    Worksheets(2).Rows(NumeroLinha).Resize(, UBound(AtributosArray) + 1).FormulaArray = AtributosArray
    Worksheets(2).Cells(NumeroLinha, UBound(AtributosArray) + 2) = MetricasArray(i)
    NumeroLinha = NumeroLinha + 1
Next i
Next linha
End Sub

Answer 2

Try using the special paste. Copy the cells you want and press Ctrl+Alt+V if you want to transpose.

Answer 3

I advise to use this macro which I did, everything will be done automatically, just create the macro and run. If the line is not on A1, just change the location of the line inside the macro.

Sub Transpor()

    'Transpor Linha
    contalinha = Cells(Rows.Count, 1).End(xlUp).Row
    Set BigArray = Cells(1, 1).Resize(contalinha, 1)
    Cells(1, 2).Resize(1, contalinha).Value = WorksheetFunction.Transpose(BigArray)
    BigArray.Clear

End Sub

Answer 4

Perfect solution

Sub CONVERTROWSTOCOL_Oeldere_revisted_new()

    Dim rsht1 As Long, rsht2 As Long, i As Long, col As Long, wsTest As Worksheet, mr As Worksheet, ms As Worksheet

    'check if sheet "ouput" already exist

    Const strSheetName As String = "Output"

    Set wsTest = Nothing
    On Error Resume Next
    Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
    On Error GoTo 0

    If wsTest Is Nothing Then
        Worksheets.Add.Name = strSheetName
    End If

    'set the data


    Set mr = Sheets("sheet1")                    'this is the name of the source sheet

    Set ms = Sheets("Output")                    'this is the name of the destiny sheet

    col = 2
    'End set the data

    With ms
        .UsedRange.ClearContents
        .Range("A1:B1").Value = Array("Mat", "value")
    End With

    rsht2 = ms.Range("A" & Rows.Count).End(xlUp).Row


    With mr
        rsht1 = .Range("A" & .Rows.Count).End(xlUp).Row
        For i = 2 To rsht1
            Do While .Cells(1, col).Value <> ""  'And .Cells(I, col).Value <> ""
                rsht2 = rsht2 + 1

                ms.Range("A" & rsht2).Value = .Range("A" & i).Value


                ms.Range("B" & rsht2).Value = .Cells(i, col).Value

                col = col + 1
            Loop
            col = 2
        Next
    End With

    With ms
        .Columns("A:Z").EntireColumn.AutoFit
    End With

End Sub