Google Sheets
Find Repeated Records in Google Sheets Without Deleting Them
Published by OneCell
Add a matching-row count to find repeated records in Google Sheets. For a two-column key, use COUNTIFS to check both parts of the key together. A count above one gives you a pair of codes to review against the source record. Google’s COUNTIFS reference
Choose what one row represents
Imagine an equipment log with one row for each kit reserved under a booking. A booking can contain several kits, and a kit can appear under several bookings. The combination of kit code and booking code identifies the reservation being checked.
On a practice tab called Bookings, enter Kit code in A1, Booking code in B1 and Matching rows in C1. Enter these fictional records.
Row 2. K-12 in A2 and B-410 in B2.
Row 3. K-12 in A3 and B-411 in B3.
Row 4. K-12 in A4 and B-410 in B4.
Row 5. K-14 in A5 and B-410 in B5.
Row 6. K-14 in A6. Leave B6 empty while its booking code is unknown.
Leave A7 and B7 empty.
Count every row with the same complete pair
Put this formula in C2, then copy C2 down through C7.
=IF(A2="", "", IF(B2="", "", COUNTIFS($A$2:$A$7, A2, $B$2:$B$7, B2)))
The two IF checks leave the result blank while either code is empty. Otherwise, COUNTIFS counts rows that match both codes. The dollar signs keep the comparison ranges fixed as you copy down. A2 and B2 change to the current row’s references. Google’s IF reference and COUNTIFS syntax
Expect 2, 1, 2, 1, blank, blank from C2 through C7. Rows 2 and 4 share the complete pair K-12 and B-410, so inspect both. Row 3 uses the same kit under a different booking. Row 5 uses a different kit under the same booking. Each of those complete pairs appears once.
Row 6 still needs its booking code confirmed. Its blank result means the pair has yet to be assessed. Compare the repeated pair with the original booking and find out what each row represents before correcting a record.
Change a code and check the result
In the practice data, change B4 to B-413. C2 through C5 should now all show 1. C6 and C7 should stay blank. Restore B4 to B-410 to bring back the original results.
To check the empty case, clear A2:B7 while keeping the helper formulas. All six results should display blank. Restore the sample codes to repeat the example.
Use consistent codes made from letters, numbers and hyphens for this example, and leave unknown codes genuinely empty. Special characters can have matching rules. Google’s criteria reference
The count covers all rows in the specified ranges, including hidden or filtered rows. As the log grows, extend both fixed comparison ranges to the same last row and copy the helper formula into the new rows.