# Monday Morning Mulling: May 2024 Challenge

3 June 2024

*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:

- Count how many times each Account ID appears in the list.
- Count how many of each combination of the Account ID and Account Name are in the list.
- 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**):

=COUNTIFS(Data[Account ID],Data[Account
ID])

-COUNTIFS(Data[Account ID],Data[Account ID],Data[Account Name],Data[Account
Name])

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.*