Help Cleaning data based on dates in different rows

csh8428

I have a data set where I need to count the number of times an HR Rep has changed AND the Distinct number of different people that have held the role for a client.. Stay with me.. it gets harder 😉 The counting is not the issue. The issue is cleaning the data.

(Scenario 1) There is bad data where our client administrator has accidentally deleted the HR Rep and then re-added them. This creates a new record in the database. However, this artificially inflates the count. I was able to account for that, BUT that set of tools wound of removing a legit change that needed to be counted: (Scenario 2) when a rep is changed from person A to person B and back to Person A. That is a legit change and should be counted.

I'm having trouble trying to figure out a way to "correct" Scenario 1 by taking min and max in a summarize tool while STILL keeping the records from Scenario 2 from merging.

Scenario 1 should take the min date of of all records(there can be a set of more than 2) where account ID, User, ID, Name and Role are the same and there are no other people in between.

Scenario 2 should NOT be affected by Scenario 1 and should be left as is.

The data looks like this

AccountId	User_ID	Name	TeamMemberRole	TMR_CreatedDate	TMR Date Removed	Comment(not needed for workflow calcs)
ABC	222	John Smith	HR Rep	1/13/2020	12/31/9999	Scenario 1
ABC	222	John Smith	HR Rep	9/26/2019	1/12/2020	Scenario 1
ABC	555	Dave Spinner	HR Rep	7/1/2019	9/25/2019
ABC	333	Brad Sol	HR Rep	11/1/2018	6/30/2019	Scenario 2
ABC	444	Julie Glass	HR Rep	7/16/2018	10/31/2018	Scenario 2
ABC	333	Brad Sol	HR Rep	5/1/2018	7/15/2018	Scenario 2

I'm trying to get it to look like this

AccountId	User_ID	Name	TeamMemberRole	TMR_CreatedDate	TMR Date Removed	Comment(not needed for workflow calcs)
ABC	222	John Smith	HR Rep	9/26/2019	12/31/9999	Scenario 1
ABC	555	Dave Spinner	HR Rep	7/1/2019	9/25/2019
ABC	333	Brad Sol	HR Rep	11/1/2018	6/30/2019	Scenario 2
ABC	444	Julie Glass	HR Rep	7/16/2018	10/31/2018	Scenario 2
ABC	333	Brad Sol	HR Rep	5/1/2018	7/15/2018	Scenario 2

I don't need help on the counting, but here's what it would like if that helps

Total Number of changes: 4(first person isn't a "change")

Different # of people: 4

I've attached the same sample as the text in the post.

Thanks for any help!

Craig

Sample.csv

csh8428

I was able to solve it. I used a multi-row formula that compares the rows based on the criteria I needed. I then used another multi-row formula to compare that result as a  summarize key. I can't really upload a workflow to demonstrate it because it's confidential corporate data.