Hello,
I have two fields, CandidateEmail and ExamCode.
I need to find the Emails that have duplicate ExamCodes, so I am starting with trying to count ExamCodes per email.
For example I have this dataset:
| CandidateEmail | ExamCode |
| testemailONE@Test.com | DCP-109C |
| testemailONE@Test.com | DCP-316C |
| testemailONE@Test.com | DCP-316C |
| testemailONE@Test.com | DCP-316C |
| testemailONE@Test.com | PR000085 |
| testemailONE@Test.com | DCP-106C |
| testemailTWOTWO@Test.com | DCP-108C |
| testemailTWOTWO@Test.com | DCP-109C |
| testemailTWOTWO@Test.com | DCP-111C |
| testemailTWOTWO@Test.com | DCP-116C |
| testemailTWOTWO@Test.com | DCP-316C |
| testemailTWOTWO@Test.com | PR000033 |
| testemailTWOTWO@Test.com | PR000033 |
I would expect to see something like this:
| Emails | Duplicate ExamCodes | Count |
| testemailONE@Test.com | DCP-109C | 1 |
| testemailONE@Test.com | DCP-316C | 3 |
| testemailONE@Test.com | PR000085 | 1 |
| testemailONE@Test.com | DCP-106C | 1 |
| testemailTWOTWO@Test.com | DCP-108C | 1 |
| testemailTWOTWO@Test.com | DCP-109C | 1 |
| testemailTWOTWO@Test.com | DCP-111C | 1 |
| testemailTWOTWO@Test.com | DCP-116C | 1 |
| testemailTWOTWO@Test.com | DCP-316C | 1 |
| testemailTWOTWO@Test.com | PR000033 | 2 |
Instead when I use this:
=If(Count(Total <CandidateEmail, ExamCode> 1) > 1, Count(Total <CandidateEmail, ExamCode> 1))
I get this(I included the ExamID to show they are different exam records there to count):
| CandidateEmail | ExamCode | =If(Count(Total <CandidateEmail, ExamCode> 1) > 1, Count(Total <CandidateEmail, ExamCode> 1)) | ExamID |
| testemailONE@Test.com | DCP-109C | - | 32557 |
| testemailONE@Test.com | DCP-316C | - | 48053 |
| testemailONE@Test.com | DCP-316C | - | 48391 |
| testemailONE@Test.com | DCP-316C | - | 48848 |
| testemailONE@Test.com | PR000085 | - | 2733 |
| testemailONE@Test.com | DCP-106C | - | 18255 |
| testemailTWOTWO@Test.com | DCP-108C | - | 18141 |
| testemailTWOTWO@Test.com | DCP-109C | - | 18140 |
| testemailTWOTWO@Test.com | DCP-111C | - | 44998 |
| testemailTWOTWO@Test.com | DCP-116C | - | 48714 |
| testemailTWOTWO@Test.com | DCP-316C | - | 48195 |
| testemailTWOTWO@Test.com | PR000033 | - | 2608 |
| testemailTWOTWO@Test.com | PR000033 | - | 2607 |
Is this the wrong expression for something like this? Or am I using it wrong?
=If(Count(Total <CandidateEmail, ExamCode> 1) > 1, Count(Total <CandidateEmail, ExamCode> 1))
