Complicated Formula Problem

[BobA]

In three cells I have the following:

456 456 456

If I want to count the number of three digit combinations I simply multiply 3x3x3=27

445
456
464
666

All of these are examples of three digit combinations.

However there are only 6 unique combinations without any duplicates.

456
465
546
564
645
654

The numbers in the cells are always changing, but there are never more than three cells of data. For example they could be:

12 145 5678

In this example there are 24 total combinations with 6 duplicates or 18 unique combinations.

I want to be able to count the number of unique 3-digit combinations, or conversely count the number of duplicate combinations and simply subtract that from the total.

Thanks for any help


PS I can already do this with two digit combinations in two different cells, but I'm having trouble doing this for a three-digit number.

For example if I have:

345 2367

There are (4x3) or 12 total combinations and 1 duplicate (3,3) for 11 unique combinations.

Here is the formula counting the number of duplicate combinations:

=IF(I27="","",SUMPRODUCT(--(ISNUMBER(SEARCH(MID(T27,ROW(INDIRECT("x1:x"&LEN(T27))),1),U27)))))

 

[BobA]

I guess what I want to do can't be done algebraically. Either that or I worded it very poorly.

 

[joeu2004]

The numbers in the cells are always changing, but there are
never more than three cells of data. For example they could be:
12 145 5678 [....]
I want to be able to count the number of unique 3-digit
combinations

This breaks down into two problems:

1. Determine the number of unique digits ("n"; 7 in the above example: 1,
2, 4, 5, 6, 7, 8).

2. Determine the number of permutations of n digits taken k at a time (k =
3).

#2 is simple to do in Excel, namely: =PERMUT(n,k).

#1 is difficult to do in Excel. There might be some clever use FREQUENCY in
an array-entered formula.

But I would opt for the following VBA function that combines #1 and #2.
Usage:

Suppose A1 contains 12, A2 contains 145 and A3 contains 5678.
Then, =uniqCombo(3,A1:A3) returns 210.

The first parameter is the number digits (k) in the combined number. The
remaining parameters (up to 30 in XL2003; up to 254 in XL2007 and later) are
cell ranges containing the original numbers.

-----

Function uniqCombo(n As Long, ParamArray rng()) As Long
Dim i As Long, nd As Long
Dim c As Variant, r As Variant
Dim s As String, x As Long
Dim d(0 To 9) As Long
nd = 0
For Each r In rng
For Each c In r
s = c.Text
For i = 1 To Len(s)
x = Mid(s, i, 1)
If d(x) <> 1 Then d(x) = 1: nd = nd + 1
Next i
Next c
Next r
uniqCombo = WorksheetFunction.Permut(nd, n)
End Function

 

[joeu2004]

This breaks down into two problems:
1. Determine the number of unique digits ("n"; 7 in the above example: 1,
2, 4, 5, 6, 7, 8).

2. Determine the number of permutations of n digits taken k at a time (k =
3).

#2 is simple to do in Excel, namely: =PERMUT(n,k). [....]
Function uniqCombo(n As Long, ParamArray rng()) As Long [....]
uniqCombo = WorksheetFunction.Permut(nd, n)

To be consistent, I should have written:

Function uniqCombo(k As Long, ParamArray rng()) As Long
Dim i As Long, n As Long
Dim c As Variant, r As Variant
Dim s As String, x As Long
Dim d(0 To 9) As Long
n = 0
For Each r In rng
For Each c In r
s = c.Text
For i = 1 To Len(s)
x = Mid(s, i, 1)
If d(x) <> 1 Then d(x) = 1: n = n + 1
Next i
Next c
Next r
uniqCombo = WorksheetFunction.Permut(n, k)
End Function

I simply changed the name of "n" to "k" and "nd" to "n".

 

[joeu2004]

1. Determine the number of unique digits ("n"; 7 in the above example: 1,
2, 4, 5, 6, 7, 8).

2. Determine the number of permutations of n digits taken k at a time (k =
3). [....]
#1 is difficult to do in Excel. There might be some clever use
FREQUENCY in an array-entered formula.

In the spirit of full disclosure, I did not come up with a single
array-entered formula (press ctrl+shift+Enter instead of just Enter) that
combines #1 and #2:

=PERMUT(SUM(--(FREQUENCY(IF(ISNUMBER(--MID(A1:A3,
COLUMN(INDIRECT("a1:" & CHAR(96+MAX(LEN(A1:A3))) & "1")),1)),
--MID(A1:A3,
COLUMN(INDIRECT("a1:" & CHAR(96+MAX(LEN(A1:A3))) & "1")),1)),
{0,1,2,3,4,5,6,7,8,9})>0)),3)

FYI, that is limited to numbers in A1:A3 with 26 digits or less. <g>

Also, that is limited to Excel 2007 and later. The number of nested
function calls (10) is too much for Excel 2003.

Needless to say, I do __not__ recommend that one-liner. I still prefer the
VBA solution.

But if you must have an Excel-only solution, I would suggest the use of
helper cells.

For example, enter the following formula into B1, copy across through K1 and
down through B3:K3(where the number of columns in B1:K1 represents the
longest number you expect):

=IF(LEN($A1)>=COLUMNS($B$1:B1),--MID($A1,COLUMNS($B$1:B1),1),"")

Then, the number of permutations of unique digits is the following
array-entered formula (press ctrl+shift+Enter instead of just Enter):

=PERMUT(SUM(--(FREQUENCY(B1:K3,{0,1,2,3,4,5,6,7,8,9})>0)),3)

That works in Excel 2003 as well as Excel 2007 and later.

 

[BobA]

In three cells I have the following:



456 456 456



If I want to count the number of three digit combinations I simply multiply 3x3x3=27



445

456

464

666



All of these are examples of three digit combinations.



However there are only 6 unique combinations without any duplicates.



456

465

546

564

645

654



The numbers in the cells are always changing, but there are never more than three cells of data. For example they could be:



12 145 5678



In this example there are 24 total combinations with 6 duplicates or 18 unique combinations.



I want to be able to count the number of unique 3-digit combinations, or conversely count the number of duplicate combinations and simply subtract that from the total.



Thanks for any help





PS I can already do this with two digit combinations in two different cells, but I'm having trouble doing this for a three-digit number.



For example if I have:



345 2367



There are (4x3) or 12 total combinations and 1 duplicate (3,3) for 11 unique combinations.



Here is the formula counting the number of duplicate combinations:



=IF(I27="","",SUMPRODUCT(--(ISNUMBER(SEARCH(MID(T27,ROW(INDIRECT("x1:x"&LEN(T27))),1),U27)))))

Thanks for your help. Give me some time to work through this and I'll get back to you.

 

[BobA]

In three cells I have the following:



456 456 456



If I want to count the number of three digit combinations I simply multiply 3x3x3=27



445

456

464

666



All of these are examples of three digit combinations.



However there are only 6 unique combinations without any duplicates.



456

465

546

564

645

654



The numbers in the cells are always changing, but there are never more than three cells of data. For example they could be:



12 145 5678



In this example there are 24 total combinations with 6 duplicates or 18 unique combinations.



I want to be able to count the number of unique 3-digit combinations, or conversely count the number of duplicate combinations and simply subtract that from the total.



Thanks for any help





PS I can already do this with two digit combinations in two different cells, but I'm having trouble doing this for a three-digit number.



For example if I have:



345 2367



There are (4x3) or 12 total combinations and 1 duplicate (3,3) for 11 unique combinations.



Here is the formula counting the number of duplicate combinations:



=IF(I27="","",SUMPRODUCT(--(ISNUMBER(SEARCH(MID(T27,ROW(INDIRECT("x1:x"&LEN(T27))),1),U27)))))

As near as I can tell your formula gives the total number of permutations. What I need...

Think of a trifecta with the following ticket:

123
123
789

The 1,2 or 3 can come first or second but not third, and the 7,8 or 9 must come third. Obviously the 1 can't come first and second so 1,1,7 is invalid as is 7,2,3.

The correct number of combinations that I'm looking for is 18, not 120 as your formula gives.

 

[BobA]

(|A| × |B| × |C|) -

((|A| × |B ^ C|) + (|B| × |A ^ C|) + (|C| × |A ^ B|)) +

2 × |A ^ B ^ C|

You really sent me back to school on this one. I'm going to work with thisand see what I can come up with.

One question: I don't understand (in the last line of the formula) where the 2 comes from as in 2 x |A ^ B ^ C|

Thanks again for going to the trouble.

 

[BobA]

You really sent me back to school on this one. I'm going to work with this and see what I can come up with.



One question: I don't understand (in the last line of the formula) wherethe 2 comes from as in 2 x |A ^ B ^ C|



Thanks again for going to the trouble.

Is there any kind of intersection function for excel without using VBA code?

 

[joeu2004]

As near as I can tell your formula gives the total
number of permutations.

Hmm, what gave it away: perhaps the explicit use of PERMUT(n,k)? ;-)

In three cells I have the following:
456 456 456 [....]
However there are only 6 unique combinations without any duplicates.
456
465
546
564
645
654

That is the only example for which you enumerated the combinations you are
interested in. Note that they are all permutations. But now I realize,
that was only by coincidence.

For example they could be:
12 145 5678
[....] In this example there are [...] 18 unique combinations. [....]
For example if I have:
345 2367
[....] There are [...] 11 unique combinations.

[....]
Think of a trifecta with the following ticket:
123
123
789
The 1,2 or 3 can come first or second but not third, and the
7,8 or 9 must come third.
[....] The correct number of combinations that I'm looking for is 18

