Duplicate company names in pivot table

[TONYJIRVINE]

Hi, I am working on a report for my company. The remit is to check how many job references there are for each customer for each salesperson. The problem I have is the data quality where the same company has numerous instances of their name. A sample of this is below -

GCP APPLIED TECHNOLOGIES	655​	655​
GCP APPLIED TECHNOLOGIES (UK) LIMIT	54​	54​
GCP APPLIED TECHNOLOGIES UK LTD	328​	315​

This is all the same company so I need the totals to be grouped and one name to be used to give the total of 1037 in this case. An additional complexity is if you have another company i.e. GCP APPLIED TECHNOLOGIES GMBH it would need to be shown as a different entity with its own totals. Any ideas on how to do this?

Tony

 

[pjmorris]

In Power Pivot specifically I dont think you would be able to do so. Importing the data using Powerquery allows you to group the data, but you'd still probably need a separate table to identify acceptable groupings. Not easy, but possible.