从列中将类别提取为具有新类别的重复行 [英] Extract categories from columns into duplicated rows with new category

问题描述

我有一张表如下所示：

Group   | Name     | Comment   | Tag 1       | Tag 2       | Tag 3
-------------------------------------------------------------------
gr1       Joe        We are...   SYSTEM        SUGGESTION    PAINPOINT
gr1       Joe        I want...   PROCESS       ATTITUDE

我需要运行一个基本上生成这个宏的宏（我正在使用Excel 2007） p>

And I need to run a macro that essentially generates this (I'm using Excel 2007)

Group   | Name     | Comment   | Tag 1       | Tag 2       | Tag 3
-------------------------------------------------------------------
gr1       Joe        We are...   SYSTEM
gr1       Joe        We are...   SUGGESTION
gr1       Joe        We are...   PAINPOINT
gr1       Joe        I want...   PROCESS
gr1       Joe        I want...   ATTITUDE

所以所有的标签都是重复的数据，而是自己的行。这样我就可以在一列中对信息进行排序和转发。我目前在VBA方面并不好，对这个特别的问题也有一些帮助。

So all tags get duplicated data but their own row. This allows me to now sort and pivot the information in one columns. I am currently not that good at VBA and would love some help with this particular question.

我希望足够清楚。

推荐答案

如果您真的需要将其作为vba代码，这里是可能的解决方案之一：
（子程序中的一些附加注释）
试用和测试

If you really need to have it as vba code here is one of possible solution: (some additional comments inside subroutine) Tried and tested

Sub Solution()

    'Select cell with 'Group' title
    'Result passed to 10th column to the right
    'Macro doesn't care of headers of result table

    Dim KOM As Range
    Dim partGNC As Variant
    Dim partTAG As Variant
    Dim resRow As Long
        resRow = ActiveCell.Row + 1
    For Each KOM In Range(ActiveCell.Offset(1, 0), ActiveCell.End(xlDown))

        partGNC = KOM.Resize(1, 3)
        partTAG = Range(KOM.Offset(0, 3), KOM.End(xlToRight))

        If KOM.Offset(0, 3).Address = KOM.End(xlToRight).Address Then

            Cells(resRow, KOM.Column + 10).Resize(1, 3) = partGNC
            Cells(resRow, KOM.Column + 13) = partTAG
            resRow = resRow + 1

        Else
            Cells(resRow, KOM.Column + 10).Resize(UBound(partTAG, 2), 3) = partGNC
            Cells(resRow, KOM.Column + 13).Resize(UBound(partTAG, 2), 1) = Application.Transpose(partTAG)
            resRow = resRow + UBound(partTAG, 2)
        End If


    Next

End Sub

这篇关于从列中将类别提取为具有新类别的重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！