Data Comparison Tricks

Hey Dick, thanks for having me over. Wow, it’s even nicer in here than I imagined. Look at all those posts! Hey, is that an Office XP beer stein… where’d you get that? Gosh, do you really wear all these baseball caps?

Okay, well great to be here. I hope I don’t blow it. I’m going to talk about a fairly pedestrian topic, but one I deal with daily as a data analyst and report writer: comparing versions of output data.

At my work we have a report modification and publication process to verify that they’re outputting reasonable results. A lot of times this means comparing a report to its previous published version and confirming that the outputs are identical before moving on with the process.

I’ll show some tricks I use to do these comparisons. Please note these examples all assume the data you’re comparing is easily re-creatable, e.g., it comes from a data connection or was exported from another tool. In other words, don’t do these tests on the only copy of your output!

The Most Basic of Tricks – Comparing Sums

One quick trick you’ve probably used is to grab an entire column of output and check its SUM in the status bar. Aside from comparing row counts, this is about as simple as it gets.

Status bar sum

I usually just look at the first three or so digits and the last three or so, mumble them to myself, switch to the other column and mumble those to myself. If my mumblings match, I call it good.

Mind you, I only do this as an informal check. Still, writing this got me to wondering how reliable it is, and about the likelihood of a false positive, a coincidental match. So I did a little test and filled two columns with RandBetween formulas then wrote a bit of VBA to recalculate them repeatedly and record the number of times their sums matched. With two columns of 1000 numbers, each filled with whole numbers between 1 and 1000, I averaged around three matches per 100,000 runs, or a .003% chance of a coincidental match. That’s a pretty small range of numbers though, equivalent to a span from one cent to ten dollars. So I upped it to whole numbers between one and a million, similar to one cent to 10,000 dollars. With a million calculations of 1000 rows there were no coincidental matching totals.

A More Thorough Trick – Compare All Cells

When I really want to make sure two sets of data with the same number of rows and columns match cell for cell, I do the obvious and … compare every cell. That could look something like this (but eventually won’t, so stick with me):

AND compare 1

The two sets of data (a modified version of the indispensable table from celeb-height-weight.psyphil.com) are on the left, with the comparison formulas for each cell on the right. In this case they all match and return TRUE:

If you’ve got more than a few columns and rows, you probably don’t want to scan all the comparison cells for FALSEs. Instead, you can wrap up all these comparisons in a single AND, like this. It will return FALSE if any of the referenced cells are FALSE:

AND compare 2

Or just eliminate the middleperson altogether with a single AND in an array formula:

AND compare 3

What If They Don’t All Match?

If they don’t all match you can add conditional formatting to highlight the FALSEs…

Conditional Formatting for FALSEs

… or just add it directly to the two tables. However, rather than conditional formatting I’d use a per-row AND array formula and filter to FALSE:

per-row ANDs

Same Data, Different Order

Sometimes my rows of data are the same, but they’re out of order. I try not to yell at them like Al Pacino. Instead I might test them with a COUNTIF(S) formula, like so, which just counts how many times the name in a the second table appears in the first table:

=COUNTIF($A$2:$A$131,E2)

To compare whole rows, you’re stuck (I think) with longer COUNTIFS formulas than I care to deal with. I’d rather concatenate the rows and compare the results with a COUNTIF. I don’t have many worksheet UDFs in my tools addin, but one exception is Rick Rothstein’s CONCAT function, which I found on Debra’s blog. It’s great because, unlike Excel’s Concatenate function, it allows you to specify a whole range, rather than listing each cell individually.

COUNTIFs can get slow though once you’ve got a few thousand rows of them. So, another approach is just to sort the outputs identically and then use an AND to compare them. Here’s a function I wrote to sort all the columns in a table:

Sub BlindlySortTable()
Dim lo As Excel.ListObject
Dim loCol As Excel.ListColumn

Set lo = ActiveCell.ListObject
With lo
    .Sort.SortFields.Clear
    For Each loCol In .ListColumns
        .Sort.SortFields.Add _
                Key:=loCol.DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    Next loCol
    With .Sort
        .Header = xlYes
        .MatchCase = False
        .Apply
    End With
End With
End Sub

At this point I should mention that I almost always work with Tables (VBA ListObjects) when doing these comparisons. A lot of the time I’ve stuffed the SQL right into the Table’s data connection. If the data is imported from something like Crystal Reports, I’ll convert it to a table before working with it.

Using Pivot Tables For Comparing Data – Fun!

As I get farther along in a report’s development, odds are I might just want to compare a subset of the old version to the whole new version, or vice-versa. Using pivot tables is great for this. Say for instance my new report is only for people whose weight is under 48 kilograms. I’d like to compare the output of the new report to a filtered list from the older version and confirm that I’m returning the same weights for the people in the new subset. A pivot table makes this easy:

Pivot compare

