Counting Number of Unique Values in a List based on Criteria

I have found many solutions, both via formulas and through use of VBA, on the net for counting the number of unique values in a list.

I want to do a count of unique values based on a criteria such that with a dataset including SiteCode and PropertyType, I want to know how many unque PropertyTypes there are with a specified SiteCode sitting alongside them.

The PropertyTypes are strings rather than numbers and the solution will also need to cope with blanks and not count a blank as a unique value.

My current dataset is a little over 1,000 records with 5 different SiteCodes but I will need to extend this to circa 40,000 records with circa 500 different SiteCodes.

Given the potential future size of the dataset, the speed of calculation will also be a consideration.

I wondered if the new "Remove Duplicates" facility on the ribbon could be used within a macro to create a temporary list either within a VBA variable or on a temporary sheet and then count the number of items in that list?

Any pointers would be much appreciated.

Thanks