Work with Duplicates

[angeline559]

I have a data set where a few of the records have some matching data.  I need to be able to isolate these records together so that I can apply some additional formulas to adjust one of the fields.  Using the Unique tool doesn't accomplish this task as it sends one of any found duplicates to the U output.  I need the duplicates to remain together.

In this sample data set, I need to find duplicates where the Item, Dept, WHSE, and OnHand values match.

Site	Item	Dept	WHSE	OnHand	Min	Max
Site A	1001	20	SAC	241920	886	1323
Site B	1001	20	SAC	241920	68416	103851
Site A	3001	130	SAC	16235	422	630
Site C	3001	130	SAC	16235	24401	36021
Site B	3001	130	SAC	16235	19863	30150
Site A	2001	10	SAC	85050	2254	3365
Site A	4001	15	SAC	12600	3190	4709

Once I have the matching entries, I need to calculate the percent of the sum for the min value for each like group.  The sample here is two lines, but it could vary up to four.  The Onhand qty in the data set is a sum from a previous step.  I need to calculate a new Onhand qty using the percent of min. 

Site	Item	Dept	WHSE	OnHand	Min	Max
Site A	1001	20	SAC	241920	886	1323
Site B	1001	20	SAC	241920	68416	103851

Needed Result: 

Site	Item	Dept	WHSE	OnHand	Min	Max
Site A	1001	20	SAC	3093	886	1323
Site B	1001	20	SAC	238827	68416	103851

[T_Willins · Accepted solution]

Hi @angeline559,

I attached a workflow that is the beginning of this process that will identify the matching groups, but I am unsure on the calculation you want to perform on each group.  I think you will want a Summarize tool (group by Group, minimum of Min) and a Formula tool to calculate a percentage that can then be joined back to the complete data by group.  Please provide additional information on the calculation if you need assistance completing this workflow.

[angeline559]

@T_Willins  Thank you!.  That worked out perfectly.  From there I was able to summarize by the group to get the values and percentages I needed.to apply.