The pivot on the left, based on the original data, has been filtered by weight and compared to the pivot on the right, based on the new, less-than-48 data. An AND formula confirms whether the data in the new one matches the original.

I was doing this the other day with multiple subsets, causing the pivots to resize. I thought “wouldn’t it be cool to have a function that returns a range equal to a pivot table’s data area?” The answer was “yes,” so I wrote one. It returns either the used range or the data area of a table or pivot table associated with the cell passed to it. Here’s the code:

Public Enum GetRangeType
    UsedRange    '0
    'CurrentRegion - can't get to work in UDF in worksheet, just returns passed cell
    PivotTable    '1
    ListObject    '2
End Enum

Public Function GetRange(StartingPoint As Excel.Range, RangeType As GetRangeType) As Excel.Range
Dim GotRange As Excel.Range

With StartingPoint
    Select Case RangeType
    Case GetRangeType.UsedRange
        Set GotRange = .Worksheet.UsedRange
    Case GetRangeType.PivotTable
        Set GotRange = .PivotTable.TableRange1
    Case GetRangeType.ListObject
        Set GotRange = .ListObject.Range
    End Select
End With
Set GetRange = GotRange
End Function

The array-entered formula in H1 in the picture above becomes…

=GetRange(A3,1)= GetRange(E3,1)

… where 1 is a pivot table. You’ll note that the code itself uses the enum variable, which would be great if you could use the enums in a UDF. Also, you’ll see that I tried to have a cell’s CurrentRegion as an option but that doesn’t work. When returned to a UDF called from a worksheet, CurrentRegion just returns the cell the formula is in.

So Long

Okay then, see you later Dick. Thanks again for the invite. It means a lot to me.

No, no, don’t get up… I can show myself out and it looks like you’re working on something there. Wait a minute… no it couldn’t be… for a second there it looked like you were using a mouse… Must have been a trick of the light.

Cheers!

Formula Auditing – woes and arrows

I’ve been playing around with the Formula Auditing tools a fair bit recently. These things:
 
Formula Auditing Tools

 
In the course of this, I noticed a few things I haven’t before. Firstly, here’s my setup:
 
Formula Auditing Example_No Arrows
 
 
When I have cell D6 selected and click Trace Precedents, Excel is kind enough to draw just one arrow from the precedent range, while putting a box around the entire Precedent range so I can see where it is:
 
Formula Auditing Example_TracePrecedents
 
 
If I were to click on Trace Dependents for that cell, I’d like to see pretty much the same thing:
 
Formula Auditing Example_DesiredTraceDependents
 
 
…but here’s what I actually see:
 
Formula Auditing Example_TraceDependents
 
 
…which looks like something that Hippies were hanging on the walls in the late sixties:
 
String Picture
 
 
…when they weren’t out protesting, that is:
 
String Protest
 
 
Doing a Trace Precedents when there’s a much longer array of dependent cells involved looks even worse:
 
Formula Auditing Example_TraceDependents_Many
 
 
…and Excel becomes very sluggish as you scroll around, so presumably Excel is constantly redrawing these. Scrolling down doesn’t tell you much…that’s for sure:
 
Formula Auditing Example_TraceDependents_Many2
 
 
Let’s take a look at another setup, to better illustrate a couple of things I didn’t know until now:
 
Formula Auditing Example2_blank
 
 
Here’s what Trace Dependents has to say about cell B2:
 
Formula Auditing Example2_TraceDependents_Level1
 
 
One thing I didn’t realise until today, is that if you keep clicking that Trace Dependent button, Excel keeps drawing in additional levels of downstream dependents:
 
Formula Auditing Example2_TraceDependents_Level2
 
 
Formula Auditing Example2_TraceDependents_Level3
Formula Auditing Example2_TraceDependents_OffSheet
 
 
In case you didn’t know, you can double-click on any of the blue arrows, and you’ll be taken to the Precedent/Dependent cell concerned…particularly handy if it points somewhere off-screen. And you can double-click the arrow once you’re there to be magically transported back again. The dotted arrow pointing to a little sheet icon in the above screenshot tells you that there’s an off-sheet dependent that points at cell C19, which you can jump to if you double click on that dotted arrow and then select the reference from the Go To box:
 
Formula Auditing Example2_Goto
 
…although as you see above, the native dialog box is so narrow that you’re unable to actually see the cell addresses, and can’t be resized. In that case, you might want to download Jan Karel’s excellent RefTreeAnalyser, that fixes this and does a good deal more besides:
Formula Auditing Example_JK_UserForm
 
It also has a much better way of displaying precedents, by overlaying in the current window some little pictures of any precendents that happen to be out of view or on another sheet. (Would be really handy to have the same functionality for dependents too.):
 
Formula Auditing Example_JK_Visualise
 
 
Colin Legg has some great code that will also help you to determine all on-sheet and off-sheet precedent cells, using the .NavigateArrow method to actually travel up those blue arrows and thus find any precedents on other sheets. I imagine Jan Karel uses pretty much the same approach. [Edit: No, he doesn't ]. You’ve got to use the .NavigateArrow method, because the Range.Precedents property doesn’t return precedents on other sheets or other workbooks.

Now here’s something nasty I’ve just noticed about the native Formula Auditing tool: It doesn’t pick up on off-sheet references that involve any kind of Table Reference, although on-sheet references work just fine:
 
Formula Auditing Example_Tables offsheet
 
 
So it is well broken, in my opinion, because I often refer to Tables on other sheets. And both Colin’s code and Jan Karel’s addin won’t help you here, I’m afraid. [Edit: Jan Karel's code still catches these.] Seems to me the only way to get around this would be to search the worksheet for instances of a Table’s name occurring within formulas. That’s assuming there’s no way to actually read Excel’s dependency tree from wherever Excel maintains it. I seem to recall seeing a post a few years back about how you can extract information from the tree by extracting XML from the workbook file, but that might just be a flight of fancy. Anyone know whether it ispossible to interrogate the dependency tree directly somehow?

How ’bout colours instead of Arrows?

Given all those arrows can get pretty confusing, I thought I’d have a crack at coding up something that lets you use Conditional Formatting instead and/or arrows to highlight Dependents (Green) and Precedents (Blue). Here’s my starter for ten, with both Dependents and Precedents highlighted. Direct Dependents/Precedents get a darker colour and white bolded font so you can easily tell them from indirect:
 
Formula Auditing Example_Jeff1
 
…and you can restrict it to just showing direct:
Formula Auditing Example_Jeff2
 
…and overlay arrows if you want:
 
Formula Auditing Example_Jeff3
 
 
It automatically updates if you change your selection, too:
 
Formula Auditing Example_Jeff4

It doesn’t solve the Table issue mentioned above, but I’ve been finding it quite handy to get a quick feel for what’s going on in those crappy spreadsheets I inherited…err…designed recently.

Here’s a sample file:
FormulaAuditing_20140914

Hardware Recommendations

It seems I have server-issue reprieve, so I’ll take advantage of it by throwing up the first post in weeks. Don’t worry, it’s not Excel related. I mean, who can expect that from this site. I do have a couple posts in the hopper, I just can’t seem to login when I want to write the up.

I got an email from an old friend asking for my recommendations for a new desktop computer. I wrote a longer reply than I expected and figured I might as well post it here. That way you can give your two cents in the comments. First, I’ll summarize his question/requirements:

I want “serious scientific/number-crunching/math-oriented”, and things labeled “home”, “office”, and “gaming” don’t seem like the right fit. I would like to use this machine primarily for compiled Python, S Plus, Mathematica, and C++ dll’s I am coding for Excel and OpenOffice. Specific points:

  1. Is 8 GB RAM enough? Speed-wise, will 16 or more make a “significant” difference?
  2. Which processor: i7?
  3. Will a hybrid drive make any difference? Incidentally, I keep all of my documents on my NAS; I would, of course, keep development versions on the local HD, but really don’t need more than 150 MB (if that!) on it.
  4. Dell looks good – highly configurable — what’s your take?
  5. Monitor/Video Card: One of my Macs is 27″, and is awesome for my wife’s photo/cine avocation. I am writing you from a 17″ Mac ProBook. Works for me. So I won’t need a particularly large monitor (which one?!?); what I should do re the video card?
  6. I/O: Several USB 3.0; Wired Ethernet, of course; Bluetooth; Thunderbolt. DVD and SD drives would be great.

Here’s my reply:

I don’t profess to be an expert, but I’m happy to give you my opinion. Just take it for what it’s worth. My last three desktops were 1) custom built 2) HP HPE-150f and 3) custom built.

My Last Three Computers

  1. I read codinghorror.com about how he built his own home theater PC. I was cutting cable and needed something to run Windows Media Center for over the air TV. I somewhat followed his lead, but since mine would be out of sight, I didn’t worry about making it small or quiet. I wouldn’t recommend my particular build for your purposes because it was built with the new (at the time) Intel chipset that had HDMI out built-in.

    I was in a situation where I didn’t trust Dell anymore, didn’t know who to trust, and was interested in trying my hand at building hardware just for the experience. I made some errors along the way, but all-in-all it has been an outstanding machine. My biggest mistake was not buying a big enough power supply. I also had a hard drive go bad in the first year. Even with the missteps, the whole cost was about $1,400.

  2. I was ready to replace my Dell desktop because it was 7 years old. I had a reasonably good experience building a PC so I was going to go that route again. It was a different build, so I there was still some chances to make mistakes, but I priced it out on New Egg anyway. Then I went to HP to see what a comparable machine would cost. It was a few hundred dollars cheaper and it didn’t really make sense for me to build it myself. I got an i7 2.8Ghz with 8 GB.

    That was four years ago, or so, and this machine has been terrific. Zero problems and it does everything I want performance wise. My only problem with HP is that they have a lot of craziness going on – they announced they were getting out of the PC business, then retracted. It just gives me pause. We use HP exclusively at work (~1,000 desktops) and they are probably still my go-to manufacturer.

  3. The third desktop was for my son. It was still a better deal not to build it, but I wanted to give him the experience of building a PC from srcatch, so we went that route. It was a pretty good experience and it has been a good machine. If you’re interested in building your own, I’ll send you the component list.

