Multi Row Formula Row Aggregation

[D_Sluss]

Hello, I am wondering how I can better aggregate my rows.  I thought I had a clever idea by using the summarize tool and concatenating my reference numbers, but since every possible combination did not exist in the record set, I ended up getting this result.  Is there a way turn the current record below to the desired record systematically.  I thought that using the multi row formula could do this task, but I cannot make it work.

Current Record:			Desired Record:
Control#	Policy#		Control#	Policy#
A1234;Z4569	1ABCD		A1234;Z4569	1ABCD;9WXYZ
Z4569	1ABCD;9WXYZ

[Thableaus]

Hi @D_Sluss 

Could you please provide a more complete input data and a desired output?

Still not so clear what you're trying to do here.

Cheers,

[danilang]

Hi @D_Sluss 

Here's one way to get the output you're looking for

Start by transposing your data to get it all in one pair of columns.  Split the values in rows using ";" as the delimiter and get the the unique name/value pairs.  Cross tab to get the Control and Polciy back as column headers, concatenating the values and finally rename the the columns to get rid of the "_" that the Cross Tab tool puts in, resulting

 

This works for your sample data producing exactly the output you want, but as @Thableaus mentioned, we need more detail to create more generic solution.  i.e. do you always want the output on one row, are there any other Key columns that need to be accounted for, etc

Dan 

[D_Sluss]

Sorry, for the vague initial post.  I did not know exactly how to word.  I completed a more thorough sample in the below database.  I had to scramble the data quite a bit since it is sensitive data.  I added a column called 'unique' which represents the level to which I am trying to condense the records (1 record by letter a-j).  I added this 'unique' column manually so I do not have this available to group the data in the full database.  As you can see the names are not reliable as a key even with fuzzy match. 

So basically, the only way I have to consolidate the records is by comparing policy to get all med id's, look up the med id's to get all the polices, and then look up the policies again to get all the med ids.  Eventually grouping into one record.  However, I am not sure how to do this.

Again, sorry again for the inadequate explanation, but I am hoping my sample data will make things clear. 

[D_Sluss]

I think I figured out a way to identify unique records when two sets of reference numbers are being updated periodically at different times.  This workflow will likely not work 100% of the time, but it handled all the exception records that I had identified.  It condensed the sample data that posted previously into the 9 unique records that I wanted to end up with.

Please let me know if you think of a better solution.

[MinaGO3 · Accepted solution]

How about this solution using Make Group? 

[D_Sluss]

This is great.  Thanks for the solution.  My solution was way too round-about for me to be comfortable with it.

[D_Sluss]

Just tried it in my workflow and it reduced my groups from 46,942 to 46,936, so it is more accurate as well as being more efficient.  Thanks again for the great solution.