Contextures News 2020-12-01

Excel Troubleshooting Challenge

December 1, 2020

Excel for the holidays, troubleshooting challenge, 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.

Thank you for reading the news, and you'll get the next email in two weeks, on December 15th.

• Holiday Excel
• Troubleshooting
• Excel Articles
• Weekly Photo

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Holiday Excel

The holiday season might be different this year, but I hope you find things to celebrate!

If you'd like a bit of help from Excel, there are planners, Advent calendars, Christmas trees, and other sample workbooks on my Contextures site.

I've updated the Excel Holiday Planner for 2020, so get the new version, if you've downloaded it in past years.

Troubleshooting

Here's an Excel troubleshooting challenge for you, and thanks to Alex Blakenburg, who sent it. His solutions are below the screen shot.

• The worksheet has rows of data, with columns for up to 5 item codes (text)
• One or more blank cells are allowed in each row
• If item codes have been entered, are they all the same as the first code in that row?

This screen shot shows the sample data, and you can click here to download the sample file.

• How can you check each row, and show which rows have problem codes?
• Can you find a solution for older versions of Excel, and for Excel 365, if you have that version?

There is a formula in column C, so you can test with empty strings: =IF($C$1="x","","test")

Troubleshooting Solutions

For these solutions, add a new column in the table, with one of the following formulas. That column can be filtered for FALSE, to find rows where there is a problem with the codes.

Older Versions: In older versions of Excel, without the new spill functions, you can use this formula in cell G4. It uses cell references, but you could use table references instead:

• =COUNTIFS(B4:F4, LOOKUP(2,1/(B4:F4<>""),B4:F4)) =COUNTIFS(B4:F4,"?*")

The formula finds the first code in the row, and compares its count, to the count of all the non-blank code cells in that row.

Excel 365: In Excel 365, you can use this spill function formula:

• =COUNTA(UNIQUE(TRANSPOSE( FILTER($B4:$F4,$B4:$F4<>""))))=1

The formula creates a unique list of non-blank codes. The count of those codes should be 1.

Excel Articles

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

YouTube: If you're looking for Excel videos on YouTube, use the Search box on your favourite channel pages, like Contextures, instead of the general search box at the top. This video shows the steps. (Level - All)

Spreadsheets - The Essential Spreadsheets guide, on the University of York site, shows how to do things in Excel and Google Sheets, with written steps, videos and sample files. It's interesting to see the comparisons! (Level - Intro/Int)

Also see: My Excel Products || Previous Issues

Record Snow

At the beginning of last week, we had a record-setting snowfall for November 22nd, with 19.4 cm piling up in our back yard. It was the day before our current lockdown, so people were frantically doing their errands, despite the weather. Fortunately, we were able to stay home and enjoy the winter wonderland from indoors. The snow is gone now, but there's more to come, I'm sure!

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