Counting Combinations within a Cell

[Tom G]

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

 

[Tom G]

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.

 

[PapaDos]

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

 

[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

 

[PapaDos]

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 ?

 

[Tom G]

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

 

[Tom G]

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

Tom

Anybody?

 

[Dana DeLouis]

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

 

[Tom G]

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

 

[Tom G]

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

 

[Tom G]

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

OK, let's start over. All I need is a way to count matching numbers.

A1 A2

2,3,4 2,3,4,5

There are three matching numbers in the two cells. (2 3 and 4) Is
there a formula to count those matches?

 

[Dana DeLouis]

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

Hi. No. It's a vba code. Here's what you do.
From the worksheet, do Alt+F11. This brings up the vba editor.
From the editor, do "Insert - Module" from the Main Menu.
Paste the code here.

Function CountUnique(s1 As String, s2 As String) As Double
Dim V1 As Variant
Dim V2 As Variant
....etc
CountUnique = N
End Function

Now, back on your worksheet, enter the following into a cell:

=CountUnique(A1,B1)

Make sure A1 & B1 have strings similar to your example (ie "1,2,3,4")
Don't hesitate to post back if you have questions, especially if it's your
first macro.
Again, this was a quick example to get you going.

 

[Tom G]

Hi. No. It's a vba code. Here's what you do.
From the worksheet, do Alt+F11. This brings up the vba editor.
From the editor, do "Insert - Module" from the Main Menu.
Paste the code here.

Function CountUnique(s1 As String, s2 As String) As Double
Dim V1 As Variant
Dim V2 As Variant
...etc
CountUnique = N
End Function

Now, back on your worksheet, enter the following into a cell:

=CountUnique(A1,B1)

Make sure A1 & B1 have strings similar to your example (ie "1,2,3,4")
Don't hesitate to post back if you have questions, especially if it's your
first macro.
Again, this was a quick example to get you going.

This is a great work around for what I wanted. It worked great. I
had go up a few posts and grab the whole code, becuse I got a syntax
error, but I figured it out right away... and it is very slick.

Thanks a lot for your time..

Tom

 

[Harlan Grove]

PapaDos wrote...

It makes no difference, the difficulty is in extracting items from a string,
not the logic.

What is your use of that "combination count" ?
I just want to be sure you really need this...

....

Not that difficult. Helps to have a defined name like seq referring to
something like

=ROW(INDEX(Sheet2!$1:$65536,1,1):INDEX(Sheet2!$1:$65536,64,1))

so that seq evaluates to a 1D array of sequential integers. Then if
1,2,3 were in A1 and 1,3,4,5,7,9 were in B1, the following array
formula would return the number of crossproduct terms with distinct
entries.

=SUM(IF(seq-2<LEN(A1)-LEN(SUBSTITUTE(A1,",","")),
LEN(B1)-LEN(SUBSTITUTE(B1,",",""))+1-
ISNUMBER(FIND(","&MID(A1,SMALL(IF(MID(","&A1,seq,1)=",",seq),seq),
SMALL(IF(MID(A1&",",seq,1)=",",seq),seq)-SMALL(IF(MID(","&A1,seq,1)=",",seq),seq))&",",
","&B1&","))))

 

[Tom G]

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

Dana,

Could this same code be slightly changed for doing the same thing with
three cells? And then four cells? That will complete my project.

In other words, I already have what I need for two cells, now I need
to do the same thing for three and four cells.

EX: For three cells:

A6 B6 C6
2,3,4 4,5,6 2,3,4,5,6

EX: For four cells:

A10 B10 C10 D10
1,2 1,2,3 2,3,4,5 6,7,8,9,10

Thanks for your help.

Tom

 

[PapaDos]

Interesting technique !

Wouldn't ROW($1:$64) do the same as "seq" ?

I used this technique for the following FORMULA ARRAYS:

NOTES:
- The word SPACE should be replaced by a space character. I did this to
avoid the "line wrapping" problem...
- My formulas are working for up to 25 numbers per cell, adjust the
ROW($1:$25) occurences for more or less...


- Good for 1 or 2 digits numbers separated by commas and/or spaces:

=COUNT( 1 / ( MID( TRIM( SUBSTITUTE( B4, ",", "SPACE" ) ) & "SPACE", FIND(
"=", SUBSTITUTE( "SPACE" & TRIM( SUBSTITUTE( B4, ",", "SPACE" ) ), "SPACE",
"=", ROW($1:$25) ) ), 2 ) <> TRANSPOSE( MID( TRIM( SUBSTITUTE( C4, ",",
"SPACE" ) ) & "SPACE", FIND( "=", SUBSTITUTE( "SPACE" & TRIM( SUBSTITUTE( C4,
",", "SPACE" ) ), "SPACE", "=", ROW($1:$25) ) ), 2 ) ) ) )


- Good for multi-digits numbers separated by commas and/or spaces:

=COUNT( 1 / ( MID( TRIM( SUBSTITUTE( A1, ",", "SPACE" ) ), FIND( "=",
SUBSTITUTE( "SPACE" & TRIM( SUBSTITUTE( A1, ",", "SPACE" ) ), "SPACE", "=",
ROW($1:$25) ) ), FIND( "=", SUBSTITUTE( TRIM( SUBSTITUTE( A1, ",", "SPACE" )
) & "SPACE", "SPACE", "=", ROW($1:$25) ) ) - FIND( "=", SUBSTITUTE( "SPACE" &
TRIM( SUBSTITUTE( A1, ",", "SPACE" ) ), "SPACE", "=", ROW($1:$25) ) ) ) <>
TRANSPOSE( MID( TRIM( SUBSTITUTE( B1, ",", "SPACE" ) ), FIND( "=",
SUBSTITUTE( "SPACE" & TRIM( SUBSTITUTE( B1, ",", "SPACE" ) ), "SPACE", "=",
ROW($1:$25) ) ), FIND( "=", SUBSTITUTE( TRIM( SUBSTITUTE( B1, ",", "SPACE" )
) & "SPACE", "SPACE", "=", ROW($1:$25) ) ) - FIND( "=", SUBSTITUTE( "SPACE" &
TRIM( SUBSTITUTE( B1, ",", "SPACE" ) ), "SPACE", "=", ROW($1:$25) ) ) ) ) ) )

 

[Harlan Grove]

PapaDos wrote...
....

Wouldn't ROW($1:$64) do the same as "seq" ?

....

It would until you start inserting or deleting rows.

 

[Harlan Grove]

Tom G wrote...
....

Could this same code be slightly changed for doing the same thing with
three cells? And then four cells? That will complete my project.

In other words, I already have what I need for two cells, now I need
to do the same thing for three and four cells.

EX: For three cells:

A6 B6 C6
2,3,4 4,5,6 2,3,4,5,6

EX: For four cells:

A10 B10 C10 D10
1,2 1,2,3 2,3,4,5 6,7,8,9,10

....

It could, but there comes a point at which spreadsheets and procedural
programming languages simply aren't the best tools for the task. Your
task is equivalent to generating generalized crossproducts and counting
up the tuples in which all entries are distinct values. There are other
software packages that'd be much better at this.

However, if you must do this in Excel, you could try the following udf.


Function foo(ParamArray a() As Variant) As Long
Dim i As Long, j As Long, k As Long
Dim b As Variant, c As Variant, d As Variant
Dim x As Variant, y As Variant

For Each x In a
If Not IsArray(x) Then x = Array(x)

For Each y In x

If IsEmpty(b) Then
b = Split(y, ",")

Else
c = Split(y, ",")
ReDim d(0 To (UBound(b) + 1) * (UBound(c) + 1) - 1)
k = -1

For i = 0 To UBound(b)

For j = 0 To UBound(c)

If InStr(1, "," & b(i) & ",", "," & c(j) & ",") = 0 Then
k = k + 1
d(k) = b(i) & "," & c(j)
End If

Next j

Next i

ReDim Preserve d(0 To k)
b = d
Erase c
Erase d

End If

Next y

Next x

foo = UBound(b) + 1

End Function

 

[Frank Price]

It could, but there comes a point at which spreadsheets and procedural
programming languages simply aren't the best tools for the task. Your
task is equivalent to generating generalized crossproducts and counting
up the tuples in which all entries are distinct values. There are other
software packages that'd be much better at this.

However, if you must do this in Excel, you could try the following udf.


Function foo(ParamArray a() As Variant) As Long
Dim i As Long, j As Long, k As Long
Dim b As Variant, c As Variant, d As Variant
Dim x As Variant, y As Variant.....

I understand. Thank you I'll try this.

Tom