Workflow setup, combining files

[TheSAguy]

Hi,

I have several files that I'd like to combine and concatenate a common field. 

Here is my input and desired output:

IN:

File 1
Unique Key	Campaign
100	Campaign A
101	Campaign A
102	Campaign A
107	Campaign A
File 2
Unique Key	Campaign
100	Campaign B
101	Campaign B
105	Campaign B
110	Campaign B
File 3
Unique Key	Campaign
101	Campaign C
107	Campaign C

Output:

Unique Key	Campaigns	Number of Campaigns
100	Campaign A, Campaign B	2
101	Campaign A, Campaign B, Campaign C	3
102	Campaign A	1
105	Campaign B	1
107	Campaign B, Campaign C	2
110	Campaign B	1

As you can see from above, the unique key that had more than one Campaign on it got grouped together and also a count of the number of campaigns per key.

Is there an easy way to accomplish this?

Thanks.

[steven_king · Accepted solution]

Hi @TheSAguy , the Summarize tool can solve this problem quickly. Combine the data with the Union tool and then configure a Summarize tool like the screenshot below. 

[lynnesonneyrsm · Accepted solution]

"Easy" is dependent on a few things. I've put together two solutions, one where the headers for each of the file 'sections' is consistent, one where they move around.

Please let me know if this helps!

[TheSAguy]

Great,

I've never used the concatenate on the Summarize tool.

Thanks for showing me!

[lynnesonneyrsm]

One thing to note is that the Summarize tool concatenate function defaults to comma delimited, but can be changed. Additionally, it does not include spaces after the delimiter so I usually follow mine up with a replace([Field],",", ", ") in a formula tool for reader friendliness.