Operating Systems

I run Win7 and my son runs Win8. He prefers Win8, but kids are stupid, so what are you going to do. On my HTPC, if I upgrade to Win8 I have to pay extra for Windows Media Center. In other words, I’m never upgrading. And when Win7 doesn’t work anymore, I’m going Linux/XMBC for the HTPC. Sorry Microsoft, but when you stopped building problems for guys like me you should have expected this.

Your Specific Questions

  1. I bought my HP with 4 GB (the stock amount) and bought another 4 from crucial.com. You can save some pretty good money that way, but price it both ways. Just make sure you get enough slots if you’re going to add after market RAM. If I were buying today, I wouldn’t get less than 12 GB because RAM is so cheap that even an incremental increase in performance is worth it. I’d look at 16 too. But really, I’ve been running 8 GB for a while, and I have no problems, so it might be a waste of money.
  2. I like my PCs to last 5-7 years, so I tend to over buy on CPU and RAM. I don’t what’s newer and better than i7, but I definitely would not go with i5.
  3. Another misstep on building my own was not having an SSD for a boot drive. If I were building again, I would get an SSD for a primary and a Western Digital for storage. SSDs are getting more reliable at a pretty quick pace.
  4. Six years ago, or so, I stopped buying Dell. I bought them exclusively for home and work for the 10 years prior, but the quality of the components was going down and I’d had enough. I’ve heard they’ve made a bit of a comeback, quality wise, but I’m not sold yet. Concurrent with them buying cheaper parts, they moved their customer service to India. Back in the day when I didn’t have to talk to Dell customer service because their shit just worked, I wouldn’t care where it was. But with failing parts, it was a problem. I’m not trying to denigrate an entire sub-continent. It was just that the company Dell hired really sucked. I’m sure the rest of India is very knowledgeable.
  5. I have two 22″ monitors side-by-side and love it. I can’t imagine coding in Excel without the VBE on one screen and Excel on the other. Even when I code in Ruby, Python, Java or whatever, I need to have stackoverflow on one screen and the IDE on the other. I will never have one monitor again – it is the single best productivity boost in my computing career (including my keyboard shortcut mania). Both my monitors are Acer that I got from Newegg. They’re slightly different heights, but I find that piano sheet music books level them up nicely. If you can get identical, that’s even better. My work monitors are identical 22″ ASUS and I’m very happy with those too.
  6. Don’t sacrifice on USB ports. They don’t all have to be 3.0, but make sure you have double what you think you need. Even if you don’t go with two monitors, you should consider a dual output video card in case you change your mind.

I’m a couple years away from replacing my desktop, but if I were forced to do it, I would price out HP, Lenovo, and Asus. And probably Dell for good measure, but I would have to be really convinced to go back to Dell. Lenovo and Asus have awesome reputations in laptops and I don’t know why that wouldn’t translate to desktops. I bought a used Asus convertible tablet several years ago and it still runs like a champ.

End of reply

If you have any opinions on desktop hardware, please leave them in the comments.

Servers Suck

Hey, did you notice that this page loaded in a few milliseconds instead of timing out? Me too!

I don’t know what caused that period of slow down because I don’t know much about servers. I know my CPU percentage has been higher lately. Here’s what it looks like over the last 30 days.

And over the last 24 hours.

Last night I created a support ticket with Digital Ocean. And today it miraculously works. It makes me suspicious, but I can’t imagine a business model where you throttle your customers until they call you on it. I mean except for the cable and cell phone industries.

So I’m going to enjoy it while it lasts. I’m also going to get in an Excel post today before I have more problems.

Sync Pivots from dropdown

Over at the Excel Guru forum, Yewee asks:

I have 3 sheets in my excel worksheet.

1. Org
2. DataSource
3. Pivots Table

My Pivot table will get the data from the DataSource sheet. I will like to have the filter of the Pivot Table from one of the cell in Org Sheet.

How can I do that?

Incredibly easily, if you have Excel 2010 or later…because:

  • a PivotTable with nothing but one field in the Filters pane looks and behaves pretty much exactly like a Data Validation dropdown does; and
  • that PivotTable can be hooked up to the other PivotTables via slicers, so that it controls them.

If you’re a long-time reader of this blog you probably already know that, and may want to skip to the end to find a bit of VBA that makes setting up Slicers slightly more easy. But if you came here via Google, then pull up a pew and read on.

