Arranging cells to create a database
I'll have to get around it without Access, but let me thank you again.
I gained a tremendous amount of speed compared to the look-up
Dave Peterson wrote in message ...
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
|