Timme,
In response to your question... "Is it possible to use a faster function?"...
Yes. Excel's "database functions" are extremely powerful and indeed can and should be used with your type of application.
They are "powerful" in two main ways.
1) Flexibility - in terms of being able to specify "precise criteria" for the type of "data analysis" required, and the criteria can be "as elaborate as is necessary".
2) Speed - The database formulas and database extraction and filter functions are "custom" functions. Being custom functions, they are written in the "C" language, making them MUCH faster than "looping through records with VBA".
I've "taken up the challenge" to make the case that Excel users should seriously consider this powerful component of Excel.
Based on your descriptions, I've created a model that I believe achieves the objective you've described. It takes into account the fact that one column can have names that do NOT exist in the OTHER column, and vice-versa. It generates a total list of UNIQUE names from both columns, and deducts the names that appear in one column but not the other, and of course the percentages.
You mention that you want to compare TWO columns, but you also mention that you have a total of 256 columns (and 100,000 records). I gather than you intend to compare two- columns-at-a-time, and that you intend to break up the number of rows - to allow for Excel's maximum of 65,536 rows.
In the model I've developed, I've populated two columns from A1:A65536, and B1:B65536.
Using a combination of the database formulas an extraction capability - which I've attached to a "macro button", it takes exactly 1 second to produce the results for the two columns. And my PC is only a Pentium 2 - 300 Mhz.
So at this rate, my calculation is that it will take approximately "2 minutes" to generate the results for 256 columns. This is of course based on only the 65536 row maximum, and it does not take into account the time it will take you to copy the columns from your worksheet into the "template" worksheet I've created for you.
If you'd like the file, or if anyone else is interested, please email me - but please provide a specific reference. As I've been offering other files, it'll be difficult to know which file you're asking for without such a reference.
I hope this helps.
With the holiday coming up, I'll leave my HOME address as well.
Regards, ...Dale Watson
WORK: dwatson@bsi.gov.mb.ca
HOME: nd.watson@shaw.ca