This tutorial has a unique type of formula to help you filter same-day duplicates in Google Sheets. It supports more than one format.
Same-day duplicates mean multiple occurrences of a value with the same date or timestamp.
My formula is very flexible. You can adjust it slightly to suites your four different data formats. Let me explain it.
The same-day duplicate filter formula requires one date or timestamp column and one description (item) column that determines the duplicates.
So there should be at least two columns in your data or range.
But, in some scenarios, you may want to filter same-day duplicates with one more additional criterion, i.e., an ID or Name column.
For example, a pharmacy sold the medicine “x” to two patients on the same day.
It’s not a duplicate sale because they sold the medicine to two different patients on the same date.
To determine that, we must require an ID column in the data.
So there can be either two columns: Timestamp and Description; or three columns: Timestamp, ID/Name, and Description.
But please wait!
In the above two cases, my filter formula can accommodate more than one description column like Timestamp, Description Column 1, Description Column 2, etc., or Timestamp, ID/Name, Description Column 1, Description Column 2, etc.
We will see all these four types of examples below. As a side note, don’t forget to check my ultimate duplicate removal tutorial.
Filter Same-Day Duplicates with Date and Description Columns in Google Sheets
Sample Data:
Let’s understand my sample data first. Then we will move to how to filter same-day duplicates in Google Sheets.
My sample data is in A1:B. We don’t require the header row in our formula. So we will use A2:B.
A2:A – Contains DateTime (Timestamps). Not an issue if you have a date column instead. My formula can handle that effectively without any modifications to it.
B2:B – The column that determines duplicates. This column can contain values such as medicine names, fruit names, equipment names, vehicle numbers, etc. For example purposes, I’m just using a few capital letters.
Result:
My filter formula in cell D2 filters the same-day duplicates from the range A2:B in Google Sheets.
As you can see, there are duplicate entries on the 23rd and 25th of April, 2023. The item “A” appears twice on the 23rd. Regarding the 25th, items “Y” and “Z” appears twice.
Formula # 1:
=lambda(ts,id,desc,ftr,unique(let(result,let(flattened,index(split(flatten(int(ts)&"|"&id&"|"& choosecols(desc,{1})),"|")),filter(flattened,index(flattened,0,if(ftr=false,2,3))<>"")),filter(result,let(counting,transpose(query(transpose(result),,9^9)),countifs(counting,counting)>1)))))(A2:A,,B2:B,false)The above is the filter formula in cell D2 that returns the above same-day duplicates.
Please take note of the vivid green-cyan part of the formula. It follows the below syntax.
Syntax: timestamp,[id],description,if_id
timestamp: The timestamp column reference.
id: The ID column reference. It’s an optional argument. We have no ID column in our sample data above, so I’ve omitted using this argument in the formula.
if_id: Specify TRUE if the data has an ID column, or specify FALSE.
Filter Same-Day Duplicates with Date, ID, and Description Columns in Google Sheets
This time we have one additional column between the timestamp and description, i.e., the ID column.
Assume the ID column contains patient names, and the description column contains medicines sold to them.
How to find the same medicine sold to the same person twice on any date?
In other words, filter same-day duplicates based on timestamp, id, and description columns.
Formula # 2:
=lambda(ts,id,desc,ftr,unique(let(result,let(flattened,index(split(flatten(int(ts)&"|"&id&"|"& choosecols(desc,{1})),"|")),filter(flattened,index(flattened,0,if(ftr=false,2,3))<>"")),filter(result,let(counting,transpose(query(transpose(result),,9^9)),countifs(counting,counting)>1)))))(A2:A,B2:B,C2:C,true)The above is the new filter formula in cell D2 that returns the same-day duplicates based on timestamp, ID, and description columns.
Please take note of the vivid green-cyan part of the formula. It’s as per the syntax: timestamp,[id], description,if_id.
How to Include Multiple Description Columns?
We have addressed two scenarios: Filter same-day duplicates with or without the ID column in Google Sheets.
What about the additional description column(s) in both of them?
In other words, how to filter duplicates with the same date in rows in Google Sheets?
The occurrences can be in one or more rows but on the same date.
The good thing is that we can use the above same formulas. The vivid green-cyan part of the formulas will be the same.
This time we should concentrate on the vivid red part of the formula.
In the following table, A2:A contains the timestamp, and some of the columns in B2:J is for the description.
Columns 1, 3, 5, 7, and 9 are the description columns in the B2:J range.
So to filter same-day duplicates in rows with the same date entry, we should use our formula # 1 and replace 1 in the vivid red part of the formula with 1, 3, 5, 7, 9.
Formula # 3:
=lambda(ts,id,desc,ftr,unique(let(result,let(flattened,index(split(flatten(int(ts)&"|"&id&"|"& choosecols(desc,{1,3,5,7,9})),"|")),filter(flattened,index(flattened,0,if(ftr=false,2,3))<>"")),filter(result,let(counting,transpose(query(transpose(result),,9^9)),countifs(counting,counting)>1)))))(A2:A,,B2:J,false)If you have an ID column, the formula will be as follows.
Formula # 4:
=lambda(ts,id,desc,ftr,unique(let(result,let(flattened,index(split(flatten(int(ts)&"|"&id&"|"& choosecols(desc,{1,3,5,7,9})),"|")),filter(flattened,index(flattened,0,if(ftr=false,2,3))<>"")),filter(result,let(counting,transpose(query(transpose(result),,9^9)),countifs(counting,counting)>1)))))(A2:A,B2:B,C2:K,true)I hope the above two formulas are self-explanatory.
Filtering Duplicates within the Same Date in Rows: Formula Logic
Let me unveil the secret of my above four dynamic formulas for Sheets enthusiasts. All of them follow the same logic.
Unpivot, Combine, and Count: The logic behind filtering same-day duplicates in Google Sheets.
If any row repeats twice in the count, that will be a duplicate. I’ve used this logic in all the above four formulas.
Let’s understand this logic with the help of formula parts. We will use the formula # 2 example for this.
1. Unpivot Source Data (D2):
=index(split(flatten(int(A2:A12)&"|"&B2:B12&"|"& choosecols(C2:C12,{1})),"|"))You won’t see the same parts in my ‘original’ formulas because I’ve used names in that to represent arrays and to avoid repetition of formula parts. The LAMBDA and LET take care of that.
Related: A Simple Formula to Unpivot a Dataset in Google Sheets.
2. Combine Each Column in the Unpivoted Data (G2):
=let(result,index(split(flatten(int(A2:A12)&"|"&B2:B12&"|"& choosecols(C2:C12,{1})),"|")),transpose(query(transpose(result),,9^9)))Related: The Flexible Array Formula to Join Columns in Google Sheets.
3. Finding the Occurrence of Rows in the Combined Data (H2):
=index(let(counting,let(result,index(split(flatten(int(A2:A12)&"|"&B2:B12&"|"& choosecols(C2:C12,{1})),"|")),transpose(query(transpose(result),,9^9))),countifs(counting, counting)))That’s all. Thanks for the stay. Enjoy!
