Number of characters by which two strings differ.

[milovnik]

Hello.

I need to compare two string and find out the number of characters by which they differ. The strings will be of identical length. The code should be as fast as possible as I will have to compare tens of thousands of strings. Let's say the string will be in a sheet in column A. The output could be probably a matrix in a csv format.

I'm still a beginner to VBA. This is how I would approach it.
1. Read the strings into a list/array
2. Iterate over the strings and compare string i to j.
3. To get the number of different characters I would probably split both strings into single letters and compare the letters at each position separately.

I wonder whether there is a more elegant/faster way to do it.

Thanks for any advice in advance.
Peter

[Pauleyb]

Attached is a possible method using just Excel functions. I made some assumptions on your strings - I wasn't sure if it was two separate lists or the same list (since you mention column A). Also, since you mention a matrix, it sounds like you want to compare each string in list A with each string in list B.

The benefit of my solution is that it uses the Excel functions which are almost always faster than writing similar tasks in VBA.

The problem with my solution is that you mention thousands of strings. Since I am using an array function, it will still take some time. I would also recommend you disable the auto-calculation, since you probably do not want it to perform this long operation every time a cell gets updated. And, while I have never encountered it, too many array functions has a reputation of crashing excel.

For a VBA solution, you are on the right track. You might want to consider doing an XOR on each character instead of a subtraction as that may run a bit faster. But, you essentially have the base idea for your code. Just a couple of loops grinding out the character comparison and adding up the ones that match.

[milovnik]

Hi Pauley.
Wow, this is a very nice formula.
Just a comment to my problem description. I have only one list of strings and I need to compare each string to all the other strings.
I'm looking for the number of different characters in two strings, so I've modified your formula accordingly: len(string)-your_formula

Now to my test of the formula.
First, I have "copy-transposed" my list (located in column A, starting in A2) to the 1st row (starting in B1). Here is one limitation. The max number of strings will be 16'384, as this is the max. columns limitation in excel, right?. Then I applied the formula:
- list of 7 strings => perfect
- list of 1400 strings => still OK, takes some time, of course, but is pretty fast, I think
- list of ca.11'000 strings => excel at its limits (auto-calculation turned off), producing various messages - cannot finish the job, cannot access the save folder. I think, it calculated the table, actually, but cannot handle the file.

I'll probably try to make some VBA script which produces directly a text file.
Anyway, your solution is so easy and is doing so much. Thanks.
Peter

[milovnik]

Hi all.
Attached is my solution using VBA I reached until now.

Short recapitulation.
I have a list of strings of an equal length which I need to compare. More precisely, I need to find out the number of different characters
between each of the two strings. Pauley suggested a nice formula using the sumproduct worksheet function. This function is problematic, however, if
the list exceeds a few thousand of strings. I thus tried to convert it into a VBA solution which should export the result into a csv file at the end.

The script is calculating the cross-comparison matrix for all the strings and stores it in a 2D array. But I got stacked at two issues.
1) I need to export/write/print the 2D array into a csv file, row by row. I've tried a couple of things without a success.

2) I'm not able to make the script dynamic in respect to the length of the strings and number of strings in the list.
For me it is fine if the len(string) info comes either from cell A1 or via an InputBox.
I imagine the size of my 2D array being connected to the last used row value.

Please Login or Register  to view this content.

Thanks for any suggestions.
Peter