5 digit number combination sing 0-9

You need further definition of your problem. with 5 digits the highest number is 99,999 and the lowest number is 00,000 for a total of 100,000 unique values.
Can digit be repeated within the 5 digit combination like 22,552 or 00,001?

There are =combin(5, 3) = 10 ways to choose which three digits contain the 3, 7, and 8

There are =fact(3) = 6 ways in which they can be ordered

There are =combin(7,2) = 21 ways the remaining 2 numbers can be selected from the other 7

There are =fact(2) = 2 ways in which they can be ordered

So: =10*6*21*2 = 2520 combinations

have no experience with excel or probability

Then this is probably not a good place to start.

Do you want only combinations that contain ALL three values?
ie

1,3,7,8,9 YES (all three values exist)
3,7,8,9,0 YES (all three values exist)
1,2,3,4,5 NO (as 7 and 8 are missing)
1,2,3,7,9 NO (as 8 is missing)

Obviously far more appropriate with VBA, but just for interest's sake, array formula**:

=SMALL(IF(MMULT(0+(LEN(ROW($11111:$99999))-LEN(SUBSTITUTE(ROW($11111:$99999),{3,7,8},""))={1,1,1}),{1;1;1})=3,ROW($11111:$99999)),ROWS($1:1))

Copy down a further 2765 rows (disagree with you slightly there, shg ).

Will probably take a couple of minutes to calculate though, so go with the VBA if someone offers it!

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Actually it's 2940 if you consider e.g. "00378" a possibility. And then you'd need:

=TEXT(SMALL(IF(MMULT(0+(LEN(TEXT(ROW($1:$99999),"00000"))-LEN(SUBSTITUTE(TEXT(ROW($1:$99999),"00000"),{3,7,8},""))={1,1,1}),{1;1;1})=3,ROW($1:$99999)),ROWS($1:1)),"00000")

copied to that number of rows.

Regards

Originally Posted by jamo31

wait it work , now it i want to put another in that is known do I add it to the brackets in the formula that already have 3,7,8 in them?

Not sure what you mean. Sorry.

Regards

Copy down a further 2765 rows (disagree with you slightly there, shg ).

Those results include numbers with repeated digits:

11378
11387
11738
11783
11837
11873
12378
...

Originally Posted by shg

Those results include numbers with repeated digits:

11378
11387
11738
11783
11837
11873
12378
...

I thought it was only the 3, 7 and 8 which were prohibited from repeating? Perhaps I misread it.

Regards

@shg

Originally Posted by XOR LX

I thought it was only the 3, 7 and 8 which were prohibited from repeating? Perhaps I misread it.

Regards

When you eliminate the duplicates, there are 2520:

Row\Col	A
1	Num
2	01378
3	01387
4	01738
5	01783
6	01837
7	01873
8	02378
2514	98731
2515	98732
2516	98734
2517	98735
2518	98736
2519	98743
2520	98753
2521	98763

Very impressive formula, though.

Originally Posted by shg

When you eliminate the duplicates, there are 2521

Hmmm. I don't get any duplicates in that 2940.

Originally Posted by shg

Very impressive formula, though.

Cheers!

Originally Posted by XOR LX

Hmmm. I don't get any duplicates in that 2940.

11378 has two 1's.

So you now want only 5-digit numbers which contain precisely one 7, one 8 and two 3s?

And are you going to want to change this again in future? Perhaps you should give a better explanation of precisely what you want.

Regards

Originally Posted by jamo31

yes that is it, apologies, no i will not be changing it in future

I believe that you only need copy this version to a total of 420 rows:

=TEXT(SMALL(IF(MMULT(0+(LEN(TEXT(ROW($1111:$99999),"00000"))-LEN(SUBSTITUTE(TEXT(ROW($1111:$99999),"00000"),{3,7,8},""))={2,1,1}),{1;1;1})=3,ROW($1111:$99999)),ROWS($1:1)),"00000")

Regards

Originally Posted by jamo31

okay so i copied that to 420 rows but only 01111 repeated as a combination

And you remembered the array formula bit?

Regards

If you are interested in a multi-step process to extract all the 5 digit numbers that are without duplicated digits and all the numbers with 3 7 8 in any order.
Be warned that this produces a large file that is very slow to calculate.

I entered the lowest possible number without a duplicate digit as text 01234 and filled the series in a column to the highest possible number without a duplicate digit 98765 in column A starting at A2.
In B2 enter this formula and fill across to column K and number each column B to K with the digits 0 to 9 to identify the column that has each of the digits. Then fill down to the end of the values in column A.

Formula:

=IFERROR(FIND(COLUMNS($A$2:A2)-1,$A2),"")

In L2 enter this formula and fill down. The SUM will be 15 if all 5 digits are found in the number.

Formula:

=IF(SUM(B2:K2)=15,1,"")

In P2 enter this array formula and fill down to extract the values in column A that do not have duplicated digits.

Formula:

=IFERROR(INDEX($A$2:$A$97533,SMALL(IF($L$2:$L$97533=1,ROW($L$2:$L$97533)-MIN(ROW($L$2:$L$97533))+1),ROWS($2:2))),"")

In N2 enter this formula and fill down to identify all the values in column A that contain 3,7,8 in any order

Formula:

=IF(COUNT(E2,I2,J2)=3,1,"")

In Q2 enter this Array formula and fill down to extract all the values from column A that have the digits 3,7,8

