We needed a First-name & Initial list.

For a group attendance list.  For Privacy reasons we can’t list of the attendees full-name, but their first name and last name initial was considered private enough.

Screenshot of excel spreadsheet showing Firstname and Initial

The Excel function to do this was very simple, using the FIND and MID functions:

=MID(A2,1,(FIND(" ",A2)+1))

The joys of DLL hooks and eTrust Antivirus.

This is a guest post by Allan, a bloke I work with.  Allan asked my opinion of what was wrong, and I suggested a DNS server issue.  (Our DNS servers have a habit of not replying on the first query, but I was wrong. )

Recently I have spent a lot of time troubleshooting what appeared to be a DLL conflict issue when a customer installed PowerPivot for Excel 2010.

Without PowerPivot installed, you could happily connect to the SQL Analysis service using the msolap100.dll that gets installed by default (version 10.0.2733.0)

Install PowerPivot, and Microsoft Excel starts throwing you vague error messages, but ONLY the first time you try and connect.
The following system error occurred

And in the eventlog:
PowerPivot error in event log


So as usual I ran off and started playing with regsvr32, different DLL files and all that good stuff believing it was a faulty DLL.

The big issue was, there was nothing on Google. Nothing that reflected the error message I was getting, nothing that was really remotely close.

Working for a shared service organisation, I was able to install PowerPivot on another customers SOE that was also Windows XP.

Surprise Surprise… It worked.. No connection issues, no dramas.

So the next thing was to trouble shoot Group Policy, no issues there.. Logon as local admin… Nope, still have the issue. Make sure the security settings in Excel matched between the two environments… Nope….  Same deal, same vague error message.

So I installed Wireshark and noticed I was getting “RST,ACK” on the TCP packets on the initial connection attempts…

So after banging my head against the desk trying to find a solution.. It hit me, it could be the AntiVirus. One environment uses Mcafee, the other Etrust.

Etrust AntiVirus is a wonderful product, and just to be clear… That statement is laced with a healthy dose of sarcasm.

One thing that many people may not be aware of, is that the Hooks that Etrust uses, stay in place even with the services are stopped, you need to disable the services and reboot.

And that is exactly what I did…. A lo and behold…. The connection issue disappeared. And who said AV is never at fault.. Oh wait, that was me and its usually correct.

Luckily it’s a simple matter to disable the hook in to the msolap100.dll file.

  1. Fire up regedit and navigate to HKLM\SOFTWARE\ComputerAssociates\ITMRT
  2. Double click on the HookExclude key and add msolap100.dll at the end of the list.
  3. Hit OK, then reboot your PC.

Problem solved. You should be able to connect to your SQL R2 Cubes with no issues at all.

One way to anonymise live data for test use

Take your table/spreadsheet with your live data and

  • sort ONLY the field holding the surname into A –> Z order
  • sort ONLY the field holding the first name into Z –> A order
  • split any email address field at the @ sign, and trash the part before the @
  • transpose the first address field by 73%.
  • sort the field holding the SUBURB field into A –> Z order

Your data is now (mostly*) anonymous, but with real, ordinary field contents.

* If you can use any data record to identify a living individual after that then the file had far too few records in it for testing purposes anyway, and you should have made the data up in the first place.

An example
Anonymised data

I strung a number of Excel functions together, to transpose the Address1 field.  I’ve highlighted those in blue.

(the steps, and the comments around, how to anonymise data was shamelessly stolen from Tim Trent’s blog article Is it Legal to Test on Live Data?)

Excel: how do I calculate the date 24 weeks from today?

The question was:

Need to create series with dates in intervals of 6 weeks 24 weeks and 52 weeks. want it to be set to do it automatically. Can anyone help me?

The answer is simple.  You can use the Excel Date functions to do this:

Excel Date Functions

To break down what is happening here, using “24 weeks from today”, as an example.

The formula is
=DATE(YEAR(B2),MONTH(B2), DAY(B2)+168)
=DATE(2009,MONTH(B2), DAY(B2)+168)
=DATE(2009,12, DAY(B2)+168)
<- (168 days equals 7 days time 24 weeks)
=15 June 2010

Another way to do this is by using the TODAY verb.
ie.  =TODAY()+168
(with thanks to The Angry Technician)

Bookmark and Share

“VBA? You wrote VBA code for that?”

exclaimed a colleague.

“Is that like Visual Basic?”

Well kind of, VBA is Visual Basic for Applications.  Which is a scripting language for the Microsoft Office system.

I’d had a problem where an extract of an asset report only had the “Last user” and “Last software scan date” on the first record for each computer listed.
I needed each line to have entries, as I was going to sort the data later.

Excel screenshot showing blank cells. but I wanted this: Excel screenshot showing cells filled in CopyCell macro

There was over 4000 records in the extract, so I couldn’t “Fill Down”.

I spent some time looking at Excel spreadsheet functions, particularly the IF function, and kept on running into issues.

So I wrote a VBA Macro.

Dim RowNumber As Integer
Dim ComputerNameColumn As Integer
Dim LastUserColumn As Integer
Dim LastSoftwareScanDateColumn As Integer

ComputerNameColumn = 1
LastUserNameColumn = 2
LastSoftwareScanDateColumn = 4

For RowNumber = 2 To 2324
‘ compare Computer Names, if same the current computer name is same as the last
‘ copy the previous “Last User, Last Software Scan Date” columns.
If Cells(RowNumber, ComputerNameColumn) = Cells(RowNumber – 1, ComputerNameColumn) Then
Cells(RowNumber, LastUserNameColumn) = Cells(RowNumber – 1, LastUserNameColumn)
Cells(RowNumber, LastSoftwareScanDateColumn) = Cells(RowNumber – 1, LastSoftwareScanDateColumn)

End If

Yes, it is simple, and you can see it was written for one time use*.  But it worked.

You can download the sample spreadsheet here.

* – for persistent use, you might want to detect things like the ActiveSheet, the number of rows; and whether the “Last user/Last software scan date” cells are actually blank before you overwrite them.

Bookmark and Share

Microsoft Office Excel cannot find the data you’re searching for.

Excel find and replace not working.

I’m doing some analysis of Help Desk data, and I often use Excel for this task.  Now I know a helpdesk caller did log a problem with the subject “five things I hate about my iPhone”.

But Excel couldn’t find it (see picture right).

The problem was caused by the Excel feature “Find by format” feature (red dot).  To turn it off, click on the Format button (green dot), and then select the Clear option.

Bookmark and Share

How to use VLOOKUP on Microsoft Excel

VLookup allows you to check to see if cell A exists in a range of cells.  This might be useful if you want to compare two lists of names, or DVD’s for example.

Just like the example pictured here:


(click on the picture to see all of it)

The formula used above is: =VLOOKUP(A6,DVDsIown!A:A,1,FALSE)

It’s simple once you get the hang of it.  You can download the Excel spreadsheet I used above, here.

Microsoft Excel Online Help: VLookup reference
Tech on the net: Excel VLookup Function