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 |
Solved! Go to 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.
@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.