-------------------------------------------------------------------
THE OFFICE LETTER
STANDARD EDITION
Tips, Tricks, Tools, and Techniques for Microsoft Office
Volume 4, Number 23 November 22, 2004
-------------------------------------------------------------------
IN THIS ISSUE
1) Reader Feedback: Double-Spacing in Word
2) Excel: Finding Duplicates
3) Reader Feedback: Deleting Duplicates
4) Access: Finding Duplicates in Excel Data
Premium Edition Extras:
Word: Double-Duty Table-Split Shortcut
Outlook: Correcting or Changing Categories in Outlook
-------------------------------------------------------------------
1) READER FEEDBACK: DOUBLE-SPACING IN WORD
-------------------------------------------------------------------
TOL subscriber Chuck Tucker wrote with these comments about our
recent tips on double-spacing text in Word.
- - -
Powell's instructions on double spacing certainly works OK, but it
seems to me that it would double the spacing of everything in the
document including all headings. This may result in very large
spaces if, for example, the headings already have spaces added
before and/or after.
I would suggest changing the style of the body text (or Normal if
that's what the document uses) to double spacing -- that would
leave the titles alone.
Of course, one can always select certain text and change it, but if
you're working with a large document that incorporates, say, many
headings and similar non-body text items, then changing the style
is the only way to go. I recently helped with formatting a Ph.D.
thesis of more than 200 pages that was originally in single-spaced
text. However, the university's standards called for double
spacing. Setting the body text to double-spaced accomplished the
change in one quick operation.
I really appreciate all the suggestions and tips that you publish.
Best regards,
Chuck Taylor
- - -
Chuck's right -- for large areas, it's best to use a style that
includes the double-spacing property. For short sections of text,
selecting the text and using one of the keyboard shortcuts we
provided is, we believe, more efficient.
-------------------------------------------------------------------
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) EXCEL: FINDING DUPLICATES
-------------------------------------------------------------------
TOL subscriber Glenn noted that while our solutions over the last
two weeks for finding duplicates using conditional formatting was
fine for a single column of data, things get more complicated if
you're trying to find duplicates using two fields. For example,
assume you have two columns of data -- Column A has the last name
of a customer and Column B has the customer's first name. You have
a match only if the first AND last name of a customer in a row
matches the same two fields in another row.
Here's how Glenn suggests handling this situation:
- - -
Okay, I'll add one more variant of the techniques already reported.
Mine assumes you're dealing with a humongous list of names and
addresses, which is what I do on a monthly basis.
All the steps as already described apply to this one, but with a
twist. Here are my assumptions:
-- Row 1, starting in Column A, contains all your field names.
-- Row 2, starting in Column A, contains all your data across each
field and extends vertically to, let's say, row 4,000.
-- You're trying to find duplicate names in the list.
Here are my steps:
1. Insert three columns, starting after Column B.
2. Label Column C, D, & E using: LNDup, FNDup, Match?,
respectively. [Editor's note: the columns indicate Last Name
duplicate, First Name duplicate, and a complete match -- that is
both first and last names match.]
3. In Column C, Row 2, enter the formula "=if(A2=A3,-1,0)" and copy
this formula in Column C to the end of your data.
4. In Column D, Row 2, enter the formula "=if(B2=B3,-1,0)" and copy
this formula in Column C to the end of your data.
5. In Column E, Row 2, enter the formula "=if(sum(C2:D2)=-2,-1,0)
and copy this formula in Column E to the end of your data.
6. Now highlight C2:EX (where X is the row at the end of your data)
and copy the selected cells.
7. Now Edit/Paste Special and paste Values only.
8. Optional: Delete Columns C and D, so that Column E remains and
contains either -1 or 0.
9. Now Data/Sort your data on Column C (Match?), Column A
(LastName), and Column B (FirstName) in ascending order.
All of this will get you a list of duplicate names, which includes
the FirstName all at the top of the list. The conditional
formatting works too, but it's tougher to include the FirstName in
the formula. Hope this is helpful!
-------------------------------------------------------------------
SPONSOR: SHARE OUTLOOK CALENDARS WITHOUT EXCHANGE SERVER
-------------------------------------------------------------------
OfficeCalendar is a simple software application that lets you share
Outlook calendar and contacts with your colleagues and create group
calendar views for group scheduling. OfficeCalendar is easy to
install and use, affordable, and highly effective. Best of all, if
you already know how to use Microsoft Outlook, there's nothing new
to learn. Sharing Outlook calendars doesn't get any easier.
Visit our Web site at http://www.officecalendar.com/offlet.asp
to download a free trial version.
-------------------------------------------------------------------
3) READER FEEDBACK: DELETING DUPLICATES
-------------------------------------------------------------------
In our discussion of duplicates, we noted that a reader used a
simple formula
=if(a2=a1,"DUPE","")
to place the word "Dupe" in a column where a duplicate was found,
then copying the cells in this new column and pasting just the
values into the column. TOL reader Jon Peltier has a better
suggestion.
- - -
Here's a quick and dirty way to clear all the duplicates indicated
by your procedure.
Insert a row at the top, and put labels above each column, say,
Values and Dupes. From the Data menu, select Filter > AutoFilter.
This lets you select rows based on selecting values in dropdowns in
each top cell.
In the Dupes dropdown, select DUPES (the "" are treated here as
blanks, by the way), so only the rows with DUPE in column B, plus
the header row, are visible. Select any cell in the filtered list,
press CTRL + SHIFT + * to select the whole contiguous range, right
click in the highlighted range, and select Delete Row.
The AutoFilter and header row disappear, as do the rows with DUPE.
This is quicker than any of the copy-paste special suggestions.
- - -
We also complained that by using the IF formula above, Excel didn't
let us use the Edit/Find command to look for cells containing DUPE.
Since the formula was in each cell in the second column, the
Edit/Find command found DUPE in every cell in that column.
TOL subscriber Art Elwell offered this solution:
You can use your method and search for "DUPE". In your FIND dialog
box, expand the "Options" button and then:
1. In the "Search" drop-down, select "By Columns"
2. In the "Look In" drop-down, select "Values"
Each "Find Next" will put you in the next "DUPE" cell.
Selecting "Find All" will give you a list of all of the cells
containing your value.
-----------------------------------------------------------
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
-------------------------------------------------------------------
4) ACCESS: FINDING DUPLICATES IN EXCEL DATA
-------------------------------------------------------------------
Last week we provided several techniques for finding duplicate
values in a column in Excel. TOL reader Tom Coffinger suggested
that the data be imported into Access. Before you gasp, thinking
that's too hard, Tom's sketched out the step-by-step instructions
to achieve exactly this. As you'll see, it's not that difficult.
Note that Tom's instructions are generic -- the exact wording of
the options described may differ based on the version of Access
you're using.
Here are Tom's instructions:
- - -
Excel offers limited ability to handle cleaning up duplicate data,
especially in large spreadsheets. However, MS Office
interconnectivity provides some pretty slick solutions once you get
comfortable with which applications do what functions. Here is
what I do.
In Excel, I add a column to my worksheet and name it "DeleteMe". I
close the spreadsheet and switch to MS Access and create a
temporary database -- I click File/New/Blank Database (this will
vary slightly based on what version of MS Access you are using).
I name the new database and place it where I desire. Next, I click
on File/Get/External Data/Link Data. Be sure to choose link, not
import. When you link to the data, changes you make in Access will
also be made in Excel. You are, in fact, using the same data.
When you import, you have created a new, separate copy of the data.
Once you click on link, MS Access will open the Link Dialogue Box,
which looks identical to the Open Dialogue Box. Change the Files
of Type box at the bottom to Excel (.xls) files. Browse to the
file. Once you select the spreadsheet, the Link Spreadsheet Wizard
opens. Click Finish. You will receive a message that your file
was successfully linked. The hard part is now done.
Still in MS Access, click on the Query tab. Click on New/Find
Duplicates/Duplicates Query Wizard and click OK. Select your
table, then click next. Highlight the fields on the left that will
define the duplicate records. [Editor's note: in our sample from
last week, there was only a single column -- which contained the e-
mail address. This is the field you'd select.] Move the fields
that define your duplicates to the right pane by highlighting them,
and clicking on the right facing arrow ( > ).
Choose enough fields to ensure your duplicates are duplicates, but
not so many fields that you have no duplicates. For example, if
your data consists of a list of customers, you would include last
name and first name fields, but not a field such as date entered
into system. Once you have selected the fields that define
duplicates for your data, click Next.
Now you will add all the fields that will help you decide if
duplicates are truly duplicates, or just two different John Smiths.
I recommend using the add all ( >> ) to include the rest of the
fields. Make sure you include the "Delete Me" field in this
selection. Then click Next. Name it whatever you like and click
Finish.
You now have a screen showing you all the duplicates based on the
logic you defined. At this point, the new column DELETEME comes
into play. In each record you decide to actually remove, put an X
in this DELETEME column. (See illustration in our online edition
this week.) Once you have selected all the records you feel should
be removed, close this screen.
Due to some limitations of the software, you will not do the actual
delete from MS Access. Instead, return to Excel and open the
spreadsheet. You will now see that the appropriate records have an
X in the DELETEME column. Sort the data on that column using a
descending sort, highlight those rows with the X's, and delete the
rows!
Here's a variation on this procedure. If you will need to clean
this data again in the future, do not get rid of this database.
Since it is linked to the spreadsheet, you can return later and re-
run the Find Duplicates query simply by double clicking on it. You
will now be presented with the current duplicates in the
spreadsheet, and can simply mark them with the X and then delete
them as before.
- - -
Do you have a tip you'd like to share with Office Letter readers?
Write us at [EMAIL PROTECTED]
-------------------------------------------------------------------
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
-------------------------------------------------------------------
SPONSOR: GIVE US 10 MINUTES, YOU'LL SAVE ONE HOUR ... GUARANTEED!
-------------------------------------------------------------------
AMF Daily Planner and Personal Information Manager Software
organizes your tasks, orders your address book and contacts,
manages due dates, and keeps you prepared for any event!
- Organize and prioritize your projects, errands, and more
- Easily share your data with other users over any network
- Keep in touch with those who mean the most to you
- Overdue items are highlighted on your to-do list
- Never forget a birthday, anniversary, or special occasion again
Get it now: http://www.amfsoftware.com/affilpiml.pl?affil+10057
-------------------------------------------------------------------
6) PREMIUM EDITION EXTRAS THIS WEEK
-------------------------------------------------------------------
This week Premium Edition subscribers are reading about a special
shortcut that can do double duty with Word tables, and how to
correct or change categories in Outlook.
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)