So let’s say these are the two Pivots that you want to control via a dropdown, and you want to put the dropdown where the red rectangle is:

Two Pivots and target

 
 

First, create a new PivotTable from the datasource that the other pivots share (or make a copy of one of the existing Pivots) and in the PivotTable Fields pane add the field you want to filter the other Pivots by to the Filters pane. (If you created this Pivot by copying another, remove any other fields that might appear).

Faux DV and Fields List

Great: Now you have a PivotTable masquerading as a Data Validation Dropdown. From now on, I’ll call it the ‘Master Pivot’. So just drag that Master Pivot where you want it:

Faux DV and Pivots

 
 

From the ANALYZE tab of the PivotTable Tools contextual menu in the ribbon, click the Insert Slicer icon:

Insert Slicer

 
 

…and from the menu that comes up, choose the field name that matches the field you put in the Master Pivot:

Chosen field

 
 
…and your slicer will magically appear:

Slicer added

 
 

Now we connect that Slicer to the other PivotTables. To do that, right click on the Slicer that just appeared, and click the Report Connections option:

Right Click

 
 

You’ll see from the Report Connections box that comes up that currently it’s only connected to one PivotTable – which of course is the Master PivotTable that we used to insert the slicer in the first place:

Report Connections Master

 
 

What we want to do is connect it to the other PivotTables, by checking those other checkboxes:

SlicerConnections_AllControlled

 
 

(Optional) We might want to make it so that the user can only select one thing at a time by clicking on the Master Pivot filter dropdown, and unchecking Select Multiple Items, if that’s your intent:

Dont select multiple items

 
 

…and now all we need to do is move that Slicer somewhere out of sight (but don’t delete it):

Faux DV and Pivots

 
 

Now when we select a region from that Master Pivot dropdown…

Select Region

 
 
… all the other Pivots are filtered to match:

PivotsFiltered

 
 

That’s it…job done. As simple as possible, and no simpler.

Actually that’s a lie…unless there’s a good reason not to, it’s much simpler just to use a Slicer in the first place, and not bother with setting up the Master Pivot dropdown at all:

Just Use Slicer

 
 

Of course, that Slicer takes up much more room than our Master Pivot dropdown. So maybe that’s a good reason to use the Master Pivot approach, and not a slicer. Especially if we might want more than one dropdown to control all the Pivots and space is at a premium:

Multiple Dropdowns

Or you can do away with the Master Pivot altogether, and just set the slicers up between the actual ‘output’ pivots themselves, so that as soon as they change a PivotFilter setting in one of the Pivots, the others get changed too. (Note that this also happens with the ‘Master Pivot’ approach…it’s just that we don’t actually need to have that Master Pivot sitting there taking up space at all).

Programatically add and connect Slicers

I’ve always found it annoying that there’s no right-click option to add a Slicer to the currently selected PivotField. Plus connecting Slicers to multiple PivotTables is a drag. And also, I hate it how it adds new Slicers over the top of old slicers. So here’s some code that remedies all that:

Sub AddSlicer()
Dim pt As PivotTable
Dim ptOther As PivotTable
Dim pf As PivotField
Dim pc As PivotCache
Dim rng As Range
Dim sc As SlicerCache
Dim varAnswer As Variant
Dim bFoundCache As Boolean
Dim rngDest As Range

Set rng = ActiveCell

On Error Resume Next 'in case user has not selected a PivotField
Set pt = rng.PivotTable
Set pc = pt.PivotCache
Set pf = rng.PivotField
On Error GoTo 0

If pt Is Nothing Then Exit Sub


If pf.Orientation <> xlDataField Then
    Set rngDest = Intersect(ActiveCell.EntireRow, ActiveCell.Offset(, ActiveCell.CurrentRegion.Columns.Count + 1))
    On Error Resume Next 'SlicerCache might already exist
    With rng
        If pt.PivotCache.OLAP Then
            Set sc = ActiveWorkbook.SlicerCaches.Add2(pt, .PivotField.CubeField.Name)
        Else:  Set sc = ActiveWorkbook.SlicerCaches.Add2(pt, .PivotField.Name)
        End If
       sc.Slicers.Add SlicerDestination:=ActiveSheet, Top:=rngDest.Top, Left:=rngDest.Left
    End With
    If Err.Number > 0 Then 'SlicerCache already existed. Work out what it's index is
        On Error GoTo 0
        For Each sc In ActiveWorkbook.SlicerCaches
            For Each ptOther In sc.PivotTables
                If ptOther = pt Then
                    bFoundCache = True
                    Exit For
                End If
            Next ptOther
            If bFoundCache Then Exit For
        Next sc
    End If

    varAnswer = MsgBox(Prompt:="Make Slicer control the " & pf.Name & " field in all Pivots on the same sheet?", Buttons:=vbYesNo)
    If varAnswer = vbYes Then
        For Each ptOther In ActiveSheet.PivotTables
            If ptOther.CacheIndex = pt.CacheIndex And ptOther.Parent.Name = pt.Parent.Name Then
                sc.PivotTables.AddPivotTable ptOther
            End If
        Next
    End If


