Find Duplicates But Only If They Are In More Than One Visit IDs In A Second Column

HobbesIsReal

HobbesIsReal

New Member
Joined
Mar 16, 2013
Messages
11
Office Version
  1. 365
Platform
  1. Windows
I have a unique challenge in find and ignoring duplicates. I am trying to find duplicate photos in field call reports. To identify a photo to compare if it is a duplicate I used concate with the employee's "First" and "Last" name columns along with the photo's "originalDateTime" to create a unique identifier in the "Duplicate concat" column. Then a countif formula to identify what are duplicates or unique in the "Duplicate concat" column.

The challenge comes in that it is okay if there is a duplicate photo in the same "visitID". We only need to identify duplicate photos that are in more than 1 "visitID". I am sure I am overlooking an obvious solution, but this is where I am getting stuck.

Bellow is an example along with the formulas I have at the moment. I am flexible so I can create another column if that helps with any referencing. The data in the first 4 columns would be pasted in with live data each time the report is created.


visitIDFirstLastoriginalDateTimeDuplicate concatDuplicate Photo Across Multiple Call Reports
3056538DarthVader11/30/21 7:09 PMCONCAT([@First],[@Last],[@originalDateTime])IF(COUNTIF([Duplicate concat], [@[Duplicate concat]])>1, "Duplicate", "Unique")
3056538DarthVader11/30/21 7:09 PMDarthVader44530.7979166667Duplicate
3056538DarthVader11/30/21 7:09 PMDarthVader44530.7979166667Duplicate
3056538DarthVader11/30/21 7:10 PMDarthVader44530.7986111111Unique
3030617DarthVader1/4/22 6:06 PMDarthVader44565.7541666667Duplicate
3030617DarthVader1/4/22 6:06 PMDarthVader44565.7541666667Duplicate
3030617DarthVader1/4/22 6:06 PMDarthVader44565.7541666667Duplicate
3030617DarthVader1/4/22 6:07 PMDarthVader44565.7548611111Unique
3030617DarthVader1/4/22 6:08 PMDarthVader44565.7555555556Unique
3030654DarthVader1/4/22 9:20 PMDarthVader44565.8888888889Duplicate
3030654DarthVader1/4/22 9:20 PMDarthVader44565.8888888889Duplicate
3030654DarthVader1/4/22 9:20 PMDarthVader44565.8888888889Duplicate
3030654DarthVader1/4/22 9:21 PMDarthVader44565.8895833333Unique
3030654DarthVader1/4/22 9:22 PMDarthVader44565.8902777778Unique
3030755DarthVader1/5/22 5:11 PMDarthVader44566.7159722222Unique
3030755DarthVader1/5/22 5:13 PMDarthVader44566.7173611111Unique
3030755DarthVader1/5/22 5:18 PMDarthVader44566.7208333333Duplicate
3030755DarthVader1/5/22 5:18 PMDarthVader44566.7208333333Duplicate


Duplicate Photos Across Multiple Visit IDs Example.xlsx
ABCDEF
1visitIDFirstLastoriginalDateTimeDuplicate concatDuplicate Photo Across Multiple Call Reports
23056538DarthVader11/30/21 7:09 PMDarthVader44530.7979166667Duplicate
33056538DarthVader11/30/21 7:09 PMDarthVader44530.7979166667Duplicate
43056538DarthVader11/30/21 7:10 PMDarthVader44530.7986111111Unique
53030617DarthVader1/4/22 6:06 PMDarthVader44565.7541666667Duplicate
63030617DarthVader1/4/22 6:06 PMDarthVader44565.7541666667Duplicate
73030617DarthVader1/4/22 6:06 PMDarthVader44565.7541666667Duplicate
83030617DarthVader1/4/22 6:07 PMDarthVader44565.7548611111Unique
93030617DarthVader1/4/22 6:08 PMDarthVader44565.7555555556Unique
103030654DarthVader1/4/22 9:20 PMDarthVader44565.8888888889Duplicate
113030654DarthVader1/4/22 9:20 PMDarthVader44565.8888888889Duplicate
123030654DarthVader1/4/22 9:20 PMDarthVader44565.8888888889Duplicate
133030654DarthVader1/4/22 9:21 PMDarthVader44565.8895833333Unique
143030654DarthVader1/4/22 9:22 PMDarthVader44565.8902777778Unique
153030755DarthVader1/5/22 5:11 PMDarthVader44566.7159722222Unique
163030755DarthVader1/5/22 5:13 PMDarthVader44566.7173611111Unique
173030755DarthVader1/5/22 5:18 PMDarthVader44566.7208333333Duplicate
183030755DarthVader1/5/22 5:18 PMDarthVader44566.7208333333Duplicate
Sheet1
Cell Formulas
RangeFormula
E2:E18E2=CONCAT([@First],[@Last],[@originalDateTime])
F2:F18F2=IF(COUNTIF([Duplicate concat], [@[Duplicate concat]])>1, "Duplicate", "Unique")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:D18Expression=MOD(ROW()-3,2*1)+1<=1textYES
F2:F18Cell Valuecontains "Duplicate"textNO
A2:A18Expression=MOD(ROW()-3,2*1)+1<=1textYES
B2:C18Expression=MOD(ROW()-3,2*1)+1<=1textYES
E2:E18Cell ValueduplicatestextNO
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about
Excel Formula: 
=IF(COUNTIFS([Duplicate concat], [@[Duplicate concat]],[visitID],"<>"&[@visitID]), "Duplicate", "Unique")
 
Upvote 0
Solution
Thanks for the tip for updating my profile.

I tried that formula but the result was "Duplicate" for every entry.

Results.PNG
 
