First, this may not be the way to go.
But if your data were laid out nicer--no gaps in column A--like:
User Number 12500 12500 12500 13100 13100 13100
Month Name JAN FEB MAR JAN FEB MAR
Beans 5 8 1 6 3 2
Bread 11 7 2 4 2 0
Jam 7 3 0 4 1 0
Total 22 18 3 14 6 2
Then I think you'd be better off changing your layout to this:
Product ID Month Qty
Beans 12500 JAN 5
Beans 12500 FEB 8
Beans 12500 MAR 1
Beans 13100 JAN 6
Beans 13100 FEB 3
Beans 13100 MAR 2
Bread 12500 JAN 11
Bread 12500 FEB 7
Bread 12500 MAR 2
Bread 13100 JAN 4
Bread 13100 FEB 2
Bread 13100 MAR 0
Jam 12500 JAN 7
Jam 12500 FEB 3
Jam 12500 MAR 0
Jam 13100 JAN 4
Jam 13100 FEB 1
Jam 13100 MAR 0
After the data is laid out like this, you can use data|pivottable to get very
nice (and quick) summaries of what you want.
But the problem is the format of the data coming to you. (Or is that just a
problem in your post to the newsgroup????)
In any case, if you can clean up your data first, you could use a routine like
this to change your data:
Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iCol As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim oRow As Long
Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add
NewWks.Range("a1").Resize(1, 4).Value _
= Array("Product", "ID", "Month", "Qty")
oRow = 2
With CurWks
FirstRow = 3 'headers in rows 1 and 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 2
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For iRow = FirstRow To LastRow
For iCol = FirstCol To LastCol
If LCase(.Cells(iRow, "A").Value) = LCase("total") Then
'do nothing
Else
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "C").Value = .Cells(2, iCol).Value
NewWks.Cells(oRow, "D").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
End If
Next iCol
Next iRow
End With
NewWks.UsedRange.Columns.AutoFit
End Sub
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
And now after you have your data nicely "tabularized", you can use
data|pivottable to create those summaries.
If you've never use pivottables, here are a few links:
Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html
John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)
Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm
MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx