counting unique values seperate by a comma

[junkforhr]

I have a spreadsheet which has in one column a list of offices in Column H. In Column K there is a lst of locations. In these cells the 'location' has a value of for example ( abcdb 1234), however in a cell there may be more than one value eg (abcd 1234, xxxxx 3241,). I need a macro or formula to count the number of locations per office and also a macro or formula to count the number of unique locations per office and the number of unique locations overall. A small example of the spreadsheet. This spreadsheet can have thousands of rows.Any help/guidance is greatly appreciated.

<table style="border-collapse: collapse; width: 225pt;" border="0" cellpadding="0" cellspacing="0" width="301"><col style="width: 80pt;" width="107"> <col style="width: 145pt;" width="194"> <tbody><tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt; width: 80pt;" height="19" width="107">Column H</td> <td class="xl64" style="width: 145pt;" width="194">Column K</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">Office 1</td> <td class="xl64">abcs 1234</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl66" style="height: 14.4pt; width: 80pt;" height="19" width="107">office 2</td> <td class="xl64">locationa 2435, locationb 6521</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">Office 3</td> <td class="xl64">abcs 1234</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl66" style="height: 14.4pt; width: 80pt;" height="19" width="107">Office 4</td> <td class="xl64">locationa 2435, locationb 6522</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">Office 5</td> <td class="xl64">abcs 1236</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl65" style="height: 14.4pt;" height="19">Office 1</td> <td class="xl64">locationa 2435, locationb 6522</td></tr></tbody></table>

 

[junkforhr]

thank you Kris. I have shown the results to my team and we are happy. There has been another development. There is now another worksheet with similar details to the worksheet that contains the Office and Locations columns.The new worksheet also contains columns which have Office and Locations. They now require the counts to include this new worksheet as well. Can your function be modified to accommodate this request?

 

[Krishnakumar]

Hi,

Apply the formula on both the sheets and sum the results.

 

[junkforhr]

Hi,

Apply the formula on both the sheets and sum the results.

Hi Kris, thanks for your help so far.I have another problem to solve, very similar my previous problems with a few more twists. I have a column with office ( as in my previous examples), but I now have a new columm with text in the following format: Test The Text -Testing. The cell can contain just text like this but it can also contain multiple values. These values are separated by a ;
So the cell will look something like Test The Text -Testing;Test The Text -Temptingng;Tept The Teht -Westing
NOt only do I need to count each occurence, but I then need to have a count of each one. I can have these values referred to in a cell if that makes it easier. Sorry if I have not been clear.