Dealing with large spreadsheets

Special techniques are required to cope with large volumes of data and/or to have many Excel Reports based on one spreadsheet.

Introduction
What is a large spreadsheet?

Is it the sheer size, the number of used rows in a particular sheet or having lots of worksheets?

All and any of these make for large unwieldy spreadsheets prone to error and known to consume lots of a user’s time and effort.

This article discusses techniques to deal with each of these situations and the general problems that they are prone to.

Dealing with large Spreadsheets

Why do spreadsheets grow very large?
Often they are used as databases, and with Excel 2007 now being able to cater for a millions rows, they can hold quite a lot of data.

The typical scenario is to have a worksheet with the data and other worksheets which use pivot tables and lookup functions like vlookup to analyse and report on the data.

Another situation that is quite common is monthly reporting. You start off with reporting for a number of branches for one month and then you end up with reporting month after month for a year or two and you now have a very complex set of linked workbooks.
e.g. 10 branches by 24 months  240 linked workbooks plus the 24 data sheets….!

Ghost rows can also bloat a workbook. Especially if it came from a very early version of excel….There are techniques to eliminate these.

Vlookup versus Index/Match

Let’s examine this vlookup formula.
=VLOOKUP(E2,A:C,2,FALSE)
There are four parts:

1: Cell Value to use – on some versions of excel there were major issues with dashes in the value to lookup.

2: The range to look-in – Note: How we used the columns, rather than a selected range.
e.g. We could have used
=VLOOKUP(E2,A1:C6,2,FALSE) instead with the range A1 to C6 specified

3: The Column with the value. In this case the 2nd column of the range is specified

4: Exact or Approximate match…beware this can cause major issues!
Setting this to false makes it do an exact match.
* One trick to getting values is to do an approximate match on a list with ever increasing values and it stops when the value is larger than the one it is trying to find. This enables you to use vlookup to get a value with a specific range.

Often people will use the more complicated index/offset and match formulae to get the same or a more accurate result.

e.g. =INDEX(B:C,MATCH(E2,A:A,0),1)
or =OFFSET(B:C,MATCH(E2,A:A,0)-1,0,1,1)

Noting that the index/match combination is simpler and easier than the offset one.

The match part looks up the column with the value and returns the row number.
The index formula then uses the row number and simply retrieves the value from the column specified in the range selected.

Here’s an example doing it across sheets:
=VLOOKUP(A2,Sheet1!A:C,2,FALSE)
=INDEX(Sheet1!B:C,MATCH(A2,Sheet1!A:A,0),2)

Or
=VLOOKUP(A2,'Large Data Worksheet'!A:C,2,FALSE)
=INDEX('Large Data Worksheet'!B:C,MATCH(A2,'Large Data Worksheet'!A:A,0),1)

We often have had to replace every formula for a client changing the vlookup to an index / match for better accuracy.


Linked Workbooks

The story gets even better when you have a linked workbook!
What if the workbook gets moved? You get those nice messages says that it cannot update the links, when you open the workbook.

=VLOOKUP(A2,'[My Data.xlsx]Large Data Worksheet'!$A:$C,2,FALSE)
=INDEX('[My Data.xlsx]Large Data Worksheet'!$B:$C,MATCH(A2,'[My Data.xlsx]Large Data Worksheet'!$A:$A,0),1)

You can select “Updated” and “Edit Links” when it cannot find the source workbook. This then allows you to select the new workbook.
If you don’t select the workbook and just say Continue, then you see the last set of values that it remembered.

In most cases, it is the monthly reporting scenario were most of these large spreadsheet systems are first created and then the whole system becomes a very complex and prone to error. E.g. The example above of 10 branches by 24 months  240 linked workbooks plus the 24 data sheets….!

What should you do?

Use Index/Match rather than vlookup if looking up text values.

Copy and past values into your monthly reports, so you don’t have links everywhere.

Get a developer in to assist you to get the data directly out of a database using a little vba.

Pump the data back into a database for monthly reporting.
* Makes comparing months and years a lot easier.


Summary
Often spreadsheets start small and grow to become very large and hard to use. By using inbuilt lookup functions and knowing when to separate large lists, you can save a lot of time and improve the usability and accuracy of your data.

Being aware of issues with certain lookup functions such as vlookup and linked workbooks, is half the battle with gaining control again.

Know when to use a database for your data and not excel.

Questions?
If you have any questions about ways to deal with huge spreadsheets, let us know.

http://www.macroview.com.au

Error 2227 Issue with clipboard in Access 2007

Error 2227: The data on the Clipboard is damaged, so Microsoft Office Access can't paste it.

Had to catch the error and advise the user to close excel first!

This nightmare was caused by an patch to excel 2007.

So put this error message at the bottom of the procedure:
Noting it was in an access 2003 database but the error occurs when running access 2007:

If Err.Number = 2227 And Application.Version = "12.0" Then
MsgBox "There is an issue with an update of excel 2007 and you need to close excel before pasting", vbCritical
Else
MsgBox "Error pasting and updating data" & vbNewLine & Err.Description & vbNewLine & "Error No:" & Err.Number, vbInformation
End If

Re: the Dot Net User group on Wednesday night 18th March 2009

Learnt a few things but went hungry…

What’s the deal that we missed pizza etc
Poor show, sitting there hungry while trying to digest c# stuff…..

News
– There was something about Microsoft Slashing Pricing by 15 to 25% for enterprise server software?
- .Net Code Camp in first weekend in April…

Slide Rocket is cool to do presentations…Can then output to PowerPoint.
Does nice spinning like a cube and allows you to easily select pictures from flicker etc

iPhone
They showed some of the new apple features coming up for the iPod….Maybe that’s why we got no pizza!!!!
Interesting how many apps and developers are into this now, all with apple taking 30% cut of everything!
Wait till the european commission tells apple to cut the monopoly!
There was even a dmf like app for SharePoint that runs on the iPhone!!!!

Saas stuff was though provoking.
There are quite a few things in the cloud and they are getting very compelling as an alternative.
Things like using external providers for bulk emailing, accounting, CRM, Support….
e.g.
Saasu for accounting rather than myob……cheaper and better and web based!
Constant Contact or Campaign Monitor for bulk emailing / newsletters as they have anti-spam agreements you can safely bulk email etc…
Noting the Campaign Monitor is sydney based…..
Zendesk and LivePerson for support

Cross site scripting – making your site more hackerproof
Then a quick one on cross site scripting and how an enterprising guy wrote some javascript he put into his facebook page that wrote a comment in anyone’s page who visited his.
Just a case of adding javascript to do something in a comments area on a web page…think blogs as well etc…
That hack got a good comment about him on thousands of pages very rapidly…..
In .net you can ensure comments cannot have javascript inserted by using html encoding….or antixss.htmlencode …..
Or write a function and add it to your tagmapping in the web.config for all lables and literals…….

6 Design Patterns…
Heavy stuff and kind of do some of them anyway depending on the situation.
For more information suggested first going to Wikipedia…also good for lots of other coding situations and information.
Interestingly the suggest was that there is a book “Head First Design Patterns” code patterns with a blonde with pony tails on the front – obviosuly marketing ot the geek factor…
Even though it is in java, to translate to c# is as simple as 1 line…..
The other book was c# 3.0 design patterns….
Justin King the presneter said he had another book about design patterns but it was so boring he fell asleep on every page.
www.dofactory.com has examples one can download examples from teh books above...


Regards,
Tom Bizannes
http://www.macroview.com.au

Synchronizing lists in SharePoint with Excel

Yes you can do it in both Excel 2003 and 2007

Did you know that you can get an addin for Excel 2007 for two way synchronization and you can write a macro to update a list from SharePoint via VBA code?

Just google "Publishing ad Synchronizing Excel 2007 tables to SharePoint Lists" to find the addin.

As for the Excel 2003 or 2007 vba code, this was cool.

A client wanted to color code a sheet from excel so we wrote code to dynamically create the list, copy it to another worksheet and colour in cells depending on the value in one column...

Just one click and this list always comes out nicely formatted.

Another case where a little coding can make life easier!

For Excel or SharePoint questions just goto:
http://www.macroview.com.au

SharePoint Designer Issues with local - remote screen

If you don't refresh the local and remote views by clicking on the refresh item, it doesn't sort by date although the newer date shows!
Also, getting issures were a file uploaded is still showing as changed because the auther is different...
But I just uploaded it!
So something weird about the PC setting is making this show me as another user.

Startup options for Microsoft Access

You can add command line switches when opening up access via a shortcut or bat file.

To go one step further we had to also use a specific security file.

See below for the bat file script:

@echo off
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" %~dp0MyDatabase.mdb /wrkgrp %~dp0MySystem.mdw
cls
@exit

This opens using access 2003 a database called MyDatabase and a Security file caled MySystem.

What is nice is that everything is relative to where the script lives...

Apparently this opens in shared mode and the other command line options are:

/excl   exclusive access.
/ro     read-only access.   
/user   Starts Access using the specified user name
/pwd    Starts Access using the specified password
/excl   Opens the specified Access database for exclusive access.
     To open the database database for shared access in a multiuser only environment, omit this option.
/x  macro    Starts Access and runs the specified macro.
          Another way to run a macro when you open a database is to use an Access AutoExec macro.                        
/cmd         Specifies that what follows on the command line is the value that will be returned by the Command function.
          This option must be the last option on the project command line.
          You can use a semicolon (;) as an alternative to /cmd.
/nostartup   Starts Access without displaying the startup dialog box
/wrkgrp      Starts Access using the specified  workgroup information file.               database
/runtime     Starts Access in run-time mode.