Upvote 0
That's not what I got.
Fluff.xlsm
ABCDEF
1visitIDFirstLastoriginalDateTimeDuplicate concatDuplicate Photo Across Multiple Call Reports
23056538DarthVader44530.79792DarthVader44530.7979166667Unique
33056538DarthVader44530.79792DarthVader44530.7979166667Unique
43056538DarthVader44530.79861DarthVader44530.7986111111Unique
53030617DarthVader44565.75417DarthVader44565.7541666667Unique
63030617DarthVader44565.75417DarthVader44565.7541666667Unique
73030617DarthVader44565.75417DarthVader44565.7541666667Unique
83030617DarthVader44565.75486DarthVader44565.7548611111Unique
93030617DarthVader44565.75556DarthVader44565.7555555556Unique
103030654DarthVader44565.88889DarthVader44565.8888888889Unique
113030654DarthVader44565.88889DarthVader44565.8888888889Unique
123030654DarthVader44565.88889DarthVader44565.8888888889Unique
133030654DarthVader44565.88958DarthVader44565.8895833333Unique
143030654DarthVader44565.89028DarthVader44565.8902777777Duplicate
153030755DarthVader44565.89028DarthVader44565.8902777777Duplicate
163030755DarthVader44566.71736DarthVader44566.7173611111Unique
173030755DarthVader44566.72083DarthVader44566.7208333333Unique
183030755DarthVader44566.72083DarthVader44566.7208333333Unique
Original
Cell Formulas
RangeFormula
E2:E18E2=CONCAT([@First],[@Last],[@originalDateTime])
F2:F18F2=IF(COUNTIFS([Duplicate concat], [@[Duplicate concat]],[visitID],"<>"&[@visitID]), "Duplicate", "Unique")


Note I changed the time in D15 otherwise they all came back as Unique.
 
Upvote 0
First of all, really appreciate the help and fast responses. Once I get it work, you are going to make me look good at work when I show this spreadsheet off! LOL

That is sooo odd? Your results are exactly what I am after. But below are mine with just copy and pasting the formula you used....

Duplicate Photos Across Multiple Visit IDs Example.xlsx
ABCDEF
1visitIDFirstLastoriginalDateTimeDuplicate concatDuplicate Photo Across Multiple Call Reports
23056538DarthVader11/30/21 7:09 PMDarthVader44530.7979166667Duplicate
33056538DarthVader11/30/21 7:09 PMDarthVader44530.7979166667Duplicate
43056538DarthVader11/30/21 7:10 PMDarthVader44530.7986111111Duplicate
53030617DarthVader1/4/22 6:06 PMDarthVader44565.7541666667Duplicate
63030617DarthVader1/4/22 6:06 PMDarthVader44565.7541666667Duplicate
73030617DarthVader1/4/22 6:06 PMDarthVader44565.7541666667Duplicate
83030617DarthVader1/4/22 6:07 PMDarthVader44565.7548611111Duplicate
93030617DarthVader1/4/22 6:08 PMDarthVader44565.7555555556Duplicate
103030654DarthVader1/4/22 9:20 PMDarthVader44565.8888888889Duplicate
113030654DarthVader1/4/22 9:20 PMDarthVader44565.8888888889Duplicate
123030654DarthVader1/4/22 9:20 PMDarthVader44565.8888888889Duplicate
133030654DarthVader1/4/22 9:21 PMDarthVader44565.8895833333Duplicate
143030654DarthVader1/4/22 9:22 PMDarthVader44565.8902777778Duplicate
153030755DarthVader1/5/22 5:11 PMDarthVader44566.7159722222Duplicate
163030755DarthVader1/5/22 5:13 PMDarthVader44566.7173611111Duplicate
173030755DarthVader1/5/22 5:18 PMDarthVader44566.7208333333Duplicate
183030755DarthVader1/5/22 5:18 PMDarthVader44566.7208333333Duplicate
Sheet1
Cell Formulas
RangeFormula
E2:E18E2=CONCAT([@First],[@Last],[@originalDateTime])
F2:F18F2=IF(COUNTIFS([Duplicate concat], [@[Duplicate concat]],[visitID],"<>"&[@visitID]), "Duplicate", "Unique")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:D18Expression=MOD(ROW()-3,2*1)+1<=1textYES
F2:F18Cell Valuecontains "Duplicate"textNO
A2:A18Expression=MOD(ROW()-3,2*1)+1<=1textYES
B2:C18Expression=MOD(ROW()-3,2*1)+1<=1textYES
E2:E18Cell ValueduplicatestextNO
 
Upvote 0
Are your VistID's stored as text?
 
Upvote 0
Yes. They are text. But I used ASAP Utilities and converted all of them to numbers and back to text. I get these results
  1. Text = results are 100% Duplicate
  2. Numbers = results are 100% Unique
 
Upvote 0
Are there any that consider duplicates in that data?
 
Upvote 0
Nevermind.... You are THE genius. I had a stupid filter hiding the other results!!! LOL.

Thank you so much for your help!!!!

This works perfectly!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Process and Indentation
Replies
1
Views
214
Fuji
F
JenniferMurphy
  • Solved
Is there a way to format date-time with proper alignment?
Replies
8
Views
508
JenniferMurphy
JenniferMurphy
C
  • Solved
Averaging + & - times in Text format
2
Replies
10
Views
598
Alex Blakenburg
A
JenniferMurphy
  • Question Question
Help with coin-collecting sheet
Replies
3
Views
174
felixstraube
F
JenniferMurphy
  • Solved
How to rank a combination of 2 or more columns?
2
Replies
10
Views
296
Scott Huish
Scott Huish

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top