Else: MsgBox "You can't add a Slicer to a Values field."
End If
End Sub

In addition, the below code will add the Add Slicer icon to the right-click menu that comes up when you right click on a PivotField:

Option Explicit


Private Sub Workbook_Open()
AddShortcuts
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteShortcuts
End Sub

 
Sub AddShortcuts()
    Dim cbr As CommandBar
 
    DeleteShortcuts

    Set cbr = Application.CommandBars("PivotTable Context Menu")

    With cbr.Controls.Add(Type:=msoControlButton, Temporary:=True)
        .Caption = "Add Slicer"
        .Tag = "AddSlicer"
        .OnAction = "AddSlicer"
        .Style = msoButtonIconAndCaption
        .Picture = Application.CommandBars.GetImageMso("SlicerInsert", 16, 16)
    End With

End Sub
 
Sub DeleteShortcuts()
 
    Dim cbr As CommandBar
    Dim ctrl As CommandBarControl
   
    Set cbr = Application.CommandBars("PivotTable Context Menu")

    For Each ctrl In cbr.Controls
        Select Case ctrl.Tag
        Case "AddSlicer"
            ctrl.Delete
        End Select
    Next ctrl
 
End Sub

…meaning whenever I right click on a PivotField I get this:

AddSlicer

 
 

Clicking on that adds a Slicer to the selected field automatically, plus asks you:

Control all pivots

 
 

Hell yes, I do!

Here’s a sample file:
Sync-PivotTables-from-dropdown_20140818

 
 

Nightmare

A couple of quirky bugs…

Found a couple of funny bugs today that I thought I’d share.

First, this one:
 
Italics
 

Weird: Partially italicizing right-aligned text screws with the display of trailing spaces. But it doesn’t do the same to left-aligned text with leading spaces:
 
Italics2
 

And then there’s this one, where you start with a file that you’ve suppressed gridlines on:
 
Nada
 
…and then you add a New Window…which allows you to look at a different parts of the same file on dual monitors – or even the same monitor if it’s suitably wide, by clicking on this:
 
New Window2
 

(Aside: I never rated this feature as being particularly useful until I got a second monitor and then recently rediscovered it. Now I’m starting to think is indispensable, as I no longer have to scroll around half as much as I do when getting to grips with how spreadsheets are laid out, or when putting in new formulas that point from one region of a workbook to a completely different region of the same workbook. I’m sure Dick has been using it for years to avoid rodenting.)
 

But when you compare the new window (which Excel temporarily renamed SomeFileName:2) to the old (which Excel temporarily renamed SomeFileName:1), there’s a couple of subtle differences:
&nbps;
SideBySide

 

So it respects my wishes to not show the Formula bar, but ignores my wish to not display gridlines and headings. And if I close the original window – the one called SomeFileName:1 – then those settings I didn’t want from SomeFileName:2 are now in the file SomeFileName. Bummer.

So make sure that’s the one you close when you’re done. Otherwise your file now has gridlines and/or headings when you didn’t want it to.

More AutoHotkey Sugar

Ken Puls, the Excel Guru, sent me some nice AHK stuff and I’m trying to put a post together to share it with you. But it’s not happening so far. In the mean time, I didn’t want you to have to wait for these gems that I’ve been enjoying.

Definition and Last Position in the VBE

If you right click on a procedure call in the VBE and choose Definition, it takes you to that procedure. If you then right click and choose Last Position, it takes you back. If you’re like me, the thought of right clicking is abhorrent to you. Instead, I use the context menu key, but alas there’s a problem. It takes a few microseconds for that menu to show up. So while I press menu+D, all I’m really doing is inserting the letter ‘d’ in my procedure call.

As any good keyboard hound knows, you can use Shift+F2 and Ctrl+Shift+F2 to accomplish the task without the context menu delay. That’s great except that my sausage fingers don’t bend that way. Shift+F2 isn’t bad, but Ctrl+Shift+F2 is too much work for something I use as much as this. Without further ado

^d::
    Send +{F2}
    Return
^l::
    Send ^+{F2}
    Return

Now Ctrl+D gives me the definition and Ctrl+L takes me back. Sometimes hotkeys take a little getting used to. It’s easy to forget they’re there. This one, however, took about five seconds to become a natural part of my VBE workflow.

Switching tabs in Access

There’s so much about Access that isn’t keyboard friendly that it sickens me. But the worst is switching tabs. Ctrl+F6 cycles forward through the tabs and Ctrl+Shift+F6 cycles backward. Never you mind that Ctrl+Tab does that job in every other Windows program every released. Ever.

#IfWinActive ahk_class OMain
^Tab::
    Send ^{F6}
    Return
