Here’s the problem: I have a download of hundreds of companies organized by Zip codes. I also have a target company, which by definition has at least one branch in each of the zip codes. The other listed companies may or may not have branches in each of the zip codes, and they may have multiple branches in each of the zip codes. This means that the other listed companies may (and mostly do) appear more than once in the download, even within the same zip. However, there may be only a couple of hundred unique companies within this list, which I can get by Data==>Filter==>Advanced Filter==>Unique Records Only. What I need to do is to count the number of each company within each zip and compare that number to the number of the target companies within each zip. I then want the LOWER of the two numbers in each case. Finally, I need to total (or aggregate) the numbers for each company for all zips. Whew!
I already know that I can do this (or most of this) in a pivot table, but this task is part of a larger model and the pivot table solution doesn’t fit what is needed for the final report. So I am looking for either a formula or VBA code that will let me get those numbers and enter them in a table.
I have attached a sample file that shows the target company (B2), the entire list of companies by zip (A3:B1278) and the list of unique companies (D3:D252).
