将数据从多列移动到单行 [英] Move data from multiple columns into single row

问题描述

此格式问题使我感到困惑.我不是Excel的王者，非常感谢您解决我的问题的方法.

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.:

我尝试搜索有关移调的解决方案，但这似乎涉及更多.每个日期我都有4倍的数据结果(如前一栏所示).

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).

推荐答案

这是一个简单的循环，它在工作表的底部向上进行，将最后一行移至4列，向下复制行，然后删除上方的行.

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

之前:

之后:

这篇关于将数据从多列移动到单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！