Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

excel - Move data from multiple columns into single row

This formatting issue is confusing me. I'm not an Excel king and would greatly appreciate the solution to my problem.

I'm trying to format data from multiple columns into a single row by date. i.e.:

enter image description here

I've tried to search for solutions regarding transpose, but this seems a bit more involved. I have 4x data results for each date (as seen in the before column).

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Here is a simple loop that works bottom up in the sheet, shifts last line over 4 columns, copies line above down and then deletes the line above.

Sub TransposeData()
    Dim WS As Worksheet
    Dim LastCell As Range
    Dim LastCellRowNumber As Long

    Set WS = Worksheets("Sheet1")
    With WS
        Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
        LastCellRowNumber = LastCell.Row
    End With

    'Loop through column A bottom up
    For i = LastCellRowNumber To 2 Step -1
        'Shift current values over
        Range("A" & i & ":D" & i).Insert Shift:=xlToRight

        'Copy new values down
        Range("A" & i & ":D" & i).Value = Range("A" & i - 1 & ":D" & i - 1).Value

        'Delete row
        Rows(i - 1).Delete Shift:=xlUp
    Next i
End Sub

Before:

enter image description here

After:

enter image description here


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...