+ Reply to Thread
Results 1 to 3 of 3

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

Hybrid View

  1. #1
    Registered User
    Join Date
    03-31-2010
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2003
    Posts
    2

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

    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?


  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

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

    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.
    Martin

  3. #3
    Registered User
    Join Date
    03-31-2010
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2003
    Posts
    2

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

    yes, this is very helpful. thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1