HobbesIsReal
New Member
- Joined
- Mar 16, 2013
- Messages
- 11
- Office Version
- 365
- Platform
- 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.
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.
visitID | First | Last | originalDateTime | Duplicate concat | Duplicate Photo Across Multiple Call Reports |
3056538 | Darth | Vader | 11/30/21 7:09 PM | CONCAT([@First],[@Last],[@originalDateTime]) | IF(COUNTIF([Duplicate concat], [@[Duplicate concat]])>1, "Duplicate", "Unique") |
3056538 | Darth | Vader | 11/30/21 7:09 PM | DarthVader44530.7979166667 | Duplicate |
3056538 | Darth | Vader | 11/30/21 7:09 PM | DarthVader44530.7979166667 | Duplicate |
3056538 | Darth | Vader | 11/30/21 7:10 PM | DarthVader44530.7986111111 | Unique |
3030617 | Darth | Vader | 1/4/22 6:06 PM | DarthVader44565.7541666667 | Duplicate |
3030617 | Darth | Vader | 1/4/22 6:06 PM | DarthVader44565.7541666667 | Duplicate |
3030617 | Darth | Vader | 1/4/22 6:06 PM | DarthVader44565.7541666667 | Duplicate |
3030617 | Darth | Vader | 1/4/22 6:07 PM | DarthVader44565.7548611111 | Unique |
3030617 | Darth | Vader | 1/4/22 6:08 PM | DarthVader44565.7555555556 | Unique |
3030654 | Darth | Vader | 1/4/22 9:20 PM | DarthVader44565.8888888889 | Duplicate |
3030654 | Darth | Vader | 1/4/22 9:20 PM | DarthVader44565.8888888889 | Duplicate |
3030654 | Darth | Vader | 1/4/22 9:20 PM | DarthVader44565.8888888889 | Duplicate |
3030654 | Darth | Vader | 1/4/22 9:21 PM | DarthVader44565.8895833333 | Unique |
3030654 | Darth | Vader | 1/4/22 9:22 PM | DarthVader44565.8902777778 | Unique |
3030755 | Darth | Vader | 1/5/22 5:11 PM | DarthVader44566.7159722222 | Unique |
3030755 | Darth | Vader | 1/5/22 5:13 PM | DarthVader44566.7173611111 | Unique |
3030755 | Darth | Vader | 1/5/22 5:18 PM | DarthVader44566.7208333333 | Duplicate |
3030755 | Darth | Vader | 1/5/22 5:18 PM | DarthVader44566.7208333333 | Duplicate |
Duplicate Photos Across Multiple Visit IDs Example.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | visitID | First | Last | originalDateTime | Duplicate concat | Duplicate Photo Across Multiple Call Reports | ||
2 | 3056538 | Darth | Vader | 11/30/21 7:09 PM | DarthVader44530.7979166667 | Duplicate | ||
3 | 3056538 | Darth | Vader | 11/30/21 7:09 PM | DarthVader44530.7979166667 | Duplicate | ||
4 | 3056538 | Darth | Vader | 11/30/21 7:10 PM | DarthVader44530.7986111111 | Unique | ||
5 | 3030617 | Darth | Vader | 1/4/22 6:06 PM | DarthVader44565.7541666667 | Duplicate | ||
6 | 3030617 | Darth | Vader | 1/4/22 6:06 PM | DarthVader44565.7541666667 | Duplicate | ||
7 | 3030617 | Darth | Vader | 1/4/22 6:06 PM | DarthVader44565.7541666667 | Duplicate | ||
8 | 3030617 | Darth | Vader | 1/4/22 6:07 PM | DarthVader44565.7548611111 | Unique | ||
9 | 3030617 | Darth | Vader | 1/4/22 6:08 PM | DarthVader44565.7555555556 | Unique | ||
10 | 3030654 | Darth | Vader | 1/4/22 9:20 PM | DarthVader44565.8888888889 | Duplicate | ||
11 | 3030654 | Darth | Vader | 1/4/22 9:20 PM | DarthVader44565.8888888889 | Duplicate | ||
12 | 3030654 | Darth | Vader | 1/4/22 9:20 PM | DarthVader44565.8888888889 | Duplicate | ||
13 | 3030654 | Darth | Vader | 1/4/22 9:21 PM | DarthVader44565.8895833333 | Unique | ||
14 | 3030654 | Darth | Vader | 1/4/22 9:22 PM | DarthVader44565.8902777778 | Unique | ||
15 | 3030755 | Darth | Vader | 1/5/22 5:11 PM | DarthVader44566.7159722222 | Unique | ||
16 | 3030755 | Darth | Vader | 1/5/22 5:13 PM | DarthVader44566.7173611111 | Unique | ||
17 | 3030755 | Darth | Vader | 1/5/22 5:18 PM | DarthVader44566.7208333333 | Duplicate | ||
18 | 3030755 | Darth | Vader | 1/5/22 5:18 PM | DarthVader44566.7208333333 | Duplicate | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2:E18 | E2 | =CONCAT([@First],[@Last],[@originalDateTime]) |
F2:F18 | F2 | =IF(COUNTIF([Duplicate concat], [@[Duplicate concat]])>1, "Duplicate", "Unique") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
D2:D18 | Expression | =MOD(ROW()-3,2*1)+1<=1 | text | YES |
F2:F18 | Cell Value | contains "Duplicate" | text | NO |
A2:A18 | Expression | =MOD(ROW()-3,2*1)+1<=1 | text | YES |
B2:C18 | Expression | =MOD(ROW()-3,2*1)+1<=1 | text | YES |
E2:E18 | Cell Value | duplicates | text | NO |