Extract categories from columns into duplicated rows with new category

Question

I have a table that looks like so:

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

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

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.

I hope that was clear enough.

An idea that doesn't involve using VBA - can you just copy the entire Group, Name and Comment columns, then paste those three between tag 1 and tag 2, and also paste them between tag 2 and tag 3? So it goes group|name|comment|tag1|group|name|comment|tag2|group|name|comment|tag3? Then you can just move the relevant tag2 block down below all the tag1 data, and so on. You can record as a macro if you need to re-use. (like this: http://screencast.com/t/1JnKrorqq) — mr.Reband, May 23 '13 at 19:25
Well I have 890 rows to do this one and I would to many empty slots since it goes it to "Tag 10" and most only use 2-3 tags. So this would be probably be inefficient. I also need something I can re-use later on without tweaking the macro too much. — JeroenEijkhof, May 23 '13 at 19:58

score 2 · Answer 1 · answered May 23 '13 at 19:58

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

Extract categories from columns into duplicated rows with new category

1 Answers1

Linked