Unique Values in a List

[walkrebe]

Hi,

I am trying to get a count of how many unique applications there are for certain groups from the data below 

Frequency	Name	Application
Daily	A	Excel, PowerPoint
Daily	A	Excel
Daily	A	Alteryx, Excel
Monthly	A	Excel, Word
Monthly	A	PowerPoint
Daily	B	Excel
Daily	B	Excel

I want to count the number of unique applications grouped by frequency and name e.g like below - any suggestions of how I could do this please?

Frequency	Name	Number of Applications
Daily	A	3
Daily	B	1
Monthly	A	3

[hellyars]

@walkrebe 

Here is a potential solution.

[hellyars]

@walkrebe 

I added a Select tool at the end to change Count to Number of Applications. 

The first Summary tool combines all the Applications for a given Frequency and Name.  Since they are separated by a comma "," I used a Text to Columns set to split to rows using the "," delimiter.  A Cleanse tool is necessary to get rid of extra white space, etc.  A Unique Tool filters out the duplicate values.  The second Summary tool then counts the number of unique applications associated with any Frequency+Name pair. 

[apathetichell]

Here's the split into rows - summarize/count - data cleanse solution.

[walkrebe]

thanks - what action did you select in the summarise tool? 

[apathetichell]

group by frequency. group by name. count distinct application.

that's after splitting application and datacleansing so " powerpoint" ="powerpoint"

[walkrebe]

What about in the first summarise? 

[apathetichell]

 

@hellyarsused a slightly different approach than me - that worfklow concatenates all values into one list (via summarize tool) - and then breaks down  - whereas I started by breaking down the values. The key elements are separating to rows by comma, data cleansing and then using a summarize. I used count/distinct in my workflow @hellyars uniqued and then counted - again both are totally solid ways to do.

Both results should work.