And these look good to use if you want to schedule compact and repair etc
Although, we put temp tables in separate tables to avoid database bloat and conflicts, so compact and repair isn't required.

/compact [target database]   Compacts and repairs the Access database
/convert [target database]   Converts an Access database in an earlier version
* Specify the source database before using the /convert option.

For any more detail just Google:
"How to use command-line switches in Microsoft Access"

###
Thinking of Migrating Access to Sql Server?
Talk to the guys who know all the tricks....
http://www.macroview.com.au

Sql Server User Group on Tuesday 10th March 2009

Wow, a large crowd and many questions.

The topic was SharePoint for Sql Guys

Was trying to present three things but so many questions we barely got through the first bit.

The three parts were:
What you need to tell your SharePoint Administrator
What you need to do
What tools can help you.

The feedback was quite good with most liking the topic etc

Will now need to add my scripts and presentation

Would recommend you run the reports that monitor the server or use settings to check the fragmentation
See….http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

You are probably better having a quick look at:
http://technet.microsoft.com/en-us/library/cc964305.aspx

sql server reporting services 2008 windows 2008 and sharepoint can all live together

Finally had the time to suss this out.

First you need to know the tricks to get to the hidden sharepoint admin settings and then there's one little config setting that will stop nearly every attempt to get it to work regardless of whether you use SharePoint integrated mode or native mode.

All I can say, is alot of people would strangle whoever created the sql server 2008 reporting services install because this one setting is just ridiculous.

And to think I thought it was IIS7 that was causing the issue!

Was getting errors like:

The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

and rsInvalidDataSourceReference

Regards,

Tom Bizannes

SharePoint and Sql Server

Sydney, Australia

http://www.macroview.com.au

Reporting Services and SharePoint Integration on Windows 2008 server

Had fun trying this on a windows 2008 box.
Note this also applies for setting up on a vista box....
Everything worked after hitting each bit a few times!
So important to get permissions set, root site collections set properly and IIS7 configured right before installing anything.

So the steps to install in order are:
Set up IIS7 with all the correct settings:

Common HTTP Features:
Static Content
Default Document
HTTP Redirection
Directory Browsing

Application Development:
ASP.Net
ISAPI Extension
ISAPI Filters

Security:
Windows Authentication

Tools:
IIS Metabase Management
IIS 6 WMI Management

Then make sure you set tcp and named pipes in the sql server surface area configuration tool for the remote connections.

Then install service 2 for sql server and the service pack for the service pack if it isn't up to version 9.0.3054

Then run the sharepoint reporting services msi file that integrates them noting this doesn't run on a windows 208 server unless you install a reg hack to run as admin or turn off the uac.

Then you need to go into the reporting services configuration tool to configure and that is where many get mixed up.
Reporting Services needs to use the same pool as sharepoint to integrate.

Everything finally fell into place except the reporting services configuration settings didn't show in the sharepoint admin application page.
Had to go to the _layouts pages instead.

When deploying your reports, the pc with report designer should also be service packed and then you get an extra option regarding sharepoint in your deployment options wording for your target folders etc.

http://servername:portno/_layouts/ReportServer/ReportServerSiteSettings.aspx
http://servername:portno/_layouts/ReportServer/ManageTrustedAccounts.aspx
http://servername:portno/_layouts/ReportServer/ReportServerSiteSettings.aspx?Settings=RS


Regards,
Tom Bizannes
Sydney, Australia
Microsoft SQL Server and Microsoft Access Consulting

VBA Copy missing files

Public Sub CopyMissingFiles(sProdFiles As String, sOfflineFiles As String, sFileExt As String)
Dim i As Integer
Dim sDestination As String
Dim iFiles As Integer
'FileExt = "*.pdf"

'Add \ at end if missing
If Not Right(sProdFiles, 1) = "\" Then sProdFiles = sProdFiles & "\"
If Not Right(sOfflineFiles, 1) = "\" Then sOfflineFiles = sOfflineFiles & "\"

iFiles = 0

With Application.FileSearch
.NewSearch
.LookIn = sProdFiles
.FileName = sFileExt
.SearchSubFolders = False
.Execute
For i = 1 To .FoundFiles.Count
sDestination = Replace(.FoundFiles(i), sProdFiles, sOfflineFiles)
If Not FileOrDirExists(sDestination) Then
'Debug.Print .FoundFiles(i)
FileCopy .FoundFiles(i), sDestination
iFiles = iFiles + 1
End If
Next i
End With

If iFiles > 0 Then
MsgBox "Copied " & iFiles & " pdf files to " & sOfflineFiles, vbInformation
End If

End Sub

Sub test_CopyMissingFiles()
Call CopyMissingFiles("D:\ProdTest\Files\", "D:\files\", "*.pdf")
End Sub