Hi,

In cells A1 and B1 I have the following data:

4,5,6,7 4,5,6,7

In B3 I want to count the number of non-matching combinations.

The answer is 12.

There are 16 total combinations, but 4 cannont be matched with 4, and
5 cannot be matched with 5, etc., which leaves 12.

I'm looking for one formula that can count the number of non-matching
combinations.

Thanks,

Tom G

On Mon, 18 Dec 2006 02:35:57 -0700, Tom G <(E-Mail Removed)> wrote:

>Hi,
>
>In cells A1 and B1 I have the following data:
>
>4,5,6,7 4,5,6,7
>
>In B3 I want to count the number of non-matching combinations.
>
>The answer is 12.
>
>There are 16 total combinations, but 4 cannont be matched with 4, and
>5 cannot be matched with 5, etc., which leaves 12.
>
>I'm looking for one formula that can count the number of non-matching
>combinations.
>
>Thanks,
>
>Tom G
>

No, the strings aren't always identical length. It can vary widely.
Sometimes one will have three and the other one, or any amount up to
twelve each max.

Are the entries alway one character length ?
Are the entries always unique ?

--
Regards,
Luc.

"Festina Lente"


"Tom G" wrote:

> On Mon, 18 Dec 2006 02:35:57 -0700, Tom G <(E-Mail Removed)> wrote:
>
> >Hi,
> >
> >In cells A1 and B1 I have the following data:
> >
> >4,5,6,7 4,5,6,7
> >
> >In B3 I want to count the number of non-matching combinations.
> >
> >The answer is 12.
> >
> >There are 16 total combinations, but 4 cannont be matched with 4, and
> >5 cannot be matched with 5, etc., which leaves 12.
> >
> >I'm looking for one formula that can count the number of non-matching
> >combinations.
> >
> >Thanks,
> >
> >Tom G
> >
>
> No, the strings aren't always identical length. It can vary widely.
> Sometimes one will have three and the other one, or any amount up to
> twelve each max.
>
>

On Mon, 18 Dec 2006 03:37:26 -0700, Tom G <(E-Mail Removed)> wrote:

>On Mon, 18 Dec 2006 02:35:57 -0700, Tom G <(E-Mail Removed)> wrote:
>
>>Hi,
>>
>>In cells A1 and B1 I have the following data:
>>
>>4,5,6,7 4,5,6,7
>>
>>In B3 I want to count the number of non-matching combinations.
>>
>>The answer is 12.
>>
>>There are 16 total combinations, but 4 cannont be matched with 4, and
>>5 cannot be matched with 5, etc., which leaves 12.
>>
>>I'm looking for one formula that can count the number of non-matching
>>combinations.
>>
>>Thanks,
>>
>>Tom G
>>
>
>No, the strings aren't always identical length. It can vary widely.
>Sometimes one will have three and the other one, or any amount up to
>twelve each max.

Are the entries alway one character length ?
Are the entries always unique ?

--
Regards,
Luc.

The entries will always be a number between 1 and 12, seperated by
commas. I'm not sure what you mean by unique.

EX:

1,2,3 1,3,4,5,7,9

1,4 2,5,6,11

1 4,7,8,9

2,3 2,3,7,8,9

These are examples of how the cells might look. Sometimes there are
matching values, and other times there are not.

Thanks,

Tom

By "not" unique, I mean that it is possible that an entry is repeated more
than once in a cell, like in (1,1,2,3).
Is it a possible situation ?

--
Regards,
Luc.

"Festina Lente"


"Tom G" wrote:

> On Mon, 18 Dec 2006 03:37:26 -0700, Tom G <(E-Mail Removed)> wrote:
>
> >On Mon, 18 Dec 2006 02:35:57 -0700, Tom G <(E-Mail Removed)> wrote:
> >
> >>Hi,
> >>
> >>In cells A1 and B1 I have the following data:
> >>
> >>4,5,6,7 4,5,6,7
> >>
> >>In B3 I want to count the number of non-matching combinations.
> >>
> >>The answer is 12.
> >>
> >>There are 16 total combinations, but 4 cannont be matched with 4, and
> >>5 cannot be matched with 5, etc., which leaves 12.
> >>
> >>I'm looking for one formula that can count the number of non-matching
> >>combinations.
> >>
> >>Thanks,
> >>
> >>Tom G
> >>
> >
> >No, the strings aren't always identical length. It can vary widely.
> >Sometimes one will have three and the other one, or any amount up to
> >twelve each max.
>
> Are the entries alway one character length ?
> Are the entries always unique ?
>
> --
> Regards,
> Luc.
>
> The entries will always be a number between 1 and 12, seperated by
> commas. I'm not sure what you mean by unique.
>
> EX:
>
> 1,2,3 1,3,4,5,7,9
>
> 1,4 2,5,6,11
>
> 1 4,7,8,9
>
> 2,3 2,3,7,8,9
>
> These are examples of how the cells might look. Sometimes there are
> matching values, and other times there are not.
>
> Thanks,
>
> Tom
>
>