It would be clearer if you enumerated the combinations for each example.

But I think I understand now: you want the (number of) unique combinations
of one digit from each cell, excluding duplicates.

Off-hand, I cannot think of an algebraic formula to calculate the number of
combinations directly. I must resort to actually enumerating the
combinations, then counting.

But that might be a good thing, since it might be prudent to look at the
combinations as well.

The VBA function below enumerates the combinations of 2 or 3 cells.

If the cells are A1:A3, simply writing =uniqCombo(A1:A3) returns the count.

But by selecting that number+1 cells in a column and array-entering
=uniqCombo(A1:A3) -- press ctrl+shift+Enter instead of just Enter -- the
function returns the count followed by the combinations.

So the output would like the following (copy, paste into text file, and open
it in Excel as a CSV file):

456,6,12,18,345,11,123,18
456,456,145,145,2367,32,123,127
456,465,5678,146,,36,789,128
,546,,147,,37,,129
,564,,148,,42,,137
,645,,156,,43,,138
,654,,157,,46,,139
,,,158,,47,,217
,,,215,,52,,218
,,,216,,53,,219
,,,217,,56,,237
,,,218,,57,,238
,,,245,,,,239
,,,246,,,,317
,,,247,,,,318
,,,248,,,,319
,,,256,,,,327
,,,257,,,,328
,,,258,,,,329

Or go to https://app.box.com/s/jfnt8omxcuqmxftw1fth to see an image.

The VBA function....


Option Explicit

Function uniqCombo(rng As Range) As Variant
Dim i As Long, k As Long, j As Long, n As Long
Dim i1 As Long, i2 As Long, i3 As Long
Dim t1 As Long, t2 As Long, t3 As Long
Dim x1 As Long, x2 As Long
Dim r As Variant, s As String, x As Long
Dim nRng As Long, maxN As Long

' separate unique digits in each cell
nRng = rng.Count
If nRng <> 2 And nRng <> 3 Then
uniqCombo = CVErr(xlErrValue)
Exit Function
End If
ReDim t(1 To nRng, 0 To 10) As Long ' t(k,0) counts t(k,i)
For k = 1 To nRng
ReDim d(0 To 9) As Long ' reinit to zero
s = rng(k).Text
j = 0
For i = 1 To Len(s)
x = Mid(s, i, 1)
If d(x) <> 1 Then
d(x) = 1
j = j + 1
t(k, j) = x
End If
Next i
t(k, 0) = j
Next k

' enumerate unique combination of one digit
' from each cell, excluding duplicates
Select Case nRng
Case 2:
maxN = t(1, 0) * t(2, 0)
ReDim res(0 To maxN) As Variant ' res(0) is count
n = 0
For i1 = 1 To t(1, 0)
t1 = t(1, i1): x1 = t1 * 10
For i2 = 1 To t(2, 0)
t2 = t(2, i2)
If t2 <> t1 Then
n = n + 1
res(n) = x1 + t2
End If
Next i2
Next i1
Case 3:
maxN = t(1, 0) * t(2, 0) * t(3, 0)
ReDim res(0 To maxN) As Variant ' res(0) is count
n = 0
For i1 = 1 To t(1, 0)
t1 = t(1, i1): x1 = t1 * 100
For i2 = 1 To t(2, 0)
t2 = t(2, i2)
If t2 <> t1 Then
x2 = t2 * 10
For i3 = 1 To t(3, 0)
t3 = t(3, i3)
If t3 <> t1 And t3 <> t2 Then
n = n + 1
res(n) = x1 + x2 + t3
End If
Next i3
End If
Next i2
Next i1
End Select

' output result as column array
ReDim Preserve res(0 To n) As Variant
res(0) = n
uniqCombo = WorksheetFunction.Transpose(res)
End Function

 

[BobA]

You first count the number of all possible triples (a, b, c), which is |A| × |B| × |C|. Then you subtract off cases where two of the three coincide, i.e the types (x, x, c), (x, b, x) and (a, x, x). In these the x must be in the intersection, i.e. in A ^ B or A ^ C or B ^ C, respectively. Thisgives us the terms in the second line. But then you have terms like (x, x,x), where x is in A ^ B ^ C. Each of these occurs as a special case of every one of the terms (x, x, c), (x, b, x) etc and so each has now been subtracted off 3 times. Hence we need to add back 2 of the 3 occurrences of the term |A ^ B ^ C| to counteract the triple-counting, which is why we have the factor 2. (This may give you some idea where the term 'Inclusion-Exclusion' comes from: you first of all remove items, then put back some of them.)



Howard

Good explanation, and thanks for taking the time, Howard. Thanks also to Joeu2004. I'm not to well versed in VBA, but I'll work with your code and try to make it work.

Thanks again to both of you, I appreciate the effort.