Do you have several lists and want to compare them to find and list out the duplicates?
You can easily do that using my custom named functions in Google Sheets.
To compare all columns with each other in a range and list out the duplicates, you just need to import and use my two named functions: COMPARE_ALL_COLUMNS and CAC_CHILD.
It’s super simple! You only need to provide the range reference — for example, A2:C7.
The comparison will happen between:
- A2:A7 & B2:B7
- A2:A7 & C2:C7
- B2:B7 & C2:C7
Let’s see it in action:
Compare All Columns with Each Other for Duplicates — Example 1
Example #1
In this example, the child function (CAC_CHILD) returns the columns being compared in E2:E4, and the main function (COMPARE_ALL_COLUMNS) lists the duplicate values found during the comparison.
Here’s another example to compare all columns with each other and return the duplicates in Google Sheets:
This time, we’ll be working with five columns.
- Range:
A2:E8 - Child Formula:
G2 - Master Formula:
H2
Compare All Columns with Each Other for Duplicates — Example 2
Example #2
In real-life scenarios, you can use these functions to quickly check if the same names (or any data) appear across multiple teams, lists, or departments.
COMPARE_ALL_COLUMNS and CAC_CHILD: Syntax and Arguments
Syntax:
COMPARE_ALL_COLUMNS(range)Arguments:
| Argument | Description |
| range | The range of columns you want to compare with each other to find duplicates. You can pass a standard range reference like A2:C100, or an array like {A2:A100, B2:B100, C2:C100} — useful if your columns aren’t adjacent! |
Syntax:
CAC_CHILD(range)Arguments:
| Argument | Description |
| range | The columns you want to identify for comparison. Same as above: either a regular range or an array of separate columns. |
How to Import the Functions
You can import these custom functions from my sample Sheet below and start using them to compare all columns with each other for common values.
Instructions:
- Click the button to make a copy of my Sheet.
- Open the Sheet where you want to use these functions.
- Go to Data > Named functions > Import function.
- Follow the on-screen instructions — and you’re all set!
Now you can quickly compare all columns with each other in any range and return the duplicates or common entries.
Quick Recap with Examples
As mentioned earlier, here are the formulas used in the first example:
- E2 (Child Function):
=CAC_CHILD(A2:C7) - F2 (Main Function):
=COMPARE_ALL_COLUMNS(A2:C7)
✅ You can even include distant (non-adjacent) columns by providing them as an array like {A2:A7, F2:F7, Z2:Z7} in the range argument.
The E2 formula shows which columns are being compared, and the F2 formula compares all columns with each other to return matching/duplicate values.
Can I Use an Open Range Like A2:C Instead of A2:A7?
Yes, you can.
But I recommend using closed ranges (like A2:A7) whenever possible to improve performance in Google Sheets, especially when working with larger datasets.
In the second example, for instance, I used an open range:
- G2:
=CAC_CHILD(A2:E) - H2:
=COMPARE_ALL_COLUMNS(A2:E)
Final Thoughts
I hope the examples above make it clear how to use the custom functions COMPARE_ALL_COLUMNS and CAC_CHILD to easily compare all columns with each other for duplicate values in Google Sheets.
If you need more help working with multiple columns, check out these related tutorials:
