Fast way to compare strings

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

test - 04 Apr 2007 12:26 GMT

Hello all,

Which is the fastest way to compare the strings below,
the following way:

Each of MyStrings shall be compared for the quantity of N 1's vs all of
the Main strings on the same position!

1) All strings are of equal size and have equal number of 1's (but in
different positions.
2) N represents the minimum quantity of matches between MyStrings vs
Main strings.
3) In this example N=2

MyStrings: = 4
1000110
0010110
0011010
1100010

Main strings: = 35
1110000
1101000
1100100
1100010
1100001
1011000
1010100
1010010
1010001
1001100
1001010
1001001
1000110
1000101
1000011
0111000
0110100
0110010
0110001
0101100
0101010
0101001
0100110
0100101
0100011
0011100
0011010
0011001
0010110
0010101
0010011
0001110
0001101
0001011
0000111

The answer I'm looking for is example is 28.

That is, 28 strings (main strings) do have
minimum N 1's (2 in this case) on the same position as MyStrings.

TIA

Reply to this Message

Billy Liddel - 04 Apr 2007 14:16 GMT

> Hello all,
>
[quoted text clipped - 59 lines]
>
> TIA

Hi

I created Headers with mystrings, like so

    1000110    0010110    0011010    1100010    >2 count    Total
1110000    1    1    1    2    1    28
1101000    1    0    1    2    1
1100100    2    1    0    2    2
1100010    2    1    1    3    2
1100001    1    0    0    2    1

This used the range A10:G45
In B11 copy this formula
=SUMPRODUCT(--(MID($A11,{1,2,3,4,5,6},1=1)*MID(B$10,{1,2,3,4,5,6},1=1)))
copy this across to column f then down.

In F11 type the array formula =COUNT(IF(B11:E11>=$D$1,B11:E11)) and commit
it with Ctrl + Shift + Enter and copy down.

In G11 type =COUNT(IF(B11:E11>=$D$1,B11:E11))

In D1 I typed the minimum number of Dupes, in this case 2

Hope this helps

Peter

Reply to this Message

Billy Liddel - 04 Apr 2007 14:24 GMT

Ooops

I miscounted the number of digits the main formula should have been

=SUMPRODUCT(--(MID($A12,{1,2,3,4,5,6,7},1=1)*MID(B$10,{1,2,3,4,5,6,7},1=1)))

results are the same in this case

Peter

Reply to this Message

Rate this thread: