Not all keyboard shortcuts are created equal

I thought I would do something different today for IKSD. Like so many things in life, one may find a whole spectrum of keyboard shortcut users. Let me elaborate:

First, there are those who don’t (but, I would say, who should). These are the people who use the keyboard to type letters and numbers. For everything else, there’s a mouse. Want to move the insertion point one character to the left? Use the mouse—there’s no need to bother with tapping that pesky little left arrow on the keyboard when you can reach over, drag the mouse to where you want it, click, and then move the mouse pointer out of the way!

Then there are those who discover one or two keyboard shortcuts that they find really useful, but never really venture into the realm of learning more. These are very often the Alt-set (not the alt-right!)—those who use Alt+xxx to enter special characters not available on the keyboard. You will often find a petite list of Alt codes pasted onto their PC box or the bottom of their monitors. They may even get someone more knowledgeable to help them create some custom shortcuts for certain special uses.

The next level is those who start learning keyboard shortcuts, and use the most basic set for certain tasks. These are the ones who learn about Ctrl+C/V/X (but somehow never seem to grasp the Cut/Copy/Paste—XCV keyboard layout connection) and some others (Ctrl+P, Ctrl+O, etc.). They generally only use system-wide shortcuts that work in all their programs for the most common tasks. And perhaps some program-specific exceptions like learning about Shift+F3 in Word (but don’t ask them what it does in Excel [it’s Insert Function, by the way—one I never use). These all, are in reality (or is that metaphorically), like the laity in the world of keyboard shortcuts.

After that, we move to the functionaries. Even here, we find different levels of progress, just like one progresses through degrees at university or coloured belts in karate. But all of these are the people who have embarked on the journey. The journey of self-improvement, or (before I start sounding like a movie-martial-arts-mystic), the journey of actively discovering and using more shortcuts. However, I recently came across shortcutfoo.com, and while I didn’t try it, I like the idea. Here, you can envelop yourself in the misty mountain, just you and your keyboard, no mouse to be heard lowing in the fields outside, and become one with the keyboard. Ok, I’ll stop now.

The point I want to make is that, while I readily admit that I am not the supreme grandmaster of keyboard shortcut-dom, there is a sure sign that someone has progressed beyond the level of just being an initiate, but is well on the way to becoming a master. And that is when they start understanding, not only that different programs have different shortcuts (that’s really for those who have just moved past initiation), or that certain shortcuts may do different things in different programs (getting into the coloured belts now…), but that even the same shortcut might work differently in different contexts within the same program (surely this must be black belt knowledge by now?).

Blackbelt keyboard shortcuts to master

I want to illustrate just a few of those, and the tool I will use for this is Excel, since it is probably the program I use that does this the most. I suppose I could list a number of others, but this is more of an effort to illustrate the point, than to provide a comprehensive list. Here is a simple workbook that you can use to practise these shortcuts and see the differences. It contains a small data range, duplicated across three worksheets (Normal sheet, Filtered list, and Table). Also, outside of that are list of numbers for rows ($J$1:$J$12) and columns ($A$17:$E$17) so that you can see when a worksheet row/column or only table row/column are being inserted deleted. Also, note that the discussion below is as for Excel 2016.

Consider the following keyboard shortcuts:

Shortcut: Ctrl + D

Normal data range

Fills cell from row(s) above. This includes all cell attributes (formatting (direct and conditional), validation, etc.) and contents.

Can fill down multiple rows and across multiple columns (although direction is always to fill down, not right).

Normal data range (filter applied)

When used on a single cell, fills cell from row directly above, even if that row is hidden by the filter.

When used down several rows, fills the top row down to all cells displayed by the filter, but leaves cells hidden by the filter unchanged.

Table (unfiltered)

As for normal data range.

Table (filter applied)

As for normal data range with filter.

Normal data range (and table) Normal data range and
table (filter applied)

Figure 1    Ctrl + D

Shortcut: Ctrl + `

Normal data range

Copies down a cell’s contents from the cell directly above. Only contents are copied, no other attributes.

Can only copy down from one row and one column (i.e., the cell above the active cell).

Normal data range (filter applied)

Copies down the value from the first row above not hidden by the filter (i.e., no longer the row directly above)

Table (unfiltered)

As for normal data range.

Table (filter applied)

As for normal data range with filter.

Normal data range (and table) Normal data range and table (filter applied)

Figure 2    Ctrl + ‘

Shortcut: Ctrl + +

Normal data range

Displays the Insert cells dialog, dynamically choosing between shifting cells down or right to make place for inserted cells.

Normal data range (filter applied)

Does nothing.

Table (unfiltered)

When one cell or several cells across one row are selected, automatically inserts a table row (not a worksheet row).

When and several cells across one column are selected, automatically inserts a table column (not a worksheet column).

When several cells across rows and columns are selected, inserts multiple rows or columns, based on which have more selected. In other words, if a symmetric range is selected (x rows by x columns), then x rows are inserted. If an asymmetric range is selected (x rows by x columns), then x rows will be inserted if x > y, and y columns will be inserted if x < y).

Table (filter applied)

Does nothing.

Normal data range (and table) Table (unfiltered)

Figure 3    Ctrl + +

Shortcut: Ctrl + –

Normal data range

Displays the Delete cells dialog, dynamically choosing between shifting cells up or left to fill up the deleted cells.

Normal data range (filter applied)

Deletes the entire row (but asks for confirmation).

Table (unfiltered)

When one cell or more than one cell within a single row are selected, automatically deletes a table (but not worksheet) row.

When cells across more than one row are selected, automatically deletes the table column (not the worksheet column).

When several cells across rows and columns are selected, deletes all selected rows or columns based on which have more selected (as with Ctrl + +).

Table (filter applied)

When one cell or more than one cell within a single row are selected, deletes the entire worksheet row (but asks for confirmation).

When cells across more than one row are selected, automatically deletes the table column (not the worksheet column). If the deleted column was filtered, then the filtering in the table for that column is removed (i.e., all rows hidden based on the filtering criteria specified for that column are shown).

When several cells across rows and columns are selected, deletes all selected columns (note the discrepancy with Ctrl++). If any of the deleted columns were filtered, then the filtering in the table for those columns is removed (i.e., all rows hidden based on the filtering criteria specified for those columns are shown).

Normal data range (and table)
Table (unfiltered) Table (filter applied)

Figure 4    Ctrl + +

I did not discuss Ctrl+R, which fills to the right, just as Ctrl+D fills down, because its functionality is not affected by context.

Another set which I could get into, but which I will for another day, is how copying and pasting to- and from filtered lists/tables differs from copying to- and from normal ranges. I think for IKSD 2018, I may do a post just on filtered list/table shortcuts, and their idiosyncrasies. If you don’t want to wait that long, you can embark on your own journey of discovery—the truth is, however, not within, but the truth is out there!

These are the first ones I could think of as I sat down to write this post. Are there others that you can add? Let me know in the comments of others that you would add to my list.

Share

Getting back the old Word Spell Checker

If it ain’t broke, don’t fix it. This, I suppose, is easier to say than to do. Still, it is something that I have, on more than one occasion, wished that Microsoft would take to heart. Items from recent memory include replacing the ultra-efficient Print dialog and Print Preview with the ultra-inefficient Print-Preview-and-Print in Word 2010, which is jack of all trades and decidedly master of none. Or removing the Autocorrect option from the spelling error context menu in Word 2013. Or removing the red/green/blue wavy underline colour scheme for spelling/grammar/contextual errors (admittedly, problematic for the colour-blind) and replacing it with red for spelling and blue for grammar and context combined (which I am still not certain works for all types of colour blindness, although that is not my area of expertise). Or the way they keep messing with the most-recently-used (MRU) file list—each version since Word 2007 has seen this change, and from my perspective, the iteration that was most useful to me was Word 2010, now two versions back already.

The problem

Another of these “improvements-as-regressions,” which I am writing about here, is replacing the Spelling and Grammar dialog with the Spelling pane (also in Word 2013, and persisting in Word 2016). Microsoft has had a long history with panes, a history that, to me, seems to have been very conflicted. For example, moving the Mail Merge wizard to the Mail Merge pane, and then obviating the need for it with the ribbon (the pane can still be accessed, though). Personally, I find panes to be, err, a pain!

The Spelling pane is especially irritating.

Compare the pane to the old dialog:

spelling-grammar-dialog-vs-spelling-pane

Figure 1    Spelling and Grammar dialog vs Spelling pane

First let me say what is nice about the pane (to be fair). If you open the pane and leave it there (which I am very unlikely to do), then as you move around your document, when you place the I-beam on an error (misspelled word or grammatical/contextual error) then you immediately get the spell checking options for that error. And (in an American accent), the pronunciation of the suggested word you have selected if it was a spelling error (I do not know how this is going to help me make up my mind which is the correct word, but hey, it’s there!). And, in an insincere nod to the disabled, the Spelling pane options have accelerator keys highlighted (e.g., Change) which you cannot use (or at least, I have not figured out how to use them). Yes, I am being sarcastic. The “features” of the new Spelling pane are not really, in my mind, all that great. I could easily get by without them. Really.

What is so bad about the pane, you may ask? Well, it’s not what it’s got that’s bad, but rather what went missing from the dialog to the pane. As with the context menu, AutoCorrect is missing. As is Undo. Microsoft might argue that Undo was not used here a lot, but when I was zipping along through a long document, trying to whip the spelling into shape, then I found that I have often clicked the Change button a bit too hastily (perhaps it’s just me). Having Undo there was really a time saver. I could also immediately go to Options…, which, in all truth, I can still do in the usual way when using the pane. I could also focus only on spelling by turning the grammar checking off with the Check grammar box, which also helped me manage my workload. So yes, I miss those tools. Oh, and the keyboard accelerators actually work. How about that?

The solution

So what do we do when Microsoft throws us a curveball like this?

Do what I did with the Print dialog (you can read about it in my book or see the discussion I have in the Word uTIlities Help file)—take back what is (rightfully) ours!

The nice part about this is that you can decide how fully you want to revert—you can keep the Spelling pane and add the Spelling and Grammar dialog, or you can more fully replace the Spelling pane with the dialog.

The first part of this is to add a macro that will give us the old dialog. So, press Alt+F8. Then, in the Macro name box of the Macros dialog, type SpellingAndGrammarDialog and click on Create (Figure 2).

creat-spellingandgrammardialog-macro

Figure 2    Creating the SpellingAndGrammarDialog macro

This will open the VBA Editor, with the Sub and End Sub statements for the macro already created. All you need to add is one line of code:

Sub SpellingAndGrammarDialog ()
  Application.Dialogs(wdDialogToolsSpellingAndGrammar).Show
End Sub

Press Alt+Q to get back to Word and just remember to save the Normal Template.

To add this newly created tool over and above the Spelling pane, just add it to the QAT. To replace the Spelling pane, assign the F7 key to the macro, instead of ToolsProofing. If you really can’t live without the Spelling pane now and then, you can still access it from the Review ribbon tab (modifying the ribbon to point to the macro is beyond where I want to go with this post). Now, to get back to real efficient spell checking, just press F7!

Share

Announcing Word uTIlities 2.0

Well, it’s been two years since the last version of the Word uTIlities was released. Note: that means no new versions released to the adoring public constituency (all ten of you J) but not, by any means, no programming being done on my side: Considering the amount of code, I have almost doubled the work done for the first versions. But now the Word uTIlities are being released in an update that represents such a reworking of much of the core of the tools that I have decided it is time to increment the main version. So welcome to Word uTIlities 2.0!

What’s new in the Word uTIlities?

Word 2016 compatible

First up, all the tools have been tested and work with Word 2016, 32-bit and 64-bit.

New dialogs

Thanks to code posted by Dean Kinnear, I was able to capitalise on changes to Word 2013 to give users of Word 2013 and 2016 better message boxes. So if you are using Word 2013/2016, some of your dialog boxes will look like this:

image1

Instead of like this:

image2

They might even look like that in Word 2007 and Word 2010, depending on some system settings, as I have tried to keep things consistent across versions.

New reports

The reporting tools have been totally redone. Three reports are generated at present: The style summary, the list of bookmarks, and the list of index entries. In fact, the three separate reporting tools have now been replaced with one central tool that generates consistent reports for all three functions (and will allow me to easily add to the list of reports in the future). Now users are presented with a choice of whether they want the report in Excel or Word. The information-gathering process for the reports has also been streamlined for faster reports, although the report generation is a bit slower initially because the program first checks to see if it can connect to Excel. Once it knows what to do, though, the final report is also generated quicker than in previous versions.

Improved Backup

Even though it was working well, I have totally rebuilt the Backup tool. It is not only faster, but also now no longer needs to close and reopen the document when the backup is being made. The tool also now searches for existing backups and can then automatically determine how to name successive backups. What this means is that when you do your first backup on any given day, the process will be just as normal. If you make the second backup, it will, as in previous versions, ask you whether you want to make an alphabetic-suffix or time-stamp suffix second backup. However, what is new is that from there, it will find those backups, and will then automatically continue with your choice. So if you make a third backup on the same day, then it will automatically use the next in the sequence of the same suffix (alphabetic or time stamp). Therefore, making multiple backups on the same day will be much simpler. It also does a lot of other background stuff, like checking the amount of disk space for the backup file, etc.

I still have more plans for this tool, but that will come in a later update.

Document Statistics

Okay, I really didn’t do much here (i.e., nothing, really). This is a Microsoft Word tool (not mine at all), but it is useful in certain contexts, and it is so deeply hidden that I thought it deserves a little more attention. So all this tool does is open the Word Document Statistics summary.

Style Summary

Thanks to a very insightful collaboration with Howard Silcock in 2014 (Thanks, Howard!), I was able to squeeze a bit more out of this tool in terms of overall speed and functionality. Not much, but generating a style summary is such a laborious process that I felt every bit helps. I also have things that I want to try with this, as I really believe that it can be improved a lot, but I will need to do a lot of learning before I get there.

Numbered equation

Changes Microsoft made to Word 2013 document formats meant that this tool was not well behaved with Word 2013 any more, nor with Word 2016. But that has been fixed. I have had to sacrifice the centred equation (which you still get in Word 2010 and Word 2007), but there really is not much that I can do about it without Microsoft making an about turn on the changes they have made to the document format, which I consider unlikely.

Change Revision authors

Some tweaks to work around idiosyncrasies in Word document structure that caused an error.

Change Comment authors

In a moment of weakness, I forgot that when an input box gets cancelled, it takes a blank string as its argument, which meant that when you open the Change comment authors tool, and cancel it, all your comment authors get removed regardless. This is probably not what should be happening, so I changed the functioning of the tool so that cancelling the dialog, or emptying the input box and clicking on OK, will result in nothing being done. To change the comment authors, enter the new name and click on OK (as in previous versions). To remove the authors completely, enter DELETE in the input box (which is also now the default when the tool is launched).

Fit Hyperlink

Some minor tweaks to improve how it works when used inside a table. Still not perfect, but I’m getting closer….

And what’s in store

I am working on two new tools which I think are really great. These will be released as versions 2.1 (and possibly 2.2). I will give you a hint as to what’s in store. Think revisions and think dates…. Once again, the impossible will be made possible right here.

I also have a number of other things that I would like to include, but it takes time…. I will, of course, post about them here when I eventually get them done.

So, without further ado, go take a look!

Share

Correcting the Windows 10 number formatting settings for the South African region

A while back I decided to bite the bullet and go for Microsoft’s free Windows 7 to Windows 10 upgrade offer.

I did cause myself a bit more pain than I needed to, because I decided it was also time to declutter my pc a bit, so I went for a full format-and-install (although the other PCs in my home I just put through the normal Windows Update upgrade routine).

As I started settling in, I soon realised that there we things about Windows 7 that I missed (like the Recent Places feature in Windows Explorer which has survived several Windows versions, but has been “upgraded” in Windows 10’s File Explorer. But there are also features in Windows 10 that are welcome additions (Virtual Desktops are very nice).

However, when I settled down to getting some serious work done, I quickly realised that there was a serious mistake in the regionalisation settings for Windows 10. Instead of the comma for the list separator (which it has always been in previous versions of Windows set to the South African region, and which, in fact, it was in Windows 7 before the update/upgrade), Windows 10 substituted the semicolon. And the decimal separator, which was always the period, was now the comma. This may seem like a small thing, but it led to all kinds of problems: Comma-separated files were not being imported properly into Excel or Access. Bank statement values were not being recognised, because the financial amounts used a period to indicate the decimal (as they should), Excel formulas now had to be typed with semicolons instead of commas to separate the arguments (it’s amazing how ingrained that habit of using commas was), etc. So I needed a solution.

A quick search on the Microsoft Community site revealed that I was not alone: http://answers.microsoft.com/en-us/windows/forum/windows_10-other_settings/south-africas-region-settings-are-wrong-how-can-i/1ee9cdc0-4e8c-415f-912f-1bf0b4ab4a7d. But that, of course, didn’t help. The big issue was that when I wanted to change the items in question, the values I was looking for were not there. Specifically, in the Customise Format dialog (which I will show soon), the Decimal symbol and List separator boxes show only the comma and the semicolon, respectively, and clicking on the dropdown arrow for those boxes do not reveal the items I wanted (the period and the comma, respectively). I started preparing myself to edit the registry (and I will show this in a moment too), but just when I thought I was stuck, it occurred to me that these might not be list boxes, but combo boxes, and that even if what I wanted was not in the list, I could just type it in. And so, the (anticlimactically simple) solution is to just open the Customise Format dialog, and type in the correct values.

These are the steps: Right click on the Start button, and choose Control Panel (Figure 1).

Fig 1
Figure 1    Opening the Control Panel

In the Control Panel, choose Change date, time or number formats under the Clock, Language and Region group (Figure 2).

Fig 2
Figure 2    Control Panel

This opens the Region dialog. Here, click on the Additional settings… button (Figure 3).

Fig 3
Figure 3    Region dialog

This opens the Customise Format dialog (Figure 4), where you can type in the correct Decimal symbol and the correct List separator (the other settings are fine).

Fig 4
Figure 4    Customise Format dialog

Of course, it is so that you would have to do that for each user on the PC, and if it happens to be a PC with numerous users registered, and you are comfortable with editing the registry, then it may be quicker to correct these values for all users on the registry. To do this, right click on the Start button, and choose Run (Figure 5).

Fig 5
Figure 5    Opening the Run dialog

The type Regedit and click OK (Figure 6).

Fig 6
Figure 6    Run dialog

After asking your for the Administrator’s permission, this will open the Windows registry (Figure 7). Where you need to go is as follows: First, go to HKEY_CURRENT_USER>Control Panel>International.

Fig 7
Figure 7    Registry changes for Decimal symbol and List separator

There, double click on the sDecimal and sList keys, and type in the correct values (Figure 8).

Fig 8
Figure 8    Editing the sDecimal key

Next, go to HKEY_USERS, and then repeat the process for each listed user (e.g., .DEFAULT>Control Panel>International, etc.).

Simple as that.

However, remember these steps, as I would not put it past Microsoft to “correct” these settings during some or other update (although I do say that without any warrant).

Share

Custom number formatting: Getting Excel to show date and day in one cell

If there is one skill you have to learn that will really make a different to the quality of your Excel work, it is learning to use custom number formatting. Here is one example.

It often happens that I have a list of dates against which data are going to be entered, but I would like to see the weekday as well (this will help me with the data entry).

An example is shown in Figure 1. My traditional approach, which is evident there, was to add a column and use the formula =TEXT(B3,”DDDD”) or =TEXT(B3,”DDD”) to show the day.

Date and day in two columns

Figure 1    Column to show day of week

About a year ago, I thought to myself that this extra column was really unnecessary, and that I should try to remove it. My immediate thought was to try and combine the date and day in the Date column, and I realised immediately that the only way for me to do that would be with custom number formatting. I knew that dates are stored in Excel as a serial number counting from 1 January 1900 (albeit with one intentional error—see here as well for some more info, and note Microsoft’s “diplomatic” choice of words!). So, for example, the date in cell B3 (2015-01-01) in Figure 1 is stored as the serial 42005. The display of the date is added with cell number formatting. So then the thought came to me that I could try using two different date-style custom number formats in one cell together, and it worked.

So the date format already added by Excel is yyyy/mm/dd, and I decided to try something like ddd yyyy/mm/dd, as seen in Figure 2, which also shows the result in column B (and I have already deleted the “Weekday” column which was in column C). the ddd format tells Excel to show the cell value in the short day format—I could also use dddd for the full day.

Figure 2    Adding a custom number format to show both day and date

That’s a simple change, but it is very effective. Now I only need the date column, and don’t have to copy down additional values in the next column to keep up to date with it, if you will excuse the pun.

Share

Using keyboard shortcuts for Windows Explorer

In support of International Keyboard Shortcut day, I want to increase everyone’s productivity in using Windows Explorer.

Now I must add that one of the best productivity steps you can make is not to use Windows Explorer! I use Total Commander for most of my file management duties, and there are several (hundred) good reasons why I do. But having said that, if you work on Windows, you cannot really escape Windows Explorer completely, as it is the native file management tool, so most programs, when opening or saving files, simply make use of Windows Explorer for that file management portion. So even if you are working in Word or Excel or a multitude of other programs, when you open or save a file, you are actually using Windows Explorer for that action. And, I have found that some these basic principles described below, with a little creativity, can also be used when doing file management on a Mac (although I am not showing that in this post).

Thus, learning to use Windows Explorer effectively will prove to be a big boost to your productivity (just think how many times you browse to open or save a file each day, and the gains will be readily apparent).

To distinguish between these contexts I will speak, below, of working in Windows Explorer or of working in File Management (some of the shortcuts only work in one of those contexts, or work differently between the two).

I will also not cover “standard” keyboard techniques, like using Tab to move between parts of the window, pressing Enter as the default action, selecting items (files/folders, in this instance) using Ctrl or Shift in combination with the mouse, etc.

One last note. These are specifically for Windows Explorer, meaning they will work for Windows 7, most of them will work for Vista, and a smaller subset will also work for Windows XP. Having said that, apart from the ribbon addition with its own set of navigation tools, most of these functions will also work with File Explorer (i.e., Windows 8 or Windows 10), although I might miss something new from those over here.

Just to help with the explanations below, Figure 1 shows my names for the various parts of the interface, so that you know what I am referring to.

Figure 1    Windows Explorer Interface

Getting there

Charity begins at home, they say. So keyboard shortcuts should start with actually accessing the tools. For many programs (including all the Office programs), these keyboard shortcuts will get you started:

Listing 1    Shortcuts for Opening or Accessing Windows Explorer

F12
File Save As dialog (from most applications)

Ctrl + O
File Open dialog (from most applications)

ÿ + E
Open Windows Explorer application

The basics

Like most Microsoft programs (I hate working in programs where the developers have not included a good stable of keyboard shortcuts), the Windows Explorer interface comes with a good set of keyboard shortcuts, only they are perhaps less easy to discover than the more traditional interfaces.

Interface

Listing 2    Shortcuts for Working with the Windows Explorer Interface

Alt + D or F4
Select Folder Address box (Alt + D selects the entry in the box, F4 selects the box and shows a history of previous entries)

Ctrl + F or F3 or Ctrl + E
Select Search box

F5
Refresh the current window

F6 or Tab
Moves among panes in Windows Explorer (Adding Shift reverses the direction)

Alt + P
Toggle the preview pane (one of my favourite “hidden” shortcuts)

Ctrl + Scroll
Switch between views (e.g., List, Details, Small/Medium/Large/Extra Large icons). (I wish there were a keyboard only version of this, so if you know of one, enlighten me. I find the scroll functionality on this particular tool a bit fiddly).

Esc
Cancel in Windows File Management

Ctrl + N
Opens a new window in Windows Explorer

Ctrl + W
Closes the current window in Windows Explorer

F11
Toggle full screen view—can also be done using standard Windows 7 navigation tools, ÿ+Up Arrow for full screen (maximize), or ÿ+Down Arrow for normal size (restore)

Management

Listing 3    Shortcuts for File and Folder Management

Ctrl + Shift + N
Create new folder

F2
Rename

Del
Delete selected files or folders (to recycle bin)

Shift + Del
Delete selected files or folders (semi) permanently

Alt + Enter
Open Properties dialog for selected item

Ctrl + Click
Selects multiple individual items

Ctrl + Click and drag with mouse
Copy the item (Windows appends ” – copy” to the file or folder name)

Ctrl + Shift + Click and drag with mouse
Create a shortcut (*.lnk) for the selected item (Windows appends ” – shortcut” to the file or folder name)

Navigation

Listing 4    Shortcuts for Windows Explorer Navigation

Alt + Right Arrow
Follow breadcrumbs forward

Alt + Left Arrow
Follow breadcrumbs backward

Alt + Up Arrow
Moves up one folder level in Windows Explorer

Backspace
Displays the previous folder in Windows Explorer (i.e., same as Alt + Up Arrow).
Moves up one folder level in Windows File Management

A short explanation of the breadcrumbs is in order. As you navigate through your various folders, Windows Explorer maintains a list of all the locations you visited. These locations are not always in linear succession (i.e., in a succession of child- or parent folders). For example, if you are in a certain sub-sub-sub-sub-folder of an external drive, and you click on one of the shortcuts in the Favorites section of the Windows Explorer folder pane (don’t forget these, they represent one place where the mouse can save you a lot of time, although they can be accessed in about the same amount of time with the keyboard shortcuts already shown). This move will have taken you to a different folder on a different drive. You can click on the Back button (rather press Alt + Left Arrow) to go back to that sub-sub-sub-sub-folder of your external drive). And having done that, you can click Forward (rather press Alt + Right Arrow) to go back once more to Documents. Pressing F4 (not Alt + D will show you the complete list of your previous locations, and you can move up and down that list with the Up Arrow and Down Arrow arrows, and press Enter to select the location of your choice.

Folder pane

These shortcuts work only in the folder pane on the left of the file browser window:

Listing 5    Windows Explorer Folder Pane Shortcuts

Right Arrow
Expands the subfolders of the currently selected folder or goes to the first child if already expanded

Numeric Keypad +
Expands the subfolders of the currently selected folder

Numeric Keypad *
Expands everything under the currently selected folder (don’t do this on C:\)!

Left Arrow
Collapses the subfolders of the currently selected folder or goes to the parent if already collapsed

Numeric Keypad –
Collapses the subfolders of the currently selected folder

Ctrl + Shift + E
Expands the Navigation pane to the folder in which the selected item is found.

More advanced bits

Remember that if you have clicked in the file list of Windows Explorer, you can easily jump to certain files by typing the first letters of a name (you have to type reasonably quickly). So, for example, in Figure 1 I can get to the WordPress folder (and bypass the Word folder) by typing “wordp.” Also, if I wanted to move through the various folders starting with a “c,” I could just press “c” repeatedly with a short break in between.

Figure 2    Selecting an item by typing its name

Then, of course, one of my personal favourites, is typing the destination I want to get it, instead of browsing by a series of mouse clicks. This relies on using the file syntax that derives pretty much all the way from MS-DOS. The first thing you need to know is that you can specify lower-level folders by typing their names. You specify (see below) higher level folders with “..\” and the root with only “\” (all sans the quotes, obviously). You can introduce a succession of lower-level folders by typing the folder names, separated by “\” (again sans quotes).

Listing 6    Text Entries Used for Specifying Higher-Level Folders

..\
Move one folder up (can be combined x number of times for x number of levels).

\
In Windows Explorer, takes you to the root of the main drive.
In Windows File Management, takes you to the root of the current drive.

aaa\bbb\ccc
Specifies folder ccc which is a subfolder of bbb, which is itself a subfolder of aaa, which is itself a subfolder of the current folder.

The best way to demonstrate the power of this technique is with a short video. I am saving a page from the Daily Dose of Excel blog by printing it as a pdf from Chrome. This is relevant for a tool that I am developing, and so I want to keep this, and look at it when I work on this tool. I obviously have to indicate where the pdf must be saved. I last saved a pdf in another folder two levels down in the Documents folder, so I first have to move two levels up (which I do by typing “..\..\”), and then down to “Computer Training\Excel\Tips – Excel\Macros\.” This is a lot of scrolling and clicking with the mouse. However, note that as I type the address into the file name box, Windows Explorer gives me all folder and files that match what I have typed. I then use this to select the folders I want in a fraction of the time it would take to do with the mouse (on an unrepresentative sample of one trial, 13s with the keyboard vs 20s with the mouse, for a 35% increase in efficiency!). Also note that by typing the folder names, I “lose” the file name, but Windows Explorer “remembers” it, so that when I eventually reach the folder I am aiming for, the name is given back (I can, of course, then type a different name if I want).


Video 1    Using typing to select a folder or file

One last tip. Sometimes, I have to “load” a file to a certain program. As one example: When I using the Spreadsheet Compare tool to compare two workbooks. I already have Windows Explorer open at the location where the file is (actually, I can do this trick much quicker in Total Commander, but this blog post is about Windows Explorer). In my program (e.g., Spreadsheet Compare) I have to click on the browse button and this opens the Windows Explorer file browser at a predefined location—e.g., a default location like “Documents” or the last used location, neither of which is what I want. But I can copy the address of the location for my file (in Total Commander I can copy the file name with its location, which means I often do not even have to click on Browse). I do this either by clicking in the Windows Explorer Folder Address box and copying the full location there (see Figure 3), or I can, of course, eschew the mouse and do it all with Alt + D, Ctrl + C, Esc (and I’m done before you even had a chance to see what I’m doing!). [As a side note, F4 doesn’t work as well as Alt + D for this one.] Now I can paste the location into the Spreadsheet Compare file browser (of course, I jumped there from Windows Explorer with Alt + Tab), press Enter, and jump straight to the location where the file is. Better yet, I can type “\” and start typing the file name, and Windows Explorer will give me the names that match—even quicker!

Figure 3    Selecting a folder address from the Address box

One last trick. You can also apply filters in the file name box. So, for example, in Figure 4 I want to see only the Excel files, not all the other files, so I type *.xlsx and press Enter. This will filter the display and show me only Excel (and only xlsx Excel) files.

Figure 4    Applying a filter in the file name box

Share

A cool browser keyboard shortcut

I am working on a big keyboard shortcut post for 4 November—International Keyboard Shortcut Day in 2015 ;-)—but in the mean time, I made a little discovery today that I felt I should share.

Some time ago, I wrote about learning keyboard shortcuts. And although I often fail, I do still try to practice what I preach (in fact, I have used three already in the first two paragraphs of this post—two for adding the hyperlinks and one for the italics).

In that previous post, I wrote that one way we often discover keyboard shortcuts is by accident—for example, when we press something on the keyboard we did not intend to. You may argue that that makes keyboard shortcuts dangerous—we intend pressing one thing, but press something else, and then we have something bad happen. I counter by pointing out that I am hard pressed to think of a keyboard shortcut for which the handy undo (Ctrl+Z) does not apply. Furthermore, and more to the point, the mouse is also dangerous, if not even more dangerous. I cannot think when I have caused “catastrophic” (i.e., unrecoverable, or at least hard-to-recover) damage with the keyboard, but I can think of several instances with the mouse. How many times have you dragged a file or an e-mail, and dropped it (accidentally, of course) into the wrong folder, and spent ages trying to find your now missing item? Enough said.

Today, I discovered another shortcut like that—I wanted to close a tab in Chrome, and instead of pressing Ctrl+F4, I pressed Ctrl+4 (just a little South of where I wanted to be…). Now when I have multiple browser tabs open (which is all of the time!), I navigate between tabs with the old faithful Ctrl+Tab (or Ctrl+Shift+Tab to reverse direction). However, through this fortuitous mistake, I discovered that when I have multiple tabs open, I can easily jump to a tab by pressing Ctrl+x, where x is the tab’s position number (from left to right). Yes, this will probably not work when you have a gazillion tabs open, but if you have a handful open that will allow you to count at a glance, this will halve the tab navigation time.I also tend to have some standard tabs open in standard positions (GMail, my website dashboard, etc.), so this will help me jump to those tabs very quickly from now on.

A little further testing has shown that the way this shortcut works is that Ctrl+1 through Ctrl+8 take you through the first 8 tabs, while Ctrl+9 handily takes you to the very last tab.

My next step was to immediately test it in other browsers, and I am happy to report that, like numerous other browser shortcuts (e.g., Ctrl+T to open a new tab, Alt+D to select the address bar), it works in Internet Explorer (I’ll test it in Edge later) and Firefox as well. So there you have it: One new shortcut in three new contexts.

So this is one small incremental improvement in productivity (made more so by the multiple browsers in which it works), but the important thing to remember with using keyboard shortcuts is that it all adds up—the gain from using one shortcut one time is minimal, but the gain from using many shortcuts multiple times is huge!

Share

Calculating the mean from a frequency table: Array formula example

Array formulas are difficult to get your head around, but once you start understanding the way in which they work, you will find that there is a lot to love about array formulas (sometimes called CSEs because you have to type the formula and then press Ctrl+Shift+Enter to have it entered as an array formula). Of course, the converse sometimes also happens—once someone “gets” array formulas, they start using them for all kinds of tasks, even where non-array equivalents exist. That is generally not a good thing, as array formulas are computationally cumbersome, and also have other drawbacks, chief of which is that they can be edited and entered with only the Enter button instead of Ctrl+Shift+Enter. Sometimes, this results in an error value, which immediately shows the problem, but other times, the non-array formula returns a result, although that result is different (and therefore incorrect) from the same formula array-entered.

Here, however, is one nice little application of an array formula which shows their power.

It is not uncommon for me, as a statistician, to receive a table like this:

Figure 1    Frequency table

Granted, I tend to work with raw data, not processed data as in the table, but the simple fact is that tables like this do come across my path from time to time.

How, then, can I calculate something like the mean age of the respondents listed in this table? I cannot simply calculate the mean of the age column, nor the mean of the counts column, as the following figure shows (I am using one of Excel 2013’s great new functions, FORMULATEXT, to display the formulas in A16:C16):

Figure 2    AVERAGE function gives totally wrong answer when applied to wrong values

But with a very simple array formula, I can “expand” the data in the table, and then calculate the mean of that (here, FORMULATEXT shows the formulas in C1:C2):

Figure 3    Simple array formula “expands” the frequency table and allows me to calculate the average from the expanded results

I should add, as a side note, that {=AVERAGE(A4:A14*B4:B14)} does not give me the desired results, in case you were wondering why my contrived formula for calculating the average was necessary.

Simple, but very effective. And that is why I love array formulas!

Share

Conditionally stopping code

One of the VBA programmer’s tool of debugging tricks is using the Stop statement in code to stop code execution at a certain point where they want to examine what is happening.

Of course, this is similar to setting a breakpoint (F9), but it is more permanent (especially useful if you are not finished today, and want to shut down so that you can continue again on a later date).

However, generally you will find that programming “tutorials” will discourage you from using this, and with good reason. The biggest problem is scattering your code with Stop statements that don’t come out in your debugging, but do interrupt your code when your users are running it.

Now I am not going to suddenly start recommending that you start prolifically using Stop statements, but I have found that in certain instances, I really do find it useful. But at the same time, I am really fearful of leaving a Stop statement into my code, and not deleting it before I release the AddIn to the public.

So today, I thought of a simple solution for myself that will not work in all contexts, but may help someone out there too. The reason that it works for me is that I generally have two versions of my AddIns. One is named xxx unprotected.xxxm (e.g., Word uTIlities unprotected.dotm). When I feel that the AddIn is ready to release to the public (it’s been more than a year since I last released an update to the Word uTIlities, but I have some big improvements coming), I make a copy without the unprotected suffix (e.g., Word uTIlities.dotm). Then I add a password to that, package it in an install file, and it’s good to go. That fact—that the version I am working on, and debugging, does not have a password, while the one my users are running does, provides me with a clue to setting up my Stop statements in my code that will work for me, but not interrupt my users.

So all I started doing was changing the Stop statements in my code as follows:

For Word:

If Not ThisDocument.HasPassword Then Stop

For Excel:

If Not ThisWorkbook.HasPassword Then Stop

This, at least, means that so long as the document has a password, the Stop statement will not interrupt the code. Yes, I will still do my best to delete my Stop statements when I am done with them, and if I should not delete those statements and I forget to give my AddIn a password, then it will still bomb out.

What if you don’t have a password? Well, you could create a global variable, but then you would have to remember to “deactivate” that variable before distributing.

So, for example, you would include this dim statement:

Dim bAllowStopping as Boolean

Then the code would just be:

If bAllowStopping then Stop

You can only activate stopping by setting:

bAllowStopping = True

But if you ship the AddIn with that line of code still there, then it will stop, and remember that, because the variable is global, it will stop anywhere the Stop statement is found.

Note that I chose something like bAllowStopping, which, when declared, has the value of False; and not something like bOverrideStopping, which would logically, want to be set to True to not stop—I would want to have to explicitly set it to True before it starts stopping my code. So, as long as I don’t actively set it to True, it won’t stop my code.

One last option, of course, is to tie the Stop statement to the username, e.g.:

If LCase(Application.UserName) = "joe bloggs" Then Stop

However, I consider this to be quite risky, as firstly, I don’t like shipping my application username with my code, necessarily, and secondly, I might, for who knows what reason, might choose to change my username, and thirdly, the odds of there being another user out there with the same username as my own (either through coincidence or being purposefully set) are not that remote.

For my part, I will stick to my first option of checking for the password, and still try to use Stop statements as little as possible, and also follow that up with deleting them when I am done with them.

Share

Manipulating text with Excel: Take II

Quite some time ago, I did a post in which I demonstrated some techniques for manipulating repetitive text blocks. However, I will confess, as my Excel-L friend Brian Canes also pointed out, that the example is somewhat contrived, as it would probably have taken less time to use the CONCATENATE function and just to click on all the cells. What I don’t like about doing something like that is the possibility of error—of clicking on one cell twice, or skipping a cell.

I want to go just a bit further with the idea of using Excel to manipulate text (also using a less contrived example), and show how I use Excel to create repetitive (or even partially repetitive) text blocks. Granted, people tend to think of Excel as a tool for working with numerical data (which, of course, it is), and granted, Excel really has some big problems working with rich text, but people tend to miss the fact that the tabular layout of a spreadsheet can still be a useful palette for working with text. For example (and before I get to my post), if I have to create something like an attendance list (a table with space for names, signatures, and perhaps some other information to be completed, but nothing else), then I prefer to do it in Excel. The page layout is a bit trickier, but I find I can do it quicker in Excel than, for example, by creating a table in Word.

Here, then, are four somewhat less contrived (or not!) examples of using Excel to manipulate text. They all relate to using excel to create programming code. The first one I used to crea VBA code, and the last three SAS code.

Example 1

Here is an example where I used Excel to write VBA code for the Word uTIlities. I wanted to be able to determine exactly what is selected, especially if an object is selected. I copied a table from the Microsoft Word help file (and the example below is from the Word 2010 help file), but the same list can be found for Word 2013 at https://msdn.microsoft.com/en-us/library/office/ff860759.aspx. The table consists of three columns, the Name, the value, and a description. I wanted to take that and use it as the base for my programming. So all I did was copy the table and paste it into Excel, and then added some extra columns to do all my “writing” for me. The process is explained below.

Figure 1    Word Help file text pasted into Excel and modified for further programming

If you look at this, the process was simple. My three original columns from the table were pasted into the worksheet columns B:D. I then added two columns between B:C. Then I typed “elseif selection.shaperange(1).type=” into A2 and filled it down. I typed “Then” into C2 and filled that down. Then I added one double quote to D2 and filled that down, and then merged those values with the aforementioned uTIlity of mine (note that the actual values are now, in Figure 2, in Column D and no longer in E). I then typed “WhatIsSelected = WhatIsSelected & ” in an” in F2 and filled that down, and again one double quote to H2 and filled that down. The double quote in D2 deserves an explanation. Because the single quote is Excel’s cue to treat a number as text, if I added that, and copied it across to the VBE, only the number would be pasted, not the single quote (which, as you will see in the code snippet below, I wanted to use for commenting out the number). But a simple search and replace could change that double quote to a single quote once it had been copied and pasted. One more search and replace would also allow me to break the Excel rows into two code rows each. And I just had to delete the very first else to make it the starting if. So, in a matter of a minute or two, I was able, from the help file, to produce the code below. Counting only the text from Excel, that’s 367 words, 2603 characters in fifty lines of code—you would have to be a very fast typist to beat that (and I am not a very fast typist, so it helped me all the more).

intTestForError = .ShapeRange(1).Type
If Err.Number = 0 Then
  '1
  If .ShapeRange(1).Type = msoAutoShape Then
      WhatIsSelected = WhatIsSelected  _
       & " in an AutoShape"
  '2
  ElseIf .ShapeRange(1).Type = msoCallout Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Callout"
  '20
  ElseIf .ShapeRange(1).Type = msoCanvas Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Canvas"
  '3
  ElseIf .ShapeRange(1).Type = msoChart Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Chart"
  '4
  ElseIf .ShapeRange(1).Type = msoComment Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Comment"
	'21
  ElseIf .ShapeRange(1).Type = msoDiagram Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Diagram"
	'7
  ElseIf .ShapeRange(1).Type = msoEmbeddedOLEObject Then
      WhatIsSelected = WhatIsSelected  _
       & " in an Embedded OLE object"
	'8
  ElseIf .ShapeRange(1).Type = msoFormControl Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Form control"
	'5
  ElseIf .ShapeRange(1).Type = msoFreeform Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Freeform"
	'6
  ElseIf .ShapeRange(1).Type = msoGroup Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Group"
	'24
  ElseIf .ShapeRange(1).Type = msoSmartArt Then
      WhatIsSelected = WhatIsSelected  _
       & " in a SmartArt graphic"
	'22
  ElseIf .ShapeRange(1).Type = msoInk Then
      WhatIsSelected = WhatIsSelected  _
       & " in an Ink"
	'23
  ElseIf .ShapeRange(1).Type = msoInkComment Then
      WhatIsSelected = WhatIsSelected  _
       & " in an Ink comment"
	'9
  ElseIf .ShapeRange(1).Type = msoLine Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Line"
	'10
  ElseIf .ShapeRange(1).Type = msoLinkedOLEObject Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Linked OLE object"
	'11
  ElseIf .ShapeRange(1).Type = msoLinkedPicture Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Linked picture"
	'16
  ElseIf .ShapeRange(1).Type = msoMedia Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Media"
	'12
  ElseIf .ShapeRange(1).Type = msoOLEControlObject Then
      WhatIsSelected = WhatIsSelected  _
       & " in an OLE control object"
	'13
  ElseIf .ShapeRange(1).Type = msoPicture Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Picture"
	'14
  ElseIf .ShapeRange(1).Type = msoPlaceholder Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Placeholder"
	'18
  ElseIf .ShapeRange(1).Type = msoScriptAnchor Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Script anchor"
	'-2
  ElseIf .ShapeRange(1).Type = msoShapeTypeMixed Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Mixed shape type"
	'19
  ElseIf .ShapeRange(1).Type = msoTable Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Table"
	'17
  ElseIf .ShapeRange(1).Type = msoTextBox Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Text box"
	'15
  ElseIf .ShapeRange(1).Type = msoTextEffect Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Text effect"
  End If
End If

 Example 2

The first SAS example concerns the scaling of values in a questionnaire for a paper we were writing. Essentially, we have thirty three items, each of which is divided into four sub-questions. The respondents had to indicate to what level they felt their mothers were involved in various aspects of their sport (A), to what level they wanted their mothers to be involved (B), and then again to what level they felt their fathers were involved in various aspects of their sport (C), to what level they wanted their fathers to be involved (D). For each parent, we had to calculate a discrepancy score—the degree to which the perceived behaviour differed from the desired behaviour (i.e., A-B for mothers, and C-D for fathers.

This could also, admittedly, be done in a different way. I must confess that my SAS macro skills are not what my Excel skills are, and even in Excel I’m probably still not at the true level of guru-like enlightenment. But presumably, this could be done with a SAS macro. However, the simple macro below doesn’t work because my incrementer variable I is used next to other characters, and so SAS doesn’t recognise it. Contrary to my own advice, I did not go and teach myself how to conflate the incrementer with the succeeding text into a new variable name, but rather thought to myself: “I’m under a tight time deadline, and I can do this in a minute or two in Excel.” Which is what I did.

So the first thing I did was enter this in Row 1:

Figure 2    Starter line of code in Excel

Next, copy all of that down to row 2 and change the value of B2, E2 and I2 to 2:

Figure 3    Second line of code, with 1 changed to 2

Now select it all, and use autofill to drag and fill it down to row 33.

Now copy all 33 rows, paste below, and in that, use search & replace to replace ‘Mom’ with ‘Dad.’

The last step can be done in one of two ways. If I copy all of that, and paste it into UltraEdit which I use for my SAS programming, each column’s values are added as tab-separated. So a simple search and replace in UltraEdit to replace tabs with nothing, gives me the final result I want:

B_1_Mom_Discrep=B_1A-B_1B;

However, I used one of my own Excel uTIlities (which I have still not released to the public!) to merge all that information into Column A (see the before and after views below). From there, I could just copy that into UltraEdit and I was good to go. Sixty six lines of code written in about two minutes from when I started typing in Excel, until I had the finished product in UltraEdit!

Before

After

Figure 4    Excel text before and after merging cell contents

Turns out, though, that a period is what joins the counter in a SAS macro to the text which comes after it, so I could just have written the macro like this (had I known this at the time):

%MACRO Discrep;
    %do i=1 %to 33;
        B_&i._Mom_Discrep=B_&i.A-B_&i.B;
    %end;
%MEND;

 Example 3

The third example, I will confess, is again a bit more contrived, but this is something I actually did, so it is worth mentioning. It also employs a technique I learned on Excel-G from the Excel guru, Bob Umlas, which, I think, is featured in his book This isn’t Excel, its Magic!

The scenario is this:

I was writing a SAS program to analyse a student’s data. The student found that some of the data points were incorrect (his error in the coding), and sent me an Excel file with the corrections. However, instead of taking the data file, and correcting it, he sent me a file containing only the corrections, and blanks for all the rest. The (de-identified) corrections look like this:

Figure 5    Data which has to be merged into SAS program

The file indicates the respondent number, as well as the items in which errors were found, and the correct values. Unfortunately, I could not simply read the data file into SAS and merge it with the existing data, because the layout was not ideal (SAS would read all the blank cells as missing values). I could certainly try to manipulate the layout and create a useable data set, and either import that from Excel, or read it into SAS as a data card. But that may have been about as much effort as the solution I chose:

The first thing I did was to delete all the empty columns, i.e., those columns to which no corrections applied. This could easily be done for a large dataset by adding a new top row, and using a COUNTA function in each column—those columns with a count of zero are empty and can be deleted (and even that process can be expedited by sorting horizontally). The result now looked like this:

Figure 6    Data with empty columns removed

Next, I needed labels for each row (this was optional, I could have written a more complex formula that pulled everything out of the top row, but I chose the simpler option for its speed). So here I used Bob Umlas’ trick. I added another column (optionally called “Order”). As before, I added 1 and 2 in the first two rows of that column (L2:L3):

Figure 7    Creating the order column

With autofill, that was quickly copied down all 33 rows. That was then copied again and pasted directly below itself, so that I had two number sequences across the rows, from 1 to 33, right below each other. Then I copied the labels from row 1 and pasted them below the data (i.e., next to the second 1 in my sequence, which was in row 35):

Figure 8    New labels added

Autofill copied that down all the remaining rows (up to row 67) and I quickly sorted on the order and No columns, to give me this:

Figure 5    Labels positioned above each row

Note that, in terms of time, this is probably about one minute’s worth of work.

Next, I added a new column A. Now I was ready to create my lines of SAS code.

The interesting thing is that I even wrote my cell formulas with the method I am describing in this post. I know it will seem contrived, but essentially, I wrote these formulas (note the cells in which I wrote them) in a blank worksheet (some cells contain text, other contain formulas, which I have highlighted, but note that B2 starts with an unseen single quote, so it is not counted as a formula). Note also that this is much less typing that it seems, as C2 & F3 contain exactly the same formula, which is only modified slightly for both B3 & D3 respectively:

Figure 9    Formulas to write an Excel formula

The result is shown below:

Figure 10    Results from formulas to write an Excel formula

Then I again use autofill to copy them down to row 12 (L is the 12th letter) and just add one snippet in B13:

Figure 11    Final text for Excel formula

Now comes the fun bit. I copy that (B2:G13), paste it into my handy text editor UltraEdit, copy it again, and paste it (F2, Ctrl+V) into cell A1 of my worksheet containing the data. The result is this:

Figure 12    Formula in first cell

Now I select A1:A2, and fill down (A2 so that the formula is filled down to every second row):

Figure 13    Final code

I can copy that, and paste it directly into my SAS program, and it will make all the corrections I want.

if no eq 8 then do;B1=3;B2=2;end;
if no eq 10 then do;B1=3;B2=3;B3=3;B6=3;B7=3;B16=3;B17=1;B18=2;B22=3;end;
if no eq 11 then do;B1=3;B2=3;B6=3;B7=3;B16=3;B18=2;B22=3;end;
if no eq 12 then do;B1=3;B2=3;B3=2;B6=3;B7=2;B16=3;B17=1;B18=2;B22=3;end;
if no eq 13 then do;B1=3;B2=3;B3=2;B6=3;B16=3;B17=2;B18=2;B22=3;end;
if no eq 14 then do;B1=3;B2=3;B3=2;B6=3;B16=3;B17=1;B18=3;B22=2;end;
if no eq 15 then do;B1=3;B2=3;B3=3;B6=3;B7=1;B16=3;B17=1;B18=2;B22=3;end;
if no eq 16 then do;B1=1;B2=3;B3=3;B6=3;B7=1;B16=3;B17=3;B18=3;end;
if no eq 17 then do;B1=3;B2=2;B3=2;B6=3;B7=3;B16=3;B17=3;B18=3;B22=3;end;
if no eq 18 then do;B1=3;B2=3;B3=3;B6=3;B16=3;B17=3;B18=2;B22=3;end;
if no eq 19 then do;B1=3;B2=3;B3=3;B6=3;B7=2;B16=3;B17=1;end;
if no eq 20 then do;B1=3;B2=3;B3=3;B6=2;B16=3;B17=1;B18=2;B22=3;end;
if no eq 21 then do;B1=3;B2=3;B3=2;B6=3;B7=3;B16=3;B17=3;B18=3;B22=3;end;
if no eq 22 then do;B1=3;B2=3;B3=3;B6=3;B7=2;end;
if no eq 26 then do;B16=3;end;
if no eq 29 then do;B1=3;B14=1;B16=3;B17=3;end;
if no eq 33 then do;B1=3;end;
if no eq 36 then do;B16=3;end;
if no eq 38 then do;B22=3;end;
if no eq 43 then do;B16=3;end;
if no eq 45 then do;B16=3;end;
if no eq 48 then do;B22=2;end;
if no eq 57 then do;B16=3;end;
if no eq 59 then do;B16=3;end;
if no eq 63 then do;B17=1;end;
if no eq 69 then do;B6=3;end;
if no eq 72 then do;B6=3;B16=3;B18=3;end;
if no eq 73 then do;B6=3;end;
if no eq 83 then do;B16=3;end;
if no eq 84 then do;B6=3;B18=1;end;
if no eq 89 then do;B3=2;end;
if no eq 99 then do;B17=1;end;
if no eq 111 then do;B18=2;end;

The process may be crude, but is surprisingly effective and efficient: This kind of programming takes just a minute or two to complete.

Example 4

This example is much the same as before: Data corrections (yes, it seems as if I spend more time correcting data than analysing data, but let’s just say that proper preparation leads to efficient execution).

The basic layout is as shown below–almost 70 rows of corrections that need to be made. The trick here is that it is not always the same variable that is to be corrected. The variables names are listed in B1:J1, and if a column has a value, that variable must be set to that value for that respondent (as listed in Column A).

Excel Text example 4b

Figure 14    Listed corrections

So I use this formula, and the results are shown in Column K in the final figure.

="if Number eq " & A2 & " and " & $B$1 & " eq " & B2 & " and " &OFFSET($B$1,0,MATCH(MAX(C2:H2),C2:H2,0),1,1) & " eq " & MAX(C2:H2)& " then do; " & $B$1 & "="&I2&";" &OFFSET($B$1,0,MATCH(MAX(C2:H2),C2:H2,0),1,1) & "="&J2&"; end;"

Excel Text example 4a

Figure 15    Formula result showing code which can be copied to SAS

Essentially, my formula finds which cells have values, and then pulls the column headings in for those cells, setting them equal to the value found in the cell. Using this method, I could get Excel to create 6557 characters of code for me by typing 228 characters (the length of the formula) and doing a little bit of layout.

Conclusion

In closing, this is definitely not a conventional usage of Excel, but in terms of getting “repetitive” (actually, “patterned” is a better term) text created, it is hard to beat for speed if you know Excel well. I can create hundreds of lines of code, if need be, in a fraction of the time it would take to type it.

Share