Combining Several large tables of data - Advice please 1

[Gavona]

Please could you advise me how to approach this task?

I have 3 identically structured tables of financial data - each relates to a different financial year and is around 50,000 rows by 13 columns.
One column is CostCentre, One is CostElement and a third is a concatenation of the two, FullCode. Fullcode uniguely identifies a record. Most Fullcodes are in all 3 tables but some will only exist in one of the tables. There is also a column I have added to each indicating the financial year. Only one financial year exists in each table.

I also have two more tables that contain futher information about CostCentres and about CostElements.

When working with smaller datasets I would create a single database within Excel. Add further columns populated with vlookups to the CostCentre and CostElement tables. Convert to values and then I and colleaques would analyse and report using advanced filters, autofilters, pivottables etc.

I have tried to use Query (with all the data tables in an excel file) without much success:
1. I could not define a join that would include every fullcode that exists in one or more table.
2.tendency to hang / freeze up/take hours.

Now, I have Access at home but not at work and nor do many colleagues who would need to access the data. I have had some basic Access training but have never really used it. Most colleagues are still developing their Excel skills, learning about pivot tables etc. and have no knowledge of Access. Certainly data extracts and reports would need to be in Excel format.

I would have no problem having each year's data in a different column in a single "flat file" database, rather than have it fully normalised.

ps I will not be working on this much during the (UK) day so apologies in advance if I am slow to respond to suggestions.


Gavin

 

[dallen43]

I had a similar problem. Each day a .csv file created with the day's sales. I created a template that allows the user to select a file and it brings in the data and refreshes the pivot table.

However, the users wanted to combine several days, such as Friday, Saturday and Sunday.

So I created a second macro that would allow them to select an additional file and the macro would check to see if the store/class is already in the table. If so, it adds the two days sales. If not, it appends the record to the end of the 1st table.

I could put all the records in a single table (as would I would like) since it would exceed the maximum number of rows.

If you don't get a better solution and would like it, I could post my macros.

Good luck,
Deb

 

[Gavona]

Thanks Debs - that certainly sounds as if it could fix the problem. I would certainly give it a go if you posted the code.
I reckon though that I should at least try a relational database approach. Maybe part of your routine would help me create a full list of key fields that I could then use within Query / Access to get around my inability to define the joins that I need.

Thanks,


Gavin

 

[KenWright]

Personally I would dump them all one under the other into a single Access table, adding a field for the year to distinguish each dataset, assuming you don't already have a date field, and then just use a Pivot table to interrogate the Access mdb file. Don't think they need Access installed just to interrogate the file that way, though I have Access installed on all my machines, so can't say for sure. Pretty sure that the folks at work also use that method via ODBC and don't have Access installed though.

Have them spend a few minutes getting used to a Pivot table and benefit from the power it gives them rather than implement workarounds that may be inefficient and cause you more work. I've yet to find someone who doesn't love them after a very brief introduction.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

other option is that once you have set up your data in Access into your single dataset as Ken suggests, you can export the table as a text file and use Excel to query against that into a pivot table etc.

This would be an option if you find that users do need Access installed to be able to interrogate it.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gavona]

Thanks for the ideas. I am struggling with getting the three main tables into a single Access table. Importing the second table I get "An error occurred trying to import file 'filename.xls'. The file was not imported."

I have copied the title row and the first row of data so these are identical in each table.
I have let Access decide the format of each column when creating the original table.
I deleted the primary access key as this was effectively adding a field to the first table that did not exist in the data I was importing.
I have even tried importing the same data that I created my first table with but get the same error.
There are no leading or trailing spaces in my titles. (There are spaces in some of them so I will try eliminating them next but it feels like a long shot.)

Any ideas please?


Gavin

 

[Gavona]

It did not work. I can't believe it should be this difficult to create one dataset out of the three identical ones.

Deb, if you are there then your solution sounds more promissing than ever. Please do post your code.

Thanks all for your advice so far.


Gavin

 

[fneily]

If your datasets have the same structure, then, if you want, IMPORT the first, then don't import but just copy and paste the other two on the bottom of the access table. I know this works. You can just create a table with the needed fields and copy and paste all three. Quite easy.

 

[dallen43]

Before getting to the macros, I need to explain the structure of the spreadsheet.

The template has two worksheets: SalesComparison and Raw.

SalesComparison contains a Pivot Table (PivotTable1) that uses the named range "RawData". In cells A1, A2 and A3 there is the title of the sheet, the Sales dates and Comp dates respectively.

Raw contains a days sales figures from the .csv file starting in cell B1. Column A is computed by the macros as a key field. It combines the store number and class, which will uniquely describes each record. In the macro you will see I've used a CONCATENATE command to create the data for this column.

Columns B through L are informational, such as division, department etc. Columns M through Z are amounts, such as Sales, Returns, Cost, etc for both current and comp.

