Counting unique values across more than one column

**Lija** · 05-15-2013, 12:50 PM

Hey guys, I have a tricky question.

As you can see in the workbook I uploaded, i have three columns:
A - Name of a company
B - Name of the patent that company has registered
C - Name of inventors that have worked on that patent

For each company I usually have data for more than one patent.

What I need is a number of all inventors that have worked for a company, that is the number of all inventors listed on all of its patents.
And since inventors tend to work on more than one patent for a company, I need to count unique values only.

As names of the inventors for one patent are written next to each other in one cell, I started by separating the names into different columns. But that is where I got stuck. Do you have any ideas how to solve this? I thought to do it with a pivot table, but can't seem to figure it out.

Please note that this list is much smaller than the one I am working on. In the original data set the maximum number of inventors that is listed on one patent is 21.

Thank you in advance!

**loginjmor** · 05-15-2013, 04:13 PM

Hi -

Unfortunately, your data is very "dirty". I doubt you will be able to find unique values with multiple names in one string without doing some VBA programming. The other issue I see is there are names that are suspiciously similar but different enough to be identified by Excel as unique. For example in Row 67 you have "LOFQUIST R,US" but in Row 97 you have "Lofquist; Robert Alden". I suspect these are the same person, but I don't know that for certain. Also, the mixture of all caps versus cap lead is irritating, but not insurmountable.

In my opinion you have some data cleaning to do to separate all the names out into one column, duplicating the information associated with each name for the company and the patent. You also need consistency in how the names are reported so you don't have near duplicates as described above. Once you have one column of names in a consistent format, the following link provides an example of how to extract a unique list of names.

http://www.excelforum.com/excel-form...43#post3234043

Hope this helps.