Identifying Specific Duplicate Records

[JakeEllins]

Hi everyone. I'm trying to identify all employees in a dataset that "work" in two specific departments. My unique identifier is the employee's id, and I have a separate column that identifies the department. My dataset has a lot of duplicates, and employees are often listed as working for one department many times.

I've tried using the unique tool, and multi-row formula tool but haven't had any luck. Any ideas? Thank you!

[apathetichell]

summarize - group by id - count. if the count is 2 or greater there are more than 2 records for that employee.

[JakeEllins]

Hi, thanks for the response. I've identified which records are duplicates but want to find all employees that work in two distinct departments. As an example, I could have an employee named 'Jack' who could be listed as working in a Business Intelligence department 10 times, but I'm trying to identify if 'Jack' is also listed in a different row working in the Sales department. So to clarify, I want to find all employees that work in Business Intelligence AND Sales.

[apathetichell]

Post some sample data with the output you want and i'll mock something up for you.

if you have unique identifiers for employees and then you transpose business and sales columns and then summarize count - you'll see where you have entries in both business in sales.

[JakeEllins]

Please see an example attached. Thank you, I really appreciate it.

[apathetichell · Accepted solution]

try this. if you need to make the two departments dynamic - you can do that too.