When I add a second date, a temporary worksheet named Raw2 is created. It is in the same format at Raw, creates the same key information in column A.

I'm not sure how other post their code in a box, so I'll just indicate what is code.

I have two macros.

The first is Update_Sales_Comparison. It updates the worksheet Raw with the 1st day's sales. It adjustes the named range "RawData", puts the dates in appropriate cells and refreshes the Pivot Table.

=========================================================
Sub Update_Sales_Comparison()
'
' This routine replaces the sales with the data from the selected file.
'
Application.ScreenUpdating = False
'
' Select the sales file
fileToOpen = Application _
.GetOpenFilename("Sales .csv Files (*.csv), *.csv")
If fileToOpen = False Then
End
End If

' Delete the current Worksheet Raw and add a blank one.
XLSFileOpened = ActiveWorkbook.Name
Sheets("Raw").Select
ActiveSheet.Unprotect
Application.DisplayAlerts = False
Worksheets("Raw").Delete
Application.DisplayAlerts = True
Set NewSheet = Worksheets.Add
NewSheet.Name = "Raw"

' Open the .csv file and copy the data from it.
' Then paste the data to the new Raw worksheet.
Range("B1").Select
Workbooks.Open fileToOpen
CsvFileOpened = ActiveWorkbook.Name
Range("A1:Y1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows(XLSFileOpened).Activate
Sheets("Raw").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
ActiveWorkbook.Names.Add Name:="RawData", RefersTo:= _
Selection
'
' Add a key of the store/class to column A.
lrow1 = Worksheets("Raw").Range("B" & Cells.Rows.Count).End(xlUp).Row
Range("A1").Select
ActiveCell.Value = "Key"
Range("A2").Select
ActiveCell.Formula = "=CONCATENATE(MID(H2,1,2),MID(L2,1,4))"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A" & lrow1)
'
' Change the formulas in column A to values
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' Sort by column A
Range("A2:Z2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'
' Put the header information into SalesComparison worksheet.
' This includes the dates.
Range("A1").Select
Sheets("SalesComparison").Select
Range("A2").Value = "Range: " & Range("Raw!B2").Text & " to " & Range("Raw!C2").Text
Range("A3").Value = "Comp: " & Range("Raw!D2").Text & " to " & Range("Raw!E2").Text
'
' Update the Pivot Table.
Range("D12").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
'
' Protect the Raw data
Sheets("Raw").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
'
' Close the .csv file
Sheets("SalesComparison").Select
Range("A1").Select
Windows(CsvFileOpened).Activate
ActiveWindow.Close
Windows(XLSFileOpened).Activate
'
' Move the Raw worksheet to be after the SalesComparison worksheet.
Sheets("Raw").Select
Sheets("Raw").Move After:=Sheets("SalesComparison")
'
' Make the PivotTable toolbar visible.
Sheets("SalesComparison").Select
Range("A1").Select
Application.CommandBars("PivotTable").Visible = True
End Sub

===========================================================

Add_File_To_Sales() is the second macro. It will add the information from another day to the 1st day. If the store/class already exists, it will add columns M through Z. If not it appends the record to the end of the table.

===========================================================

Sub Add_File_To_Sales()
'
' This routine adds the data from a sales file to the existing sales.
'
' Get name of file to add
fileToOpen = Application _
.GetOpenFilename("Sales .csv Files (*.csv), *.csv")
If fileToOpen = False Then
End
End If

Application.ScreenUpdating = False
XLSFileOpened = ActiveWorkbook.Name


' Unprotect Raw, we will be updating it
XLSFileOpened = ActiveWorkbook.Name
Sheets("Raw").Select
ActiveSheet.Unprotect

' See if Raw2 exists, if so delete it.
' Set NewSheet = Sheets.Add(Type:=xlWorksheet)
For I = 1 To Sheets.Count
If Sheets(I).Name = "Raw2" Then
Application.DisplayAlerts = False
Sheets("Raw2").Select
ActiveSheet.Unprotect
Worksheets("Raw2").Delete
Application.DisplayAlerts = True
End If
Exit For
Next I

' Add worksheet Raw2
Set NewSheet = Worksheets.Add
NewSheet.Name = "Raw2"

' Get information from selected file and put it in Raw2
Range("B1").Select
Workbooks.Open fileToOpen
CsvFileOpened = ActiveWorkbook.Name
Range("A1:Y1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows(XLSFileOpened).Activate
Sheets("Raw2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False

' Close the csv file
Windows(CsvFileOpened).Activate
ActiveWindow.Close
Windows(XLSFileOpened).Activate


' Combine Store and Class to form a Key in column A
lrow1 = Worksheets("Raw2").Range("B" & Cells.Rows.Count).End(xlUp).Row
Range("A1").Select
ActiveCell.Value = "Key"
Range("A2").Select
ActiveCell.Formula = "=CONCATENATE(MID(H2,1,2),MID(L2,1,4))"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A" & lrow1)
'
'Change the formulas in column A to values.
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' Sort by column A
Range("A2:Z2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

' Add the date ranges to the header of the Sales Comparison
Range("A1").Select
Sheets("SalesComparison").Select
Range("A2").Value = Range("A2").Value & " and " & Range("Raw2!B2").Text & " to " & Range("Raw2!C2").Text
Range("A3").Value = Range("A3").Value & " and " & Range("Raw2!D2").Text & " to " & Range("Raw2!E2").Text

' Combine the data from Raw2 to Raw.
' If store/class doesn't exist, add it to the end.

Sheets("Raw2").Select

' These are the columns that need to be combined if store/class is found.
Dim TestArray(14) As String * 1
TestArray(1) = "M"
TestArray(2) = "N"
TestArray(3) = "O"
TestArray(4) = "P"
TestArray(5) = "Q"
TestArray(6) = "R"
TestArray(7) = "S"
TestArray(8) = "T"
TestArray(9) = "U"
TestArray(10) = "V"
TestArray(11) = "W"
TestArray(12) = "X"
TestArray(13) = "Y"
TestArray(14) = "Z"

lrow2 = Worksheets("Raw2").Range("A" & Cells.Rows.Count).End(xlUp).Row
lrow1 = Worksheets("Raw").Range("A" & Cells.Rows.Count).End(xlUp).Row
lend1 = Worksheets("Raw").Range("A" & Cells.Rows.Count).End(xlUp).Row
ltop1 = 2
Set myRange2 = Worksheets("Raw2").Range("A2:A" & lrow2)

For Each myObject In myRange2
If myObject.Value < 1 Then
Exit For
End If

' The following is a double loop.
'
' The 1st loop checks through each store/class key from Raw2.
'
' The 2nd loop sees if that store/class is already on the Raw worksheet.
' If so, it accumulates the amounts from Columns M thru Z.
' If not, it adds the record (Columns A thru Z) to the end of Raw.
'
lcKey = myObject.Value
Set myRange1 = Worksheets("Raw").Range("A" & ltop1 & ":A" & lend1)

With myRange1
Set c = .Find(lcKey, LookIn:=xlValues)
If Not c Is Nothing Then
' found this store/class, so add the amounts to current record.
c.Font.Bold = True
fromRow = myObject.Row
toRow = c.Row
ltop1 = toRow + 1

For I = 1 To 14 Step 1
lcCol = TestArray(I)
fromAmt = Worksheets("Raw2").Range(lcCol & fromRow).Value
toAmt = Worksheets("Raw").Range(lcCol & toRow).Value
NewAmt = fromAmt + toAmt
Worksheets("Raw").Range(lcCol & toRow).Value = NewAmt
Next I

Else
' can't find this store/class, so add entire record.
lrow1 = lrow1 + 1
Set newCell1 = Worksheets("Raw").Range("A" & lrow1 & ":Z" & lrow1)

newRow = myObject.Row
Set fromRange = Worksheets("Raw2").Range("A" & newRow & ":Z" & newRow)
fromRange.Copy

Worksheets("Raw").Paste Destination:=newCell1

End If
End With
Next myObject

' Finished adding the values, now change the name range for pivot table
' (because we have added new rows)
Sheets("Raw").Select
Range("B1:Z1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="RawData", RefersTo:= _
Selection

' Sort by column A
Range("A2:Z2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

' Protect the raw sheet
Sheets("Raw").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

' Delete Raw2
Application.DisplayAlerts = False
Sheets("Raw2").Select
Worksheets("Raw2").Delete
Application.DisplayAlerts = True

' Refresh the pivot table
Sheets("SalesComparison").Select
Range("D12").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Sheets("SalesComparison").Select
Range("A1").Select
Application.CommandBars("PivotTable").Visible = True

' Debug - Message at end
lnAnswer = MsgBox("Sales has been added!", vbOKOnly)

End Sub
===========================================================

Note: Be careful of the wrapping. Extra CRLF will cause problems in the macro.

I'm not an expert with macros and am sure there are others that would have written them with more elegance.

Hope this helps.
Deb

 

[Gavona]

fneily, that sounds simple. I got it appearing to work but need to check out why my control totals don't match the original data.

Deb: Thanks for that. I'll need to study it of course - will post back. Going to give fneily's approach a try first.
To put code in a box you need [/code] at the end and

 at the start.


Gavin

 

[MSIsam]

A few months ago, I downloaded the MS Office 2007 beta from microsoft. If you are on XP, it might solve your problem since the new version of Excel can handle a million rows per tab. It was only $1.50 so you may want to give it a shot.

http://www.microsoft.com/office/preview/beta/getthebeta.mspx?showIntro=n