can't use Pivot to format because data is non-numeric

**kkehne** · 03-31-2010, 04:33 PM

My source data and the format I want to maintain it in is:
1H09 Grp1 item1
1H09 Grp1 item2
1H09 Grp1 item3
1H09 Grp2 item4
1H09 Grp2 item5
2H09 Grp1 item6
2H09 Grp1 item7
2H09 Grp2 item8
2H09 Grp2 item9
2H09 Grp2 item10

The number of Groups and the number of Items is
variable for a given timeframe.

Want to generate a table/report with:
Two column headings of (timeframe) 1H09 and 2H09
Two Row Headings of Grp1 and Grp2
and the list of items in the intersection of the
grid of the headings.

-------------------------1H09-------------------2H09
--------------------------------------------------------------
-------------------------Item1------------------Item6
Grp1------------------Item2------------------Item 7
-------------------------Item3
-------------------------------------------------------------
-------------------------Item4------------------Item8
Grp2------------------Item5------------------Item9
---------------------------------------------------Item10

I thought pivot table would be the solution but Item is a non-numeric
so doesn't work. How can I automate the generation of the
report but maintain it in the source format?

**mrice** · 04-02-2010, 11:03 AM

This macro might do it.

Sub Test()
Cells(1, 5) = "X"
For N = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If Application.CountIf(Range(Cells(1, 6), Cells(1, Columns.Count)), Cells(N, 1)) = 0 Then
        Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1) = Cells(N, 1)
    End If
    TargetColumn = Range(Cells(1, 6), Cells(1, Columns.Count)).Find(Cells(N, 1), , xlValues, xlWhole).Column
    If Application.CountIf(Range(Cells(2, 5), Cells(Rows.Count, 5)), Cells(N, 2)) = 0 Then
        NewGroupRow = Cells(Rows.Count, 5).End(xlUp).CurrentRegion.Row + Cells(Rows.Count, 5).End(xlUp).CurrentRegion.Rows.Count + 1
    
    
        Cells(NewGroupRow, 5) = Cells(N, 2)
    End If
    TargetRow = Range(Cells(2, 5), Cells(Rows.Count, 5)).Find(Cells(N, 2), , xlValues, xlWhole).Row
    Do While Cells(TargetRow, TargetColumn) <> ""
        TargetRow = TargetRow + 1
    Loop
    Cells(TargetRow, TargetColumn) = Cells(N, 3)
    

Next N
End Sub

I've assumed that your data starts in Cell A1 and it is sorted by Group number.