Contextures News 20191126

Tricky Excel Formula

November 26, 2019

Count duplicate sets, meal planners, 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.

Find Duplicate Sets

Last week, someone asked me how to count duplicate number sets. They had hundreds of rows, with 6 numbers in each row (yes, they were lottery results!) How many times was each number set in the list, in any order? For example -- 1,2,3,4,5,6 would be a duplicate of 3,4,5,1,2,3 -- all 6 numbers are the same.

This one's a bit tricky! Here's the sample data that I used, and my formula in column H counts the duplicates for each row. What formula would you use to solve this? You can download the sample file, and work on a solution.

My formula is below, and there are many more counting examples on my Contextures site.

Here's the array-entered formula that I used in cell H4, and a brief explanation on how it works. There are more notes in the sample file, on the solution sheet.

=SUM(IF(MMULT({1,1,1,1,1,1}, TRANSPOSE(COUNTIF(B4:G4, $B$2:$G$12)))=6,1))}

1. COUNTIF checks each table row, to see if each number has a match in the formula's row. The result is an array with 11 rows and 6 columns
2. MMULT will return an array with the number of matches in each row. We need that array to have 1 row, and 11 columns (1 column for each set of numbers)
   1. Array1 is {1,1,1,1,1,1}
   2. For Array2, TRANSPOSE switches the rows and columns in the COUNTIF array, to create an array with 6 rows and 11 columns.
3. IF returns a 1, if the number of matches is 6 (all others = FALSE)
4. SUM adds up all the 1s to give the number of duplicate sets

This screen shot shows the COUNTIF calculation results for Row 4, with the rows and columns transposed. The MMULT result is 1 row, with 11 columns, and the final SUM is 2 -- rows 4 and 6 have the same set of numbers.

With the array-entered formula in cell H4, we're doing all these calculations at once! As you can imagine, this will slow down a workbook, if the formula is in hundreds of rows.

On Contextures

Here are a couple of things from my Contextures site and blog.

Dinner Planner: My Excel dinner planner will help you schedule all the steps in your Thanksgiving meal preprations this week. I couldn't get through a holiday meal without it!

Pie Shape Colour: I've updated the Happy Face Colour file (UF0050) from last week. It now has a "Partial Circle" shape, and its Pie Slice changes size and colour, based on the number you enter on the worksheet. I've written some instructions too, on my Contextures blog.

Excel Articles

Here are a few Excel-related articles that you might find useful or interesting.

Rich Data: Jon Peltier shows how to use Excel's new Rich Data type for Geographical data. Make a list of region or city names, and Excel can automatically fill in related data, like population. (Level - All)

What's New: Watch 13 recordings from Excel sessions at this year's Microsoft Ignite event. See what Office Scripts can do, use Excel with Visio and Flow, or learn 5 pivot table hacks. And for a quick overview of what's new, check the list of Excel announcements from Ignite. (Level-Int/Adv)

Excel Tweets: See what people tweeted about Excel this week. Does Excel teach you patience?

Also see: My Excel Products || Previous Issues

November

This is the last newsletter for November, so it's the perfect time to share a poem that my son wrote in Grade 1. That was many years ago, and the ink has faded with age. In case you can't read the words, its sad message is this: "No shorts, No flowers, No picnics, November."

The picture is equally bleak - a row of flowers with all their heads missing, and a tree with half of its leaves gone. That's a nice orange suit though! And I'm sure that Winnipeg No Pants Man is happy that his team won the Grey Cup championship game on Sunday, and he doesn't have to wear shorts now!

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20191126ctx.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
ddalgleish @contextures.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.