Another way around that would be to put the data into Access and use excel to do
the pivottable against the Access file.

And if it exceeds 64k rows, you may want to start there.

Frank Richmond wrote:

No bad! Not bad at all.

One quirk might be the number of cells exceeds 65536. but it's a start.

Thanks

Dave Peterson wrote in message ...
I think I'd change the layout of the original data to have 3 columns:

Date, Qty, (or whatever that last number is) and Product type.

Then use Data|Pivottable to create the summary report.

This is one way to rearrange your data. It copies the values to another
worksheet.

Option Explicit
Sub rearrangeMyData()

Dim FirstRow As Long
Dim LastRow As Long
Dim NumberOfRows As Long

Dim iCol As Long
Dim LastCol As Long
Dim FirstCol As Long

Dim oRow As Long

Dim newWks As Worksheet
Dim curWks As Worksheet

Set curWks = ActiveSheet
Set newWks = Worksheets.Add
newWks.Range("a1").Resize(1, 3).Value _
= Array("Date", "Qty", "Type")

oRow = 2
With curWks
FirstCol = 1
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

For iCol = FirstCol To LastCol Step 2
FirstRow = 2
LastRow = .Cells(.Rows.Count, iCol).End(xlUp).Row

If LastRow = FirstRow Then
NumberOfRows = LastRow - FirstRow + 1

newWks.Cells(oRow, 1).Resize(NumberOfRows, 2).Value _
= .Cells(FirstRow, iCol).Resize(NumberOfRows, 2).Value

newWks.Cells(oRow, 3).Resize(NumberOfRows, 1).Value _
= .Cells(1, iCol + 1).Value

oRow = oRow + NumberOfRows
End If
Next iCol
End With

End Sub

Then you can use Data|Pivottable to do the summary.

(if you need a macro for that portion, record it when you do it once. About the
only thing you'll have to change is the input range for the pivottable.)



Frank Richmond wrote:

Here is my dilemna. I'm currently using a complex R1C1 vlookup
function to satisfy my database need but was hoping to a better
solution.
My spreadsheet looks like this

datea producta dateb productb datec productc
12/31/02 32.5 12/29/02 65.5 01/02/03 12.4
01/02/03 31.6 12/31/02 65.5 01/04/03 10.5
01/04/03 35.5 01/02/03 66.5
01/03/03 66.4
01/04/04 66.2

It needs to be consolidated like this

date producta productb productc
12/29/02 65.5
12/31/02 32.5 65.5
01/02/03 31.6 66.5 12.4
01/03/03 66.4
01/04/03 35.5 66.2 10.5

Any thoughts without some complex R1C1 look-up formula?

Thanks

--

Dave Peterson