This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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...
Company 2 LLC
Company 2 inc
Company 1 international
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.
To identify the same company name spelled differently, check out the Fuzzy Match tool
This example matches your need: In the Alteryx Designer, open the sample under Help > Sample Workflows > Learn one tool at a time > Join > Fuzzy Match. Look at the examples in the section for "Merge Mode vs. Purge Mode"