Contextures News

List Duplicate Names in Excel

Mar 8, 2016

See how to get duplicate names, shortcuts, and more, in this week's Excel news. If there are topics that you'd like to see covered in future newsletters, please let me know.

 -  Debra -  ddalgleish @ contextures.com
     Visit my Excel website for many more tutorials and videos.

Get Duplicate Names

If you have two lists of names, how could you create a new list, with the duplicates -- names that appear in both lists? Someone asked me that last week, and I recommended an Advanced Filter.

It has a built-in option to send the results to a different worksheet, and you can set up as many criteria as you need. There is an example on my website, in the Filter to Match Items in a List section.

If you're not familiar with Advanced Filters, this page shows how to get started with them. Do you use Advanced Filters, or do you find them too complicated?

Pivot Table Shortcuts

Do you use keyboard shortcuts most of the time, or are you a mouse person? There are a few keyboard shortcuts that I use all the time, like Ctrl + ; to enter the date.

If you work with pivot tables, and like shortcuts, I've put together a reference list for you. For example, you can select a pivot field or pivot item, and press Ctrl + - to hide that field or item

Your Feedback

Thanks for sending your comments and questions each week, after you read my newsletter. I love to hear from you!

Last week, I wrote about the EOMONTH function (End of Month), and Ron P reminded me that years that are divisible by 100 are not Leap Years, unless they're also divisible by 400. So, to get a list of Leap Year days, you could type 2/29/1996 in cell B1. Then put the following formula in cell B2, then copy it down as far as you'd like. Thanks Ron!

=EOMONTH(B1,IF(AND(MOD(YEAR(B1)+4,100)=0,MOD(YEAR(B1)+4,400)>0),96,48))

Excel Articles

Here are a couple of Excel articles I read recently, that you might find useful.

Excel Basics -- Ian Green's article is written for auditors, but the difference between absolute and relative references is important for all Excel users to understand. (Level - Basic)

Business Analytics -- If you missed the recent PASS Business Analytics webinars (free), there's good news. The recordings are online now, so you can watch when you have some free time. (Level - Intermediate/Advanced)

More Excel Articles -- Find links to more articles in the latest Excel Weekly Roundup on my Contextures blog. And for a bit of humour, read my weekly collection of Excel tweets.

Run, Forrest, Run

Several of my Excel friends are in Australia this week, leading sessions at the Excel Summit South. From all reports, they're having a great time, and enjoying the summer weather. I've never been that far south, but have enjoyed several winter vacations in Hilton Head, South Carolina. Nearby is the lovely town of Beaufort, where several movies were shot, including The Big Chill and Forrest Gump. Forrest ran across the bridge that you can see in my picture below -- they pretended that he was crossing the Mississippi River.

NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy this link and paste it into your browser:

www.contextures.com/newsletter/excelnews2016/20160308ctx.html

Recommended Excel Products

• Macros for Beginners: Allen Wyatt, a long-time Excel trainer and publisher, is offering a macro course for absolute beginners. It's online video training, designed for absolute beginners. In this course, you'll focus on the basics, and learn how to use macros to get your Excel work done faster and more efficiently. Allen covers each topic slowly and carefully, to help you understand the key steps. Registration is only open for a couple of weeks, and there is a 20% discount if you register by this Thursday, March 10th.
  NOTE: This training is not for you if you're looking for advanced skills, or if you want to learn at a rapid pace.

• Excel Chart Tools: If you build complex charts, save time with Jon Peltier's Excel Charting add-in

• See more recommended Excel products

________________________

Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.