Challenge Scenario
When consolidating customer sign-ups from multiple event registration booths or online forms, duplicate submissions frequently pollute the database.
By using an expanding range reference in COUNTIF($A$2:A2, A2), data analysts can track the cumulative frequency of each customer name and identify whether a record is a fresh unique submission or a redundant duplicate.
As a CRM Data Administrator, your tasks are:
- Occurrence Count (Column B): Calculate the cumulative occurrence of each name using an expanding range:
=COUNTIF($A$2:A2, A2).
- Duplicate Flag (Column C): If Occurrence Count is equal to 1 (
B2 = 1), label as "UNIQUE"; otherwise label as "DUPLICATE".
- Total Entries (Cell B8): Count total raw registrations using
COUNTA(A2:A5).
- Unique Submissions (Cell C8): Count how many records are labeled
"UNIQUE" using COUNTIF.
Your Sheet Layout
Understanding how the duplicate deduplication audit sheet is organized:
- Customer Name Submissions (Column A): Lists incoming customer names in order of receipt.
- Deduplication Audit (Columns B & C): Tracks cumulative occurrences (1st appearance, 2nd appearance) and flags unique vs duplicate rows.
- Registration Summary (Rows 7 & 8): Summarizes total received forms and counts net unique customer attendees.
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:A5 |
Customer Name |
Submissions |
Raw attendee sign-up names |
Read-only incoming sign-up records. |
B2:B5 |
Occurrence |
Frequency Tracking |
=COUNTIF($A$2:A2, A2) |
Counts appearances from the top of the list down to the current row. |
C2:C5 |
Status |
Deduplication Flag |
=IF(B2 = 1, "UNIQUE", "DUPLICATE") |
Marks 1st appearances as UNIQUE, and repeats as DUPLICATE. |
B8 |
Total Submissions |
Audit Summary |
=COUNTA(A2:A5) |
Counts total submissions received (4). |
C8 |
Unique Customers |
Audit Summary |
=COUNTIF(C2:C5, "UNIQUE") |
Counts net unique customers (3). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column B (B2:B6), output "Keep" on first appearance using COUNTIF($A$2:A2, A2)=1, otherwise "Duplicate".
2
In cell B9, count total rows using COUNTA(A2:A6).
3
In cell C9, count unique "Keep" clients using COUNTIF(B2:B6, "Keep").
4
In cell D9, count "Duplicate" client records using COUNTIF(B2:B6, "Duplicate").
Solution & Formula Breakdown
Let's examine how the expanding range technique identifies duplicates in real-time:
1. Tracking Cumulative Appearances with Expanding COUNTIF (B2:B5)
=COUNTIF($A$2:A2, A2)
Notice the range $A$2:A2:
- The start of the range (
$A$2) is locked with a dollar sign.
- The end of the range (
A2) is relative and expands as you copy the formula down:
- Row 2 (Alice, range
$A$2:A2): Alice appears for the 1st time → returns 1.
- Row 3 (Bob, range
$A$2:A3): Bob appears for the 1st time → returns 1.
- Row 4 (Alice, range
$A$2:A4): Alice appears for the 2nd time → returns 2.
- Row 5 (Charlie, range
$A$2:A5): Charlie appears for the 1st time → returns 1.
2. Flagging Unique vs Duplicate Status (C2:C5)
=IF(B2 = 1, "UNIQUE", "DUPLICATE")
If occurrence equals 1, the record is the initial unique entry ("UNIQUE"); any occurrence greater than 1 is a duplicate ("DUPLICATE").
3. Audit Summary Metrics (B8 & C8)
- Total Submissions (Cell B8):
=COUNTA(A2:A5) returns 4.
- Unique Customers (Cell C8):
=COUNTIF(C2:C5, "UNIQUE") returns 3.
The expanding range formula =COUNTIF($A$2:A2, A2) is one of the most famous and powerful formulas in Excel for deduplication without removing rows!