Contextures News 20190305

Quickly Compare 2 Excel Tables

March 5, 2019

Hide duplicates, compare tables trick, 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 helps support the free tutorials on my site.

Compare 2 Tables

Do you ever need to compare two Excel Tables? This simple formula shows TRUE, if the tables are exactly the same. If there are any differences, the result is FALSE. Thanks to UniMord for this tip!

=SUMPRODUCT((Table1=Table2)-1)=0

SUMPRODUCT multiplies the array of value comparisons, and returns the sum. Can you figure out what the "-1" does in the formula? (see below). See another SUMPRODUCT example here.

This formula compares each cell in the two tables. If you highlight (Table1=Table2), in the Formula Bar, and press F9 to calculate, you'll see all the results as TRUE or FALSE (top image below).

But, if you also include the -1, that operation changes the results to numbers (bottom image below)

• If all the comparisons are TRUE (1), all the numbers will be zero (1-1), and the total will be zero
• If any are FALSE (0), they will show as -1 (0-1), and the total will not be zero.

Hide Duplicates

When you're working with tables, it's important to have all the key data in each row, so you can sort and filter the data. However, that can make the list hard to read, with lots of repeating text in some columns.

To make the list look cleaner, use conditional formatting to hide duplicate values. In this example, the list is sorted by month, and only the first instance of each month name will appear.

• Select all the cells with month names, cells D2:D13 in this example.
• On the Ribbon's Home tab, click Conditional Formatting
• Click Use a Formula to Determine Which Cells to Format
• In the Edit the Rule section, enter this formula: =D2=D1
• Click the Format button, and choose white font and white fill
• Click OK to close the windows, and the duplicate months are hidden.

Watch my video to see the steps. The steps haven't changed since I made that video in 2008, but now my mouse doesn't click as loudly!

Excel Articles

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

Charts - With a stacked column chart, you can show the amount for each section, but there's no built-in way to show the column's total. Jon Peltier created a free add-in that quickly puts those totals in stacked chart for you (column, bar, area or line). (Level - All)

Power Query - Ken Puls shows how to pivot stacked data, using Power Query. For example, a mailing list, with names and addresses all in the same column, with blank rows separating them. You don't need complicated formulas now!. (Level - Int/Adv)

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

Fancy Dinners

How do you celebrate the end of a long week? We like to have our Saturday suppers by candlelight, on the "good" dishes -- even simple food seems fancy that way! Last week, to give spring a little encouragement, we used our flowery napkins. It probably won't make winter end any sooner, but it can't hurt to try, right?

P.S. Our candle is on the table. We don't try to solve the candle problem, and stick it to the wall.

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190305ctx.html
I'll also post any article updates or corrections there.

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

Debra Dalgleish
[email protected] gmail.com

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.