Final Friday Fix: May 2024 Challenge

On the final Friday of each month, we set an Excel / Power Pivot / Power Query / Power BI problem for you to puzzle over for the weekend.  On the Monday, we publish a solution.  If you think there is an alternative answer, feel free to email us.  We’ll feel free to ignore you.

The Challenge

For a client, we recently built a solution that imports account records from a text file generated by their tracking system.  We were lucky to notice that there were a few mistakes in the data, before it caused a problem.  We always like to build error checks into our models, so when we notice mistakes like this, we don't leave it to chance for next time.

We had found that some account numbers in the list of data that had more than one account name.  The client confirmed that this should never be the case, so we needed a formula to detect that problem and highlight the error if it ever happens again.

For the challenge, we've simplified the data, but the problem is the same.  As you can see in the example, we have more than one account name for Account ID 1005.  We need to highlight this as an error so it can be corrected.  You might notice that Tim is listed for more than one Account. That's okay, because Tim can have multiple accounts. 

The challenge is to come up with a dynamic array formula to find any Account IDs that have more than one Account Name.  Our data will always be in a table, and it will have a different number of rows each time, so we want to use a dynamic array so that it will automatically work regardless of how many rows there are in the table.

You can download the original question file here.

Sounds easy?  Then why not have a go?  We’ll publish one solution in Monday’s blog.  Have a great weekend in the meantime!