On Mon, 18 Dec 2006 04:33:00 -0800, PapaDos
<(E-Mail Removed)> wrote:

>By "not" unique, I mean that it is possible that an entry is repeated more
>than once in a cell, like in (1,1,2,3).
>Is it a possible situation ?
>
>--
>Regards,
>Luc.
>
>"Festina Lente"

OK, I see. No there will never be any repeats within a cell.

Tom

On Mon, 18 Dec 2006 11:18:37 -0700, Tom G <(E-Mail Removed)> wrote:

>On Mon, 18 Dec 2006 04:33:00 -0800, PapaDos
><(E-Mail Removed)> wrote:
>
>>By "not" unique, I mean that it is possible that an entry is repeated more
>>than once in a cell, like in (1,1,2,3).
>>Is it a possible situation ?
>>
>>--
>>Regards,
>>Luc.
>>
>>"Festina Lente"
>
>OK, I see. No there will never be any repeats within a cell.
>
>Tom

Anybody?

Hi. This doesn't have much error checking, but would this general idea
work?

=CountUnique(A1,B1)
12

Function CountUnique(s1 As String, s2 As String) As Double
Dim V1 As Variant
Dim V2 As Variant
Dim N As Double
Dim j As Long
Dim k As Long

'// Index is zero-based
V1 = Split(s1, ",")
V2 = Split(s2, ",")

'// Assume they are all unique
N = (UBound(V1) + 1) * (UBound(V2) + 1)
'// Subtract 1 if there is a match
'// (Note: True is -1 in vba)
For j = 0 To UBound(V1)
For k = 0 To UBound(V2)
N = N + (V1(j) = V2(k))
Next k
Next j
CountUnique = N
End Function


--
HTH :>)
Dana DeLouis
Windows XP & Office 2003


"Tom G" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Mon, 18 Dec 2006 11:18:37 -0700, Tom G <(E-Mail Removed)> wrote:
>
>>On Mon, 18 Dec 2006 04:33:00 -0800, PapaDos
>><(E-Mail Removed)> wrote:
>>
>>>By "not" unique, I mean that it is possible that an entry is repeated
>>>more
>>>than once in a cell, like in (1,1,2,3).
>>>Is it a possible situation ?
>>>
>>>--
>>>Regards,
>>>Luc.
>>>
>>>"Festina Lente"
>>
>>OK, I see. No there will never be any repeats within a cell.
>>
>>Tom
>
> Anybody?
>

On Tue, 19 Dec 2006 12:05:26 -0500, "Dana DeLouis"
<(E-Mail Removed)> wrote:

>Hi. This doesn't have much error checking, but would this general idea
>work?
>
>=CountUnique(A1,B1)
>12
>
>Function CountUnique(s1 As String, s2 As String) As Double
> Dim V1 As Variant
> Dim V2 As Variant
> Dim N As Double
> Dim j As Long
> Dim k As Long
>
>'// Index is zero-based
> V1 = Split(s1, ",")
> V2 = Split(s2, ",")
>
>'// Assume they are all unique
> N = (UBound(V1) + 1) * (UBound(V2) + 1)
>'// Subtract 1 if there is a match
>'// (Note: True is -1 in vba)
> For j = 0 To UBound(V1)
> For k = 0 To UBound(V2)
> N = N + (V1(j) = V2(k))
> Next k
> Next j
> CountUnique = N
>End Function

Hi, do I just enter all of that into a cell?

Tom

Maybe this would be simpler.

A1 A2

3,4,5 3,4,5,6

The number of unique combinations is 9. (4*3)-3.

If you multiply the number of digits in each cell (4 and 3) together,
and then subtract the number of matches, you have the number of
combinations.

In the above example you have 4*3=12. The 3,4 and 5 are duplicated in
both cells, so you would subtract the number of duplicatons (3) for an
answer of 9.

Perhaps it would be easier to write a formula that way?

Tom