Monday Morning Mulling: 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

We needed a dynamic formula that will detect a problem in our data and will work even if we have a variable number of rows from time to time.  The formula should allow us to highlight any Account IDs that have more than one Account Name, using the example data shown here.

As always, there are numerous ways to solve most problems in Excel, so if yours is different, that's great, as long as it detects the same problem in the data.

Our approach was:

1. Count how many times each Account ID appears in the list.
2. Count how many of each combination of the Account ID and Account Name are in the list.
3. Subtract 2 from 1.  For any rows where the result is greater than zero, we know there's a problem.

For example, if we look at Account ID 1005, we get a count of three [3].  Looking at 1005 with name Steve, we get a count of one [1].  If these numbers are not the same, it means that the 1005 has names other than Steve, which is a problem.

The formula we used is the following (our Table is called Data):

The formula has two parts, both using COUNTIFS.

The first part, COUNTIFS(Data[Account ID],Data[Account ID]), finds the number of occurrences of each Account ID.  The Account ID column is the argument criteria_range1, and we use the same for criteria1.  The dynamic array behavior means that this will get the count for each row in the table.

The second part, COUNTIFS(Data[Account ID],Data[Account ID],Data[Account Name],Data[Account Name]), gets the number of occurrences of each combination of Account ID and Account Name.  We use two criteria ranges and criteria for this part, because we're counting the combination of Account ID and Account Name.

Then we simply subtract the second from the first, and if the answer is greater than zero [0], which means there are extra names used for the Account ID on that row.

As you can see, the Extra Names column shows if there are extra names used for the Account ID, and it calculates how many extra names there are.  Any values greater than zero [0] indicates a problem. The formula is not a part of the table, because dynamic arrays don't work inside tables.

You can download the original question file here and our suggested solution file here.

The Final Friday Fix will return on Friday 28 June 2024 with a new Excel Challenge. In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business working day.