Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arranging cells to create a database
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arranging cells to create a database
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arranging cells to create a database
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arranging cells to create a database
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.
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arranging cells to create a database
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Excel Database | Excel Discussion (Misc queries) | |||
Create address database | Excel Discussion (Misc queries) | |||
How do I create a template that add the fields to a database? | Excel Discussion (Misc queries) | |||
Create database in excel? | New Users to Excel | |||
How do I create an Excel database? | New Users to Excel |