如何将多列转换为单列？

I have a table that looks like this:

我有一个看起来像这样的表:

Col1        Col2        Col3        Col4
a       b       c       d
e       f       g       h

I need to convert it to this:

我需要将其转换为:

Col1        New
a       b
a       c
a       d
e       f
e       g
e       h

I have a ton of data and doing this by hand is out of the question. Does anyone know of a fast way to do this? I'm hoping that there is a built in way (such as paste->transpose or a function?). If not could someone point me at a relevant VBA example since I am little rusty at VBA.

我有大量数据,手工完成此操作是不可能的。有谁知道快速的方法吗?我希望有一种内置方式(例如paste-> transpose或函数?)。如果没有,有人会指出我在相关的VBA示例,因为我在VBA上生锈了。

1 个解决方案

#1

Select the data you want to transform (not including column headings) and run the following macro.

选择要转换的数据(不包括列标题)并运行以下宏。

Sub Transform()

    Dim targetRowNumber As Long
    targetRowNumber = Selection.Rows(Selection.Rows.Count).Row + 2

    Dim col1 As Variant
    Dim cell As Range

    Dim sourceRow As Range: For Each sourceRow In Selection.Rows

        col1 = sourceRow.Cells(1).Value
        For Each cell In sourceRow.Cells

            If Not cell.Column = Selection.Column Then
                Selection.Worksheet.Cells(targetRowNumber, 1) = col1
                Selection.Worksheet.Cells(targetRowNumber, 2) = cell.Value
                targetRowNumber = targetRowNumber + 1
            End If

        Next cell

    Next sourceRow

End Sub