Arranging cells to create a database

  • Thread starter Thread starter Frank Richmond
  • Start date Start date
F

Frank Richmond

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
 
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.)
 
No bad! Not bad at all.

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

Thanks
 
Well that's a factor of your original data. Of course it can be worked around, by checking to see at what cell you are currently at, and then creating a new sheet for continuation, or by moving over to the next available column(s) and starting the data again at the top. Another is to split your data as it is being processed into some functional category, like by Calendar year, or Fiscal year, whichever/whatever you may desire. Creating a worksheet for each of those functional categories. This would be a more user functional database, so that the user could go to a particular year and see what was done, and your "searches" through your database would be faster, as you would realize that your O would be n, to find the last purchased item, where it would be 1 + m for a search given a particular date.
 
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.
 
Back
Top