^+Tab::
    Send ^+{F6}
    Return

AHK’d!

Changing Field Size in Access

Speaking of crappy Access keyboarding, how about changing the field properties as you create a table? Sucks, huh? Here’s the scenario: I’m entering a field and changing the Data Type to Number. If I want a Long Integer, I’m good because that’s the default. If I want a Double, however, I have to press F6 to get to the Field Properties and change the field size to Double. Then, instead of Shift+F6 to back, I have to F6 five times to cycle through all of the windows/panes/screen areas. That’s just stupid. So

+F6::
    Send {F6 5}
    Return

That works for me because I don’t vary my Access window. If you hide your Navigation pane, or have some other configuration than me, you might have to change that 5 to something else.

Windows APIs

Ken has a nifty API generator that I’ll be posting about later. While mine is a little more pedestrian, I think it offers some advantages. I have a separate hotstring for every API that I care about. Here’s one

::declaregetopenfilename::
    SendInput !to!i{Enter}
(
+3If VBA7 Then
    Public Declare PtrSafe Function GetOpenFileName Lib "comdlg32.dll" Alias _
            "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
     
    Public Type OPENFILENAME
        lStructSize As Long
        hwndOwner As LongPtr
        hInstance As LongPtr
        lpstrFilter As String
        lpstrCustomFilter As String
        nMaxCustFilter As Long
        nFilterIndex As Long
        lpstrFile As String
        nMaxFile As Long
        lpstrFileTitle As String
        nMaxFileTitle As Long
        lpstrInitialDir As String
        lpstrTitle As String
        flags As Long
        nFileOffset As Integer
        nFileExtension As Integer
        lpstrDefExt As String
        lCustData As Long
        lpfnHook As LongPtr
        lpTemplateName As String
    End Type
 
+3Else

    Public Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
            "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
     
    Public Type OPENFILENAME
        lStructSize As Long
        hwndOwner As Long
        hInstance As Long
        lpstrFilter As String
        lpstrCustomFilter As String
        nMaxCustFilter As Long
        nFilterIndex As Long
        lpstrFile As String
        nMaxFile As Long
        lpstrFileTitle As String
        nMaxFileTitle As Long
        lpstrInitialDir As String
        lpstrTitle As String
        flags As Long
        nFileOffset As Integer
        nFileExtension As Integer
        lpstrDefExt As String
        lCustData As Long
        lpfnHook As Long
        lpTemplateName As String
    End Type
+3End If
)
SendInput !to!i{Enter}

That’s a big one. I type declaregetopenfilename in a module and this little honey appears. One of the advantages of this method, verbose though it is, is that the API is mostly intact in the AHK file. That makes it really easy to edit if the need were to arise. Making a new API hotstring is as easy as setting up declareapiname, pasting Jan Karel’s code, and changing a few things.

The first thing you have to change is the # signs have to be change to +3 or they won’t come across. The other thing is a little more subtle. Did you notice that I send Alt+TO, Alt+I, Enter before and after the API stuff? That turns off, then back on, the Auto Indent feature in the VBE. AHK sees all these tabs in my string and it dutifully returns them. That means that every line is indented one too many times from the line above it. Cumulatively. By the time you get to the last line, there’s 30 tabs in there.

By turning Auto Indent off and back on, I can keep my API looking clean in the AHK file, which I like.

One of these things is not like the other…

Sometimes when I copy code from the web and paste it into VBA, I get something like this:
 
Error
 

…and I’m damned if I know why. It’s not the usual culprit of incorrect quote marks. It’ something else, and I don’t know what.

Usually I just retype the offending line:
 
No error
 
…and after looking in vain for a difference, simply delete the bad one, and put it down to inexperience.

But not today. Because I’m tired of being compiled around. So today, I’m gonna find out why. And I’m going to use Excel to keep VBE honest. Conquer and #DIV/0, I say.

If I paste the two formulas into separate cells in Excel, then I can clearly see that something is amiss. I just can’t clearly see the actual something:
 
Excel 1
 

Okay, let’s get nasty, and atomise these suckers so I can compare their DNA:

=MID(A,ROW(A1:INDEX(A:A,LEN(A))),1)
=MID(B,ROW(B1:INDEX(B:B,LEN(A))),1)

Array
 

Well take a look at that…there’s the culprit:
 
False
 
No wonder I couldn’t see it:
 
Different

In this particular case, the culprit looks like Ken Pul’s Blog platform…I stole the code from a comment Jan Karel left there, and note that the code has no similar issue if I lift it directly from Jan Karel’s site.

There you go. Busted.

Makes me feel like singing:

Singing

Formulas for Sale


101 Ready to Use Excel Formulas

It’s for sale. If you pre-ordered, it’s probably on the way. If you didn’t, well, you know what to do.