Formula:

=IFERROR(INDEX($A$2:$A$97533,SMALL(IF($M$2:$M$97533=1,ROW($M$2:$M$97533)-MIN(ROW($M$2:$M$97533))+1),ROWS($1:1))),"")

The enclosed file is stripped of all formulae except for row 2 but all the calculated values are shown that were derived from the formulae.

Originally Posted by jamo31

@newdoverman how do I read the values from that? @xor LX no i didnt use array bit, is that something seperate to your formula?, how do i do it?
thanks

But I already gave the instructions on how to enter array formulas in one of my previous posts.

I presume you followed them that first time since you said that version was working - and all the formulas I have so far posted have been array formulas.

Regards

@newdoverman

Paradoxical.

Normally we would consider a multi-step over a single-formula process for the simple reason that, despite taking up more space in the workbook (and also having less "aesthetic appeal" to some), we save considerably on calculation time and/or render the formula-work more comprehensible.

Your set-up certainly does the latter, though - and this is why I say paradoxical - would seem to be far less efficient than the equivalent single-formula process which I offered.

Regards

@XOR LX
The shear volume of the data generated is something to be considered. The breakdown of the cells used is below. This large volume of calculations could account for a lot of the difference between the single formula and the step approach.

@ Jamo31
There are two lists to the right of the data that contain all the 5 digit values that don't have duplicated digits . Column P has the 5 digit numbers without duplicated digits. Column Q has the 5 digit numbers that contain 3, 7 and 8 in any order.

Unless you have a fast computer with lots of memory, don't fill the formulae down the columns. It takes a long time to calculate. I just checked and there are 97532 5 digit values starting from 01234 to 98765 inclusive down column A. Multiply that by 12 (One column per digit and 2 columns for summary) and you have 1,170,384 with calculations before extracting the values into columns P and Q that have 36000 calculations. The total is then just over 1.2 million cells with calculations.

@SHG
Same problem with my solution when extracting the 3, 7 and 8 from the original data and I uploaded the wrong file

If the formula in my workbook at M2 and filled down is changed to the following the problem is eliminated

Formula:

=IF(AND(COUNT(E2,I2,J2)=3,L2=1),1,"")

Via code:

Sub x()
  Const sSym        As String = "0124569"
  Dim aiC(1 To 2)   As Long
  Dim s             As String
  Dim asOut()       As String

  aiC(1) = -1
  Do While bNextCombo(aiC, 7)
    s = "378" & Mid(sSym, aiC(1) + 1, 1) & Mid(sSym, aiC(2) + 1, 1)
    bStrPermute s, asOut
    Cells(Rows.Count, "A").End(xlUp)(2).Resize(UBound(asOut)).Value = asOut
  Loop
End Sub

Public Function bNextCombo(aiC() As Long, n As Long) As Boolean
  ' shg 2009-12
  '     2011-07 (modified to require aiC(1) < 0 to initialize)

  ' VBA only

  ' Sets aiC to the next combination of n choose m in lexical order
  ' Returns True unless the combination is the last, in which case
  ' it leaves aiC unmodified.

  ' If aiC(1) < 0, initializes aiC to the first combo:
  '                   {m-1,  m-2, ...,     1,   0}
  ' The last combo is {n-1,  n-2, ..., n-m+1, n-m}

  Dim m             As Long
  Dim i             As Long

  m = UBound(aiC)
  If n < m Then Exit Function

  If aiC(1) < 0 Then    ' set initial combo
    i = 1
    aiC(1) = m - 2

  Else

    ' find rightmost incrementable index
    For i = m To 2 Step -1
      If aiC(i) < aiC(i - 1) - 1 Then Exit For
    Next i
  End If

  If i <> 1 Or aiC(1) < n - 1 Then
    ' increment that index, and set 'righter' indices descending to 0
    aiC(i) = aiC(i) + 1
    For i = i + 1 To m
      aiC(i) = m - i
    Next i

    bNextCombo = True
  End If
End Function

Function bStrPermute(sInp As String, asOut() As String) As Boolean
  ' puts the permutations of sInp in dynamic array asOut
  ' VBA only

  If Len(sInp) < 9 Then
    ReDim asOut(1 To WorksheetFunction.Fact(Len(sInp)), 1 To 1)
    GetPermutation "", sInp, asOut, 0
    bStrPermute = True
  End If
End Function

Sub GetPermutation(sL As String, sR As String, asOut() As String, nOut As Long)
  ' adapted from http://spreadsheetpage.com/index.php/site/tip/generating_permutations/

  Dim i             As Integer
  Dim j             As Integer

  j = Len(sR)
  If j <= 1 Then
    nOut = nOut + 1
    asOut(nOut, 1) = sL & sR
  Else
    For i = 1 To j
      GetPermutation sL & Mid(sR, i, 1), Left(sR, i - 1) & Right(sR, j - i), asOut, nOut
    Next
  End If
End Sub

Dunno. You may be right.

Well from msg#3 "isolate all combination with 3,7,8 in them" means to me to extract the 5 digit numbers that contain 3,7 and 8.

@SHG
I tried your code and it sure is fast!

Well done!

Originally Posted by newdoverman

Well from msg#3 "isolate all combination with 3,7,8 in them" means to me to extract the 5 digit numbers that contain 3,7 and 8.

That may or may not be what the OP wanted, but if it were, that would certainly have been a more succinct statement of the problem.