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

[Jonathan-Sherman]

Hi @csh8428,

Is this what you're looking to achieve?

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[csh8428]

The logic is right, but the data set doesn't have a "Scenarios" column. I just put that in there for descriptive purposes.  I like that you split it up based on that and re-joined, but the problem I'm coming up against is how to identify those scenarios since the records that satisfy scenario 1 could be any number of records. 

[Jonathan-Sherman]

So what's the logic behind which is scenario 1, scenario 2 and blank?

[csh8428]

Scenario 1: Same AccountId, User_ID, Name, TeamMemberRole for multiple consecutive rows(The dataset is sorted: AccountID, User ID, TermMemberRole, TMR Date Removed; Descending.

Scenario 2: Person A to Person B to Person A

The blank just doesn't fit into either one of those scenarios, but is a legit row.

I was using Summarize by grouping on those values and taking Min TMR_Created Date and Max TMR Date Removed to fix Scenario 1, but that messed up Scenario 2; which should not be changed.

[csh8428 · Accepted solution]

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.