Hi Everyone, hope I can get help for this problem.
I have a workflow which looks for duplicates in records. I then use the grouping tool so I have single set's of duplicates to look at to ensure we do not mark the same record to both keep and deactivate.
This results in this for example. As you can see there can be any number of matches from 1 to sometimes over 10.
| Group | Key |
| 6000000002 | 6500002038 |
| 6000000002 | 6500004673 |
| 6000000003 | 6500001977 |
| 6000000004 | 6500002028 |
| 6000000005 | 6500002034 |
| 6000000008 | 6500002031 |
| 6000000008 | 6500005622 |
| 6000000018 | 6000000701 |
| 6000000018 | 6000027351 |
| 6000000018 | 6500005486 |
| 6000000018 | 6500005715 |
In order to run the rest of my workflow to select a single survivor for each set of duplicates, I need to transpose the data to look like this where there is a single row for each group with the values filling each column.
| Group | Key 1 | Key 2 | Key 3 | Key 4 |
| 6000000002 | 6500002038 | 6500004673 | | |
| 6000000003 | 6500001977 | | | |
| 6000000004 | 6500002028 | | | |
| 6000000005 | 6500002034 | | | |
| 6000000008 | 6500002031 | 6500005622 | | |
| 6000000018 | 6000000701 | 6000027351 | 6500005486 | 6500005715 |
I have tried the cross tab function, but the only way I can think of to get that to work is to assign a ID # (which is essentially a count) to each match so it would look like this where the count restarts for each group but I am unsure what tool to use to do this.
| Group | Key | Key ID |
| 6000000002 | 6500002038 | 1 |
| 6000000002 | 6500004673 | 2 |
| 6000000003 | 6500001977 | 1 |
| 6000000004 | 6500002028 | 1 |
| 6000000005 | 6500002034 | 1 |
| 6000000008 | 6500002031 | 1 |
| 6000000008 | 6500005622 | 2 |
| 6000000018 | 6000000701 | 1 |
| 6000000018 | 6000027351 | 2 |
| 6000000018 | 6500005486 | 3 |
| 6000000018 | 6500005715 | 4 |
I also tried the transpose tool to no avail. Any Ideas?
