Fixing Interchanged First and Last Names in Running Count in Google Sheets

Have you ever encountered a situation where the first and last names in a running count formula in Google Sheets were interchanged, resulting in incorrect occurrence numbers? Don’t worry, I’ve got you covered! In this article, I will show you how to fix this issue and ensure you get the accurate running count you need.

The Problem with Interchanged Names

When using a regular running count array formula, names that are interchanged are treated as new entries, leading to a new count. For example, if “Denise Young” appears twice and “Young Denise” appears thrice, the correct occurrence numbers would be {1, 2, 3, 4, 5}. However, the incorrect result would be {1, 2, 1, 2, 3}. This can be quite frustrating, especially when dealing with large datasets.

To address this issue, I have used an array formula in cell D2 that fixes interchanged first and last names while returning the correct running count. But before we dive into the formula, let’s understand a key aspect to consider.

A Note on Swapped Names

It’s important to note that the formula treats swapped names as the same, even if they represent different individuals. So, if you have “Essie Phelps” and “Phelps Essie,” both names will be considered identical. This is because the formula treats them as interchanged names.

Now, let’s explore the formula and its explanation.

How to Fix Interchanged First and Last Names in Running Count

To fix interchanged first and last names in a running count in Google Sheets, we will use a LAMBDA formula. This formula will return the correct running count after resolving the issue.

Here is the formula:

=ARRAYFORMULA(MAP(B2:B, C2:C, LAMBDA(first, last, IF(first & last = "",, SUM(COUNTIFS(B2:first & C2:last, {first & last, last & first})))))

Let’s break down the formula to understand its components:

• B2:B is the range of cells that contains the first names.
• C2:C is the range of cells that contains the last names.
• B2 is the first cell in the range of cells that contains the first names.
• C2 is the first cell in the range of cells that contains the last names.

This is an array formula, meaning you just need to insert it in cell D2, and it will automatically expand down if there are no values blocking its expansion. However, if there are any such values, the formula will return the #REF! error.

How the Formula Works

Now, let’s delve into the inner workings of the formula and understand the steps it takes to fix the interchanged first and last names while returning the correct running count.

1. Non-Array Formula to Fix Interchanged Names

To begin, we will create a non-array formula that fixes the interchanged names in the running count of occurrences. The key to this formula lies in using the OR operator within the COUNTIFS function.

Here is the non-array formula:

=ARRAYFORMULA(SUM(COUNTIFS($B$2:B2 & $C$2:C2, {B2 & C2, C2 & B2})))

By inserting this formula in cell D2 and dragging the fill handle down to cell C2, you will obtain the running count after fixing the interchanged first and last names.

To understand the COUNTIFS formula in detail, let’s break it down further.

The syntax of the COUNTIFS function is as follows:

COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])

The following COUNTIFS formula counts the range $B$2:B2 & $C$2:C2 with the criteria {B2 & C2, C2 & B2}:

=ARRAYFORMULA(COUNTIFS($B$2:B2 & $C$2:C2, {B2 & C2, C2 & B2}))

Where:

• criteria_range1 is $B$2:B2 & $C$2:C2
• criterion1 is {B2 & C2, C2 & B2}

Since the criterion1 contains two conditions, we use the ARRAYFORMULA function.

Simplifying the formula, we have:

=ARRAYFORMULA(COUNTIFS(first_name & last_name, {first_name & last_name, last_name & first_name}))

The SUM function totals the returned values:

=ARRAYFORMULA(SUM(COUNTIFS($B$2:B2 & $C$2:C2, {B2 & C2, C2 & B2})))

When we drag the formula down, the range becomes $B$2:$B$3 & $C$2:$C$3, and the criteria become {B3 & C3, C3 & B3} in the second row, as shown in Figure 2.

2. Array Formula to Fix Swapped First and Last Names

To convert the formula in cell D2 to an array formula that automatically expands, we will utilize the MAP function.

The syntax of the MAP function is as follows:

MAP(array1, [array2, ...], LAMBDA)

In this case, array1 is B2:B, and array2 is C2:C.

=MAP(B2:B, C2:C, LAMBDA(

Within the lambda, we will use the COUNTIFS formula. However, instead of directly copying and pasting it, we should define names that represent the two arrays and replace them within the COUNTIFS formula.

=MAP(B2:B, C2:C, LAMBDA(first, last,

By naming B2:B as first and C2:C as last, we can update the COUNTIFS formula and insert it inside the lambda.

=MAP(B2:B, C2:C, LAMBDA(first, last, ARRAYFORMULA(SUM(COUNTIFS($B$2:first & $C$2:last, {first & last, last & first}))) ))

This formula fixes the interchanged first and last names while returning the running count.

Additionally, we need to make one more tweak to the formula to avoid the running count of blank cells in the first and last name columns. This is achieved through an IF logical test in the main formula.

That’s it! You now have a formula that fixes interchanged first and last names in a running count in Google Sheets, ensuring accurate occurrence numbers.

Remember, if you need any further assistance with Google Sheets or any other related topic, be sure to visit Crawlan.com. We have a plethora of resources to help you excel in your data management journey.

Happy Sheets-ing!