Combination Of Numbers

H

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,524
Office Version
  1. 2016
Platform
  1. Windows
Hello To All,

i need a formula which would result in possible combination of two digit figures.

For Example

Cell A1=503

Possible Combination of two digits figure are

00-03-05-50-53-55-30-33-35

Is there any formula by which we can obtain the above mentioned result.

Awaiting reply,

Humayun
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Hello,

Alt + F11
Alt - I - M insert module

try on a copy:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> combos_no_doubles()<br><br><SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> Length <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, Original <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> tempOriginal1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, n <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, d <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><br>Original = Range("A1")<br>Length = Len(Original)<br><br>i = 1<br>d = 0<br><br>tempOriginal1 = Mid(Original, i, 1)<br><br>Range("C:C").ClearContents<br>    <br>    <SPAN style="color:#00007F">For</SPAN> n = 1 <SPAN style="color:#00007F">To</SPAN> Length<br>        <br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range("C1:C" & Length).Offset(d * Length)<br>            <br>            c.Value = tempOriginal1<br>        <br>        <SPAN style="color:#00007F">Next</SPAN> c<br>        <br>        i = i + 1<br>        d = d + 1<br>        tempOriginal1 = Mid(Original, i, 1)<br>    <br>    <SPAN style="color:#00007F">Next</SPAN> n<br>    <br>    i = 1<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range("C1:C" & Range("C1").End(xlDown).Row)<br>        <br>        <SPAN style="color:#00007F">If</SPAN> i > Length <SPAN style="color:#00007F">Then</SPAN><br>            i = 1<br>            c.Value = c.Value & Mid(Original, i, 1)<br>            i = i + 1<br>        <SPAN style="color:#00007F">Else</SPAN><br>            c.Value = c.Value & Mid(Original, i, 1)<br>            i = i + 1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0
ok jeff, i have copied the code into the module and yes ofcourse i have done it on a seperate sheet..

now further what to ? i mean is there any formula which needs to be entered in B1.... pls remember that the value 503 is in cell A1
 
Upvote 0
You may attach this to a button, or press alt + F8 and select combos_no_doubles to run. After selecting run the results should appear in column C

NOTE: this will clear all of column C:C

Also Humayun, you may need to custom format the cells in column C:C to display the 05 and not just 5. (..I used a custom format where I typed in 00 in the entry feild.)

-Jeff
 
Upvote 0
Dear Jeff,

Sorry for a late reply.....

Bingo .... Working PERFECT

Thanks a lot

Best Regards,

Humayun
 
Upvote 0
Hello To All,

I Started This thread long time back and got what whas required...

i need further help regarding this


i need a formula which would result in possible combination of FOUR digit figures.

Example # 1

Cell A1=123

Possible Combinations would be 81 (3x3x3=81)

Example # 2

Cell A1=1234

Possible Combinations would be 256 (4x4x4x4=256)

Is there any formula by which we can obtain the above mentioned result.

Awaiting reply,

Humayun
<!-- / message -->
 
Upvote 0
Hello Humayun,

Glad to hear from you.

I am confused by the example. Would this be counting the digits in cell A1, and then taking that count and multiplying it by itself the number of time equal to the count?

like

A1 = 19 and the result would be 4
A1 = 4578394 and the result would be 7 to the seventh power, or 823543?

Please let me know if this is correct.

If so I am think using the POWER() function.

-Jeff
 
Upvote 0
Dear Jeff,

I am so happy u r there... I knew you would be the first one to reply provided u r online...

What i need is if i enter Any value in cell A1 then i need the possible combinations of 4 digits..

For say i enter 19 in cell A1

What i need in any column (for say column C) is

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 width=64>1111</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>1119</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>1191</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>1199</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>1911</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>1919</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>1991</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>1999</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>9111</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>9119</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>9191</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>9199</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>9911</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>9919</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>9991</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>9999</TD></TR></TBODY></TABLE>

you can also see post # 2 --- you will get an idea coz you provided me the code for possible combination of 2 Digit Numbers in that code

Regards,

Awaiting Reply,

Humayun
 
Upvote 0
Dear Jeff,

I was looking at exactly the same thread which you were looking i.e.
Finding Possible combinations..

I copied the code from that thread and have modified it a bit to get the result..

Here is the code

Code: 
Sub testComb()
    Dim i As Long, j As Long, k As Long, l As Long, lastRow As Long, lin As Long
    Dim wk As Worksheet
 
    Set wk = Sheets("2Digits") '<---Change this accordindgly
 
    With wk
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("C1") = "Combinations"
        .Range("C2:C" & .Rows.Count).ClearContents
        lin = 1
 
        For i = 2 To lastRow - 2
            For j = i + 1 To lastRow - 1
                For k = j + 1 To lastRow - 1
                    For l = k + 1 To lastRow
                    lin = lin + 1
                    .Range("C" & lin) = .Range("A" & i) & .Range("A" & j) & .Range("A" & k) & .Range("A" & l)
                    
                    Next l
                Next k
            Next j
        Next i
        
        End With
 
End Sub

Now instead of entering 19 in cell A1 i am entering 1 in Cell A1 & 9 in Cell B1 to get the desired result...

Pls comment on this

Regards,

Humayun
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Cut Smaller Pieces From Bigger Pieces
2
Replies
13
Views
771
Solon Aquila
S
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
400
dshtesy
D
M
  • Question Question
Pivot table not showing as Numbers
Replies
1
Views
279
shift-del
shift-del
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
606
awoohaw
awoohaw
B
  • Solved
Need to add two sets of minutes with custom formatting and round
Replies
1
Views
536
bh24524
B

Forum statistics

Threads
1,224,606
Messages
6,179,866
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top