-------------------------------------------------------------------
THE OFFICE LETTER
STANDARD EDITION
Tips, Tricks, Tools, and Techniques for Microsoft Office
Volume 4, Number 22 November 15, 2004
-------------------------------------------------------------------
IN THIS ISSUE
1) Reader Tip: Advanced Find in Outlook
2) Review: SecuriKey
3) Reader Feedback: Finding Duplicates in Excel
4) New Updates from Microsoft
5) Free Windows Tips, Technology News
Premium Edition Extra:
Word: Splitting and Rejoining Tables
-------------------------------------------------------------------
1) READER TIP: ADVANCED FIND IN OUTLOOK
-------------------------------------------------------------------
Reader Malcolm Morris wrote with this Outlook tip:
Summary: In Contacts, Advanced Find using "CategoryA, CategoryB"
(with a comma) returns different results from "CategoryA CategoryB"
(no comma)
I have categories for Suppliers, Hardware, and Software. To select
contacts by category, I go to Tools/Find/Advanced Find/More Choices
and there is a box to enter categories.
If you use the mouse and click on the Categories button to select
two categories, Outlook puts them in the text box like this:
Suppliers, Software
and when you click Find Now it returns any contact with *either* of
those categories, or both of them (a logical OR).
But, as I found by chance, if you remove the comma like this
Suppliers Software
Outlook changes to a logical AND and only returns contacts which
have *both* categories. This means I can select my Software
suppliers separately from Hardware suppliers, and I'm a happy
camper.
As far as I can tell, this is not documented anywhere. If it is,
please let me know! It has saved me vast amounts of time exporting
contacts to Excel and sorting them there.
And thanks for the newsletter!
- - -
Do you have a tip you'd like to share? Write us at
[EMAIL PROTECTED]
-------------------------------------------------------------------
SPONSOR: GET THE WHOLE ENCHILADA!
-------------------------------------------------------------------
The new Camtasia Studio Add-in records all the richness of your
PowerPoint presentation. Preserve all animations, transitions, and
sound effects -- even your live audio and on-the-fly annotations!
Share your recorded presentation in the format that best suits your
audience, including Web site, CD, Flash or streaming video.
To find out more and download a free trial, visit us at:
http://www.officeletter.com/red/camtasia.html
-------------------------------------------------------------------
2) REVIEW: SECURIKEY
-------------------------------------------------------------------
Wouldn't it be nice to be able to secure your own computer with
something as simple as a tiny USB device (called a token) that's
smaller than those "USB disk drives" and doesn't slow down your
boot-up process? Griffin Technolgies' SecuriKey ($129) promises
(and delivers) this, and more.
Why not just use a password? They can be broken, for one, and most
people use a password that's too short or easy to remember (and
hence break). SecuriKey uses what's called two-factor
authentication -- this combines the token itself and the password
you enter during installation.
Installation is relatively simple: you install the software, then
insert the token into each existing USB device (one by one) of your
system. You can define what happens when the token is removed
during system operation (it can wait until you re-insert the token,
log you off, or shut down the system).
Sure enough, after rebooting the system, the tiny gizmo works as
advertised. I can't get into the system without the token, and if
it's removed while I'm working, it logs me off (the option I chose
during setup). The kit comes with a spare token, and if lost or
stolen, the company can replace it (assuming you've registered --
the registration includes passing a special key to Griffin to
enable this).
Because I had set up my system to automatically log into a single,
default user and never ask for a password, the SecuriKey's
installation caused some changes to my boot-up sequence. After
installation, I now have to pick my userid ("Owner") and enter a
password. As the vendor explained, that's the trade-off you have
to make to get the security this device provides. As the vendor
also properly pointed out, for many of its customers that operate a
network environment, they already are getting the logon process
before using SecuriKey. The good news is that the device remembers
the last user selected, so you need only enter the password each
time you boot -- which is part of what security's all about.
A bit more disconcerting, however, is that the installation program
changed screens to use the older NT-style start-up and shut-down
messages, which may be annoying for some users who prefer the newer
Windows XP look.
I'm not crazy about the mediocre documentation (the installation
placed a broken link to the user guide on my Start menu), but not
much documentation is needed -- you pretty much just plug it in,
run the setup once, and you're in business.
For more information, visit http://www.securikey.com/.
-- James E. Powell
-----------------------------------------------------------
SPONSOR: WORDTOYS SUPERCHARGES WORD FOR FREE
-----------------------------------------------------------
WordToys gives you dozens of utilities that make Word easier
to use and more fun to work with. Create unique graphical
Favorite Fonts, Favorite Symbols, and Favorite Bullets menus.
Insert any accented character. Select the parts your mouse
can't reach. Backup while you save. Copy/paste any format.
Print to any printer. Lots more goodies for Word 2000, XP,
and 2003. Best of all: WordToys is FREE. No time limits.
Download now from
http://www.wordtoys.com
-------------------------------------------------------------------
3) READER FEEDBACK: FINDING DUPLICATES IN EXCEL
-------------------------------------------------------------------
Our story last week on using conditional formatting to find
duplicate e-mail addresses in a worksheet with a single column drew
lots of mail.
Among the messages were these observations from Bob Martell:
Your conditional formatting isn't a bad way to go.
I've long used a formula that looks up at the previous cell,
though. It requires inserting or adding a column for the
formula, however.
If A1 is the heading and A2 is the first data row (record) then
go to B2 and enter the formula "=if(a2=a1,"DUPE","")
Copy this formula down to the end of the data in the same column.
Unfortunately, one cannot search for the word DUPE in the B
column, since it is a formula, and every cell has it!
Copying and pasting as values in the column doesn't help either.
The alleged "" (blank) isn't, so you can't CTRL-DOWN to find the
next un-blank cell.
You can however search for DUPE. In and out of Find dialog...
That is WAY too tedious! It's ugly, especially on a sheet with
3400 rows!
I'll be REALLY interested in a better solution! I don't want to
get into importing to Access and then export again, just to find
duplicates!
- - -
Reader Rawson Groves Hobart had a similar suggestion:
An equally inelegant solution to finding duplicates which I find
very useful is to create a blank column following the column of
data which I am searching for duplicates. Let's say I am
searching column A for duplicates.
I label my blank column B "Match", then copy the formula
IF(A2=A1,1,"") down that column. This labels the matches. Then
I copy column B and paste special/values over it. That allows me
to sort my worksheet so that all the duplicates appear together,
allowing me to easily delete or modify them.
- - -
We tested the suggestions from Bob and Rawson.
As Bob said, I couldn't get the Edit/Find command to find just the
"DUPE" cells, since "DUPE" is part of the formula in every cell in
the column. However, our experience was quite different from what
Bob reported for everything else.
While Bob says that copying and pasting as values doesn't help, I
believe it does. As Rawson points out, you can copy the second
column values and paste (Edit/Paste Special/Values) over the second
column values (essentially pasting the results of the formulas over
the formulas themselves). After I did that, I could use Edit/Find
to locate "DUPE" in the second column.
(OK, I'll confess -- I'm a bit more timid when it comes to what
Rawson suggests. I put the If/Dupe formula in a cell in the second
column, copied the formula all the way down to the bottom of the
data, and copied the values from the second column and
paste/special/values into a (new) third column. Once I saw that
everything's correct in this third column, I deleted the second
column. Call me chicken, but I just like to take things in
stages.)
- - -
TOL subscriber Johannes Postma suggests a similar solution -- his
formula substitutes "duplicate" for "DUPE" -- using the same IF
statement and copy/paste special suggestion, adding, "Re-sort the
worksheet by column B and delete the rows with the word duplicate
in column B. Next, delete column B to arrive at a cleaned up
worksheet without duplicates."
Assuming you perform this paste/special over the second column
values, you could, indeed, sort the entire range by the second
column, putting all the duplicates together, then deleting all the
duplicates. Johannes wasn't the only reader to suggest deleting
all the duplicates in this way. Of course, be careful if you do
this. When you sort by the second column, you'll put all the
duplicates together but separate the duplicate data from the
original data. If you only have one column of data (the e-mail
address, if our example), then this is fine.
But what if there are additional columns? By separating the
duplicate row from the original, you can't compare to see which row
(the duplicate or the original) is actually the row you want to
delete. You may not be able to assume that the duplicate row is
the one to delete -- since the duplicate row may actually contain
newer data than the original (it might have a later transaction
date in the fifth column of your data, for example).
- - -
TOL subscriber Tom Coffinger wasn't afraid of Access as the way to
go:
While I like your solution, personally, I would link the
spreadsheet into an access database, then use the new button on
the query tab to create a "Find Duplicates" query. There is no
real knowledge of Access needed to run this wizard.
We'll share Tom's instructions on how to do this next week.
- - -
Loren Smith of Australia wrote with two completely different
suggestions.
I'll probably be one of several dozen people who forward you a
simpler solution to the "Excel: Finding Duplicates with
Conditional Formatting" from Office Letter of 8th November.
Technique #1:
1. We will assume that your e-mail address list is in Column A
with a Heading in Cell A1, and data starting in Cell A2.
2. With the cursor in Cell A2, select Format/Conditional
Formatting.
3. Select "Formula Is" in the drop down box and enter the
following formula to its right:
=COUNTIF(A:A,A2)>1
4. Select the format you want when the condition is true (e.g.,
bold red text) and click OK.
5. Now copy the formula from Cell A2 into the remaining data
cells, either by using the format painter or copy/paste
values/formats.
If you have no other data in the column in question, then you
could simply apply the format to the entire column. This would
ensure duplicates will continue to be accurately highlighted if
your list gets shorter or longer.
The formula works by counting the number of times the content of
the current cell appears in the list and then if the count is
greater than 1 (i.e., duplicates exist) the conditional format
(highlighting) is applied. This formula works when the cell
contents exist more than twice in the list and does not require
the list to be sorted.
Technique #2:
An alternate approach I can think of offhand is the use of a
pivot table. It may be more useful in some circumstances. Once
again, the data does not have to be sorted and works for
occurrences greater than 2.
1. Again, assume that your e-mail address list is in Column A
with a Heading in Cell A1 and data starting in Cell A2.
2. Highlight the column containing the data (including the
heading) and select Data/Pivot Table.
3. A wizard will ask where the data is to be analyzed. Accept the
default "Microsoft Excel list" then click "Next"
4. You will then be asked where the data to be used is. This
should be already completed (because of point 2 above) -- if not
select the cells including the header, then click "Next"
5. Select where to put the pivot table. "New Worksheet" is the
simplest while you are getting used to pivot tables, then click
"Finish".
6. You will be shown a new worksheet with a table outline. Click
within the table if it doesn't already display "Drop ... Here"
instructions on it.
7. Now drag the column heading from the "Pivot Table Field List"
onto the area that indicates "Drop Row Fields Here" and then
again onto the area indicating "Drop Data Fields Here".
8. You should now have a table showing all the unique e-mail
addresses along with a count of the number of times each appears
in the list.
If your data list changes simply right click on the pivot table
and select "Refresh" to update it.
Hope these are useful or at least broaden someone's imagination
for solving other problems.
[Editor's note: see the Pivot Table we created from last week's e-
mail list in our online edition this week.]
- - -
We have a short comment to make about Loren's first technique.
Loren wasn't the only reader to come up with this idea. While the
solution works, I would only recommend it for very short lists.
Here's why.
In our original conditional formatting solution, each cell looks at
the cell one row before (above) and one row after (below) in order
to determine if special formatting (red, bold) should be applied.
(Yes, we're assuming the list has been sorted.) In Loren's
solution, each cell has to compare its own value with ALL other
cells in the range. If your list contains 100 rows, that's not
much overhead. If, however, you have 10,000 or 20,000 e-mail
addresses (rows), you're going to tax Excel needlessly. It's much
quicker to sort the data and ask conditional formatting logic to
compare two rows for each cell than compare each of 20,000 values
to 20,000 values in the range.
-------------------------------------------------------------------
SPONSOR: EASY-TO-USE WEB HOSTING FROM $9.95
-------------------------------------------------------------------
All plans include 24/7 technical support, unlimited e-mail, state
of the art web site builder, control panel admin, FrontPage and ASP
support. Domain names just $12.95. No contracts required, and
we'll set you up for FREE in under 20 minutes. Sign up today at:
http://www.officeletter.com/thehostgroup.html
-------------------------------------------------------------------
4) NEW OFFICE UPDATES FROM MICROSOFT
-------------------------------------------------------------------
Microsoft announced new office updates last week. Included is an
update to the Office XP Alternative User Input framework used to
support advanced text features, such as speech, handwriting, and East
Asian keyboard input services. The update fixes a fairly obscure problem
that occurs with the previous update (from March) on a Windows 2000
system. (Knowledge Base article 832668)
Of more interest is an updated junk e-mail filter for Outlook 2003. Check
out Knowledge Base article 873362.
If you use Office 2003 with French proofing tools, Knowledge Base article
873381 addresses the French grammar checker.
-------------------------------------------------------------------
5) FREE WINDOWS TIPS, TECHNOLOGY NEWS
-------------------------------------------------------------------
Want more tips and tricks in your inbox? Keep on top of the latest
technology and Windows news -- plus tips and tricks to get more done with
Windows and Windows apps -- free every month. The AMF E-zine is available
here:
http://www.amfsoftware.com/subscribe.html
-------------------------------------------------------------------
6) PREMIUM EDITION EXTRA THIS WEEK
-------------------------------------------------------------------
This week Premium Edition subscribers are reading about how to
split (and reunite) tables in Word.
Why not subscribe to the Premium Edition today for just $12/year?
HTML format, extra content, access to all back issues, a fast
search engine, a printer-friendly format option, and no
advertising! Visit:
http://www.officeletter.com/subscribe.html
-------------------------------------------------------------------
CONTACT POINTS
-------------------------------------------------------------------
SUBSCRIPTION CHANGES
Your subscription record shows the following:
Email address: [EMAIL PROTECTED]
First name: subscriber at [EMAIL PROTECTED]
Last name:
Full name:
If you would like to edit the above data, click here:
http://www.office-letter.com/cgi-bin/mmp/[EMAIL PROTECTED]
To unsubscribe, visit:
http://www.officeletter.com/sub/substdremove.html
and enter your e-mail address.
SHARE YOUR TIPS: Send them to [EMAIL PROTECTED] and include
your permission to acknowledge you by name.
FEEDBACK: Send your suggestions and comments about The Office
Letter to [EMAIL PROTECTED]
RECOMMEND THE OFFICE LETTER TO YOUR FRIENDS AND COLLEAGUES
Point them to http://www.officeletter.com/current.html
MORE FREE TECH E-NEWSLETTERS: http://www.techletters.com
----------- THE OFFICE LETTER ----- www.officeletter.com ----------
Tips and Tricks for Microsoft Office - Published Weekly
Copyright 2004 Masterware, Inc. All rights reserved
Now In Our Fourth Year - ISSN: 1543-5768
Editor in Chief: James E. Powell
Contributing Editors: Jim Boyce (www.boyce.us)
Dick Archer (www.diseno.com)
