Contextures

Contextures News 20180417

Excel Compare Cells Challenge

April 17, 2018

Quick print, compare cells, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!

Quick Print Tip

When you're working with a named Excel table, here's a quick way to print just the table, without all the other stuff that's on the worksheet.

  • Select any cell in the table
  • Click the File tab, and then click Print
  • In the first drop down list under Settings, choose Print Selected Table
  • Then, click the Print command at the top

If you do this frequently, add the command to your Quick Access Toolbar. In the "All Commands" list, look for "Print List".

Print selected table

Compare Cells

In this Compare Cells challenge, we have a list with addresses in columns A and B. What formula would you use, to check how many characters match, starting from the left. For example, only the first character matches in row 5. After that, they're different.

To try the challenge, download the sample file, and to see a solution, look below the screen shot.

Compare address cells

This challenge came from UniMord (thanks!), and here's how his solution works:

  • First, the formula in C2 calculates the length of the first address: =LEN(A2)
  • Then, the formula in cell D2 calculates how many characters at the left are a match:
    • =SUMPRODUCT(--(LEFT(A2, ROW(INDIRECT("A1:A" & C2)))
      =LEFT(B2, ROW(INDIRECT("A1:A" &C2)))))
  • Finally, the formula in E2 calculates the percentage: =D2/C2

In the SUMPRODUCT formula:

  • INDIRECT function creates a reference to cells A1:A9, based on the length in cell C2.
  • ROW function returns the row number for each row in that range (screen shot below)
  • LEFT functions return the first X characters in A2 and B2, for each of those numbers
  • Equal sign compares the results, and "--" returns 1 or 0 for each result
  • SUMPRODUCT calculates the sum of those 1s and 0s, to give the match length.

ROW function gets row numbers

For more details on this formula (with screen shots), and other formulas to compare cells, see my Compare Cells blog post.

Excel Articles

Here are a couple of Excel articles that you might find useful or interesting.

Dashboards - Doug Glancy calls this technique "History Navigation", and it would be a nice feature on an Excel dashboard. Type in a year, and a table displays that year's data. A drop down list stores the years as you enter them, so it's easy to go back and check one again. Click "Clear History" to start fresh. (Level - Int)

Data - Antonio (Anakic) explains how he built an Excel add-in, QueryStorm, which helps you clean, query and modify data. Would you give up so much for an Excel project? (Level - All)

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

Back in Time

Last week, I enjoyed watching this "Introduction to Microsoft Excel" promotional video from 1992 -- they were launching Excel 4.0. Two young businessmen are working on an Excel report, as they ride an elevator up to the boss' office. AutoFormat - it looks so professional! The video was posted on YouTube by Jan Brehm, who plays the boss.

Is their laptop a Toshiba TC3200? You can see part of the model name about the 1:20 mark. It reminds me of our first laptop - a Macintosh PowerBook 170. As you can see in the photo, it has Excel 3.0 installed, and it still works. Would you be able to get your Excel projects done with the features in that version?

weekly photo

That's it for this week! If you have any comments or questions, send me an email.

NOTE: For the online version, paste this URL into your web browser: http://www.contextures.com/newsletter/excelnews2018/20180417ctx.html
I'll also post any article updates or corrections there.

Debra Dalgleish
dsdalg @ gmail.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: May 4, 2018 3:26 PM