5 digit number combination sing 0-9

[jamo31]

I need urgent help getting excel to show me all possible 5 digit combinations of 0-9 and then showing me all of the combinations containing 3,8,7 in any order? Help! I also am completely new so please be very detailed in explaination with examples, Thanks!!

[newdoverman]

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?

[jamo31]

the same number can't be in the same combination, so 3-7-8-0-1 not 3-3-7-8-1 i need to be shown how to get excel to generate the list, and then how to isolate all combination with 3,7,8 in them, can you help?

[shg]

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

[jamo31]

i'm getting more confused, I need to get excel to make a list of (5 DIGIT) combinations in excel using numbers 0-9 , i need to make the list in excel first, how do I make the list in excel??? can you go into more detail on what do just wrote i have no experience with excel or probability

[shg]

have no experience with excel or probability

Then this is probably not a good place to start.

[jamo31]

I asked how to generate a 5 digit list in excel and find all specific combinations containing 3,7,8 in any order, Since this is a excel forum it is the only place to ask, you gave me the probability of possible outcomes which is nice to know but that isn't what i asked please don't reply unless it deals with my specific question, can anyone give me a specific answer without being smart

[Special-K]

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)

[jamo31]

yes please, can you help?

[XOR LX]

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).

[jamo31]

okay thanks i will try that

[XOR LX]

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

[shg]

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
...

[jamo31]

so say i put 00000 in A1 where do I put A1 in the fomula, I am not getting any result yet, when I copy formula a pull down empty rows it just show up as 00001, help?

[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?

[XOR LX]

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

[XOR LX]

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

[shg]

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.

[jamo31]

sorry you are right, and that bit worked, but know i want to add another number what i mean if i want to add another 3 so know the known numbers are 3,8,7,3 how do I put that into the formula and only combinations including those 4 are important, do I add another to the brackets that already contain 3,7,8?

[XOR LX]

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

[jamo31]

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

[XOR LX]

When you eliminate the duplicates, there are 2521

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

Very impressive formula, though.

Cheers!

[XOR LX]

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

[jamo31]

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

[newdoverman]

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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

[XOR LX]

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

And you remembered the array formula bit?

Regards

[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

[XOR LX]

@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]

@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]

@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]

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

11378 has two 1's.

[newdoverman]

@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

Please Login or Register  to view this content.

[shg]

Via code:

Please Login or Register  to view this content.

[XOR LX]

@shg

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

Regards

[shg]

Dunno. You may be right.

[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.

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

Well done!

[shg]

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.