Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Find unique value from a column with multiple entries and count them

5 - Atom

This is what I have:


ACompany 1; Company 2 LLC
BCompany 2 inc
CCompany one
DCompany 1 international; Company 3; C2
ECompany 3


This is the outcome I want


CompaniesNumber of actions
Company 12
Company 22
Company 32


Problem 1: Find unique values from the column "companies"

Problem 2: Identify names that were spelled incorrectly/ in different ways, but actually belong to the same company

Alteryx Certified Partner
Alteryx Certified Partner

Firstly, you want to use the text to columns tool to make sure there is a single company in each row. You need to use ; as the delimiter and use 'Split to rows' mode, this will bring your data to look something like...


ACompany 1
ACompany 2 LLC
BCompany 2 inc
CCompany one
DCompany 1 international
DCompany 3
ECompany 3


To get the count you can then use the summerize tool, grouping by the companies field and also performing a count against the companies field.


Now as you mentioned, there are issues with your company details, to resolve this there is a couple of approaches you could take, one automated, one a bit more manual; personally, I would apply a manual method to suppress common extensions like 'inc', 'LLC' and 'international'. This can be done via a mapping table and the 'find and replace' tool.


Alternatively, a more automated approach, would be through using the fuzzy matching tool; a good starter blog for this tool is here:





12 - Quasar

To identify the same company name spelled differently, check out the Fuzzy Match tool



After you've matched similar company names, use the Summarize tool to count the actions.