Excel Compare Cells Challenge
April 17, 2018
Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!
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.
If you do this frequently, add the command to your Quick Access Toolbar. In the "All Commands" list, look for "Print List".
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.
This challenge came from UniMord (thanks!), and here's how his solution works:
In the SUMPRODUCT formula:
For more details on this formula (with screen shots), and other formulas to compare cells, see my Compare Cells blog post.
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)
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?
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: https://www.contextures.com/newsletter/excelnews2018/20180417ctx.html
I'll also post any article updates or corrections there.
dsdalg @ gmail.com
Last updated: May 4, 2018 3:26 PM