I got my copy last night. My wife, who is not an Excel geek by any stretch of the imagination, is excited to read it. At 240 pages, it’s not the usual phone book sized tech book, and it seems more accessible to her. What she doesn’t realize is that it’s packed with awesome formulas and she’s actually going to learn something. That’ll show her.

After being on sale for a few hours it’s the 643,122nd ranked book in the world. Let’s see if we can get that up to 643,121.

Slicers and SlicerCaches

Behind the scenes, Excel does quite a bit of smart rationalisation in terms of SlicerCaches whenever you connect Slicers to mulitple PivotFields, and this can be a bit confusing if you’re not familiar with what’s going on. So let’s take a look-see.

First, let’s create three PivotTables all based on the same data source – meaning they all share the same PivotCache and therefore can all be connected – or ‘daisy-chained’ – together with Slicers later on if we so desire:
 
Three Pivots One Cache

Next, let’s add a separate Slicer for each of them, with each Slicer pointed at the “Item” field of it’s related Pivot:
 
Slicers and Pivots only
 
 
So that’s what we see. How does Excel see this?

Seperate SlicerCaches

There’s a couple of points to note about this diagram. Firstly, the boxes across the top are screenshots from the Report Connections dialog box:
 
ReportConnections

…which you get by right-clicking on a Slicer and selecting this:
 
ReportConnections<

And secondly, the reason I've drawn circles around those Item fields:
 
PivotField level

…is that I really want to underscore that slicers operate at the PivotField level, not on PivotTable level.
 
So where where we. Ah yes, three Pivots based on the same PivotCache, with three Slicers all pointing at the Item field of their respective PivotTable:
 
Seperate SlicerCaches

Let’s now change Slicer Two so that it also points at the Item field of PivotTable Three:
 
ReportConnections - both
 
How did that change the conceptual lay of the land?
Shared SlicerCaches2

 
Well, that looks different. Excel rationalised the SlicerCaches by ditching SlicerCache three, and now both Slicer Two and Slicer Three are connected to SlicerCache Two. Meaning that conceptually, they both point at the Item field in both PivotTable2 and PivotTable3. In fact, if you were to right click Slicer Three and look at the ReportConnecitons, you’d see it looks exactly the same as for Slicer 2, even though we didn’t touch it. And if we change the selection in one of these Slicers, we see it replicated in the other as well as in each Pivot. It’s as if those Slicers are one and the same:
One and the same
 
Interestingly, if we remove PivotTable3 from that SlicerConnections dialog:
 
ReportConnections - remove 3
 
…things don’t go back to the way before: Slicers Two and Three are still synced together, but control PivotTable2 only. PivotTable3 is completely slicer-less:
 
Remove PT3  from Slicer Two

There’s no way you can get that Slicer Three to operate independently on its own again. You’ll just have to delete it and add another, I’m afraid.

Adding Slicers Programatically

 
If you record a macro while adding a slicer, you get code like this:

ActiveWorkbook.SlicerCaches.Add(ActiveSheet.PivotTables("PivotTable1"), "SomePivotField"). _
        Slicers.Add ActiveSheet, , "SomePivotField", "SomePivotField", 146.25, 309.75, 144, 187.5

All those arguments of the Slicers.Add command are optional except the first. And all those numbers just tell Excel where you want the Slicer, and how big you want it to be. So you could just go ahead and use this for the same result:

ActiveWorkbook.SlicerCaches.Add(ActiveSheet.PivotTables("PivotTable1"), "SomePivotField"). _
        Slicers.Add ActiveSheet

You can actually add a SlicerCache that controls a pivot without adding a Slicer:

ActiveWorkbook.SlicerCaches.Add ActiveSheet.PivotTables("PivotTable1"), "SomePivotField"

…and then you can connect another PivotTable to it:

ActiveWorkbook.SlicerCaches("Slicer_SomePivotField").PivotTables.AddPivotTable (ActiveSheet _
        .PivotTables("PivotTable2"))

…meaning you now have an invisible slicer that keeps the pivots in sync, based on user selections from the pivot filters themselves. Spooky! Note that –

  • If you add another slicer to that same PivotField on either of those PivotTables, Excel simply uses the slicer cache you just set up, meaning the new slicer controls BOTH pivots, even though you just added it to one.
  • If you delete that slicer, Excel performs a Slicer Exorcism: it deletes the underlying SlicerCache, meaning your two pivots are no longer synced. Unspooky!

Here’s something else slightly spooky. Or rather, kooky. Normally if you delete a pivot that is the ONLY pivot that uses a particular PivotCache, Excel gets rid of the PivotCache automatically. Excel basically thinks “Well, we won’t need that crap lying around anymore”. But strangely, if you have a slicer set up for that pivot, then deleting the pivot leaves both the slicer AND the pivot cache alone. The PivotCache only gets deleted once you delete that orphaned slicer.

Well, that’s enough for today. But *HORROR* there’s some Slicer-related sequels coming to a screen near you soon:
 
Nightmare

So stay tuned. And awake.