将具有数据列的行转换为Excel中具有多行的列 [英] Convert row with columns of data into column with multiple rows in Excel

问题描述

我有数据行： -

TAG   SKU   SIZE   GRADE   LOCATION
A001  123    12      A       X1
A002  789    13      B       X3
A003  456    15      C       X5

我需要转换进入： -

I need to convert it into:-

A001   123  SIZE 12
A001   123  GRADE A
A001   123  LOCATION X1
A002   789  SIZE 13
A002   789  GRADE B
A002   789  LOCATION X3
A003   456  SIZE 15
A003   456  GRADE C
A003   456  LOCATION X5

我使用了以下（基于Ben McCormack在09年11月23日发布的建议），但不会产生上述结果： -

I used the below (based on Ben McCormack's suggestion posted on Nov 23 '09) but it doesn't produce the above result :-

Sub NormalizeSheet()
Dim wsOriginal As Worksheet
Dim wsNormalized As Worksheet
Dim strKey As String
Dim clnHeader As Collection
Dim lngColumnCounter As Long
Dim lngRowCounterOriginal As Long
Dim lngRowCounterNormalized As Long
Dim rngCurrent As Range
Dim varColumn As Variant

Set wsOriginal = ThisWorkbook.Worksheets("Original")     'This is the name of your original worksheet'
Set wsNormalized = ThisWorkbook.Worksheets("Normalized") 'This is the name of the new worksheet'
Set clnHeader = New Collection

wsNormalized.Cells.ClearContents        'This deletes the contents of the destination worksheet'

lngColumnCounter = 2
lngRowCounterOriginal = 1
Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)

' We'll loop through just the headers to get a collection of header names'
Do Until IsEmpty(rngCurrent.Value)
    clnHeader.Add rngCurrent.Value, CStr(lngColumnCounter)
    lngColumnCounter = lngColumnCounter + 1
    Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)
Loop

'Here we'll reset our Row Counter and loop through the entire data set'
lngRowCounterOriginal = 2
lngRowCounterNormalized = 1
lngColumnCounter = 1

Do While Not IsEmpty(wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter))

    Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)
    strKey = rngCurrent.Value ' Get the key value from the current cell'
    lngColumnCounter = 2

    'This next loop parses the denormalized values for each row'
    Do While Not IsEmpty(wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter))
        Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)

        'We're going to check to see if the current value'
        'is equal to NULL. If it is, we won't add it to'
        'the Normalized Table.'
        If rngCurrent.Value = "NULL" Then
            'Skip it'
        Else
            'Add this item to the normalized sheet'
            wsNormalized.Range("A" & lngRowCounterNormalized).Value = strKey
            wsNormalized.Range("B" & lngRowCounterNormalized).Value = clnHeader(CStr(lngColumnCounter))
            wsNormalized.Range("C" & lngRowCounterNormalized).Value = rngCurrent.Value
            lngRowCounterNormalized = lngRowCounterNormalized + 1
        End If

        lngColumnCounter = lngColumnCounter + 1
    Loop
    lngRowCounterOriginal = lngRowCounterOriginal + 1
    lngColumnCounter = 1    'We reset the column counter here because we're on a new row'
Loop



End Sub

推荐答案

这是一个从工作的方法直接到工作表。如果数据集太大且可用内存对于使用阵列而言太小，这可能是必需的。它可能会很慢。

Here's an approach going from worksheet to worksheet directly. This might be necessary if the dataset is too big and available memory too small for using arrays. It's likely to be slow.

它使用与reOrgV1相同的调用参数，几乎相同的逻辑。

It uses the same call parameters as reOrgV1, and pretty much the same logic.

它被更新为添加DEFECTS到属性。输入如下：

It's updated to add "DEFECTS" to the properies. The input looks like:

TAG     SKU   SIZE GRADE LOCATION DEFECTS
A001    123    12   A       X1      3
A002    789    13   B       X3      5
A003    456    15   C       X5      7

这是代码。 p>

Here's the code.

Public Sub reOrgV2(inSource As Range, inTarget As Range)
'' This version works directly on the worksheet
'' and transfers the result directly to the target
'' given as the top-left cell of the result.

'' **** Changed to add "Defects"
    Dim resNames()
    Dim propNum As Integer
    Dim srcRows As Integer
    Dim resRows As Integer
    Dim i As Integer
    Dim j As Integer
    Dim g As Integer

    '' Shape the result
    resNames = Array("Size", "Grade", "Location", "Defects")
    propNum = 1 + UBound(resNames)

    '' Row counts
    srcRows = inSource.Rows.Count
    resRows = srcRows * propNum

    '' re-org and transfer source to result range
    inTarget = inTarget.Resize(resRows, 4)
    g = 1
    For i = 1 To srcRows
        For j = 0 To 3
            inTarget.Item(g + j, 1) = inSource.Item(i, 1)      '' Tag
            inTarget.Item(g + j, 2) = inSource.Item(i, 2)      '' SKU
            inTarget.Item(g + j, 3) = resNames(j)              '' Property
            inTarget.Item(g + j, 4) = inSource.Item(i, j + 3)  '' Value
        Next j
        g = g + propNum
    Next i
End Sub

这是修改后的电话采购范围更广。

This is the revised call sourcing the wider range.

'' Call ReOrgV2 with input and output ranges
Public Sub test4()
    Dim i As Integer
    i = Range("InData!A:A").Find("").Row - 2
    reOrgV2 Range("InData!A2").Resize(i, 6), [OutData!A1]
End Sub

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