Help urgent help for simple numeric combination problem

H

huat08

New Member
Joined
Apr 3, 2008
Messages
40
Hi guys,

I need some urgent help on combination of numbers


on the extreme left column, i have 23 numbers from A1:A23. All 23 numbers are in the form of 4 digit. For example A1 there is 1234, i need to display the possible 3 digit combination of this in the same row (like say 123,124,234,134 in B1,C1,D1 AND E1).

Another example in A2 there is 3545, i need to display 354,455,355 in the same row in B2,C2,D2

I need to perform this operation for the 23 numbers on the extreme left row. Can anyone give me some hint on the code please. Thank you
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
If 1234 is in A1
=MID($A1, 1, 1)&MID($A1, 2 ,1)&MID($A1, 3 ,1) in B1 will return "123"
=MID($A1, 1 ,1)&MID($A1, 2,1)&MID($A1, 4 ,1) in C1 will return "124"
etc.

The results in the second row of your example don't follow the pattern of the first row.
Why doesn't the number 3545 break to 354, 355, 545, 345?
 
Last edited:
Upvote 0
Can anyone give me some hint on the code please
Click to expand...
Loop 3 times by digit after digit
Code: 
for i = 1 to 2
   for ii = i + 1 to 3
       for iii = ii + 1 to 4
           mid(number, i, 1) & mid(number, ii,1) & mid(number, iii)
next iii,ii,i
 
Upvote 0
mikerickson said:
If 1234 is in A1
=MID($A1, 1, 1)&MID($A1, 2 ,1)&MID($A1, 3 ,1) in B1 will return "123"
=MID($A1, 1 ,1)&MID($A1, 2 ,1)&MID($A1, 4 ,1) in C1 will return "124"
etc.

The results in the second row of your example don't follow the pattern of the first row.
Why doesn't the number 3545 break to 354, 2355 545, 345?
Click to expand...

thks for the quick reply mikeerickson. I guess is because i needed only 3digit from the extreme left column without any repetition. i also need the digit to be in small to big for eg (3545->355,345,455 with no repeats). Is there any way to go about this?
 
Upvote 0
This might help.
<table border=1 cellspacing = 0 bgcolor="#ffffff"><tr bgcolor = "#aaaaaa"><td> <td align=center width=45><b>A</b><td align=center width=45><b>B</b><td align=center width=45><b>C</b><td align=center width=45><b>D</b><td align=center width=45><b>E</b><td align=center width=45><b>F</b>
<tr><td align=center bgcolor="#aaaaaa"><b>1</b><td bgcolor="#FFFFFF" > <FONT color="#000000">1234</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">123</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">124</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">134</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">234</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>2</b><td bgcolor="#FFFFFF" > <FONT color="#000000">3545</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">345</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">355</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">455</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">#NUM!</FONT></tr></table><table border=1 cellspacing = 0 bgcolor="#ddedcc">
<tr><td colspan=3 align="center">Formulas in this range: </tr>
<tr><td align=center>Range with same formula<td align=center>Cell:<td align=center>holds Formula:</tr>
<tr><td>C1:C2<td align=right>C1 * <td align = left >{=MIN(--(SMALL(--(MID(A1,{1,2,3,4},1)),{1,1,2,1})&SMALL(--(MID(A1,{1,2,3,4},1)),{2,2,3,3})&SMALL(--(MID(A1,{1,2,3,4},1)),{3,4,4,4})))}</tr>
<tr><td><td align=right>D1 * <td align = left >{=SMALL(--(SMALL(--(MID($A1,{1,2,3,4},1)),{1,1,2,1})&SMALL(--(MID($A1,{1,2,3,4},1)),{2,2,3,3})&SMALL(--(MID($A1,{1,2,3,4},1)),{3,4,4,4})),FREQUENCY(--(SMALL(--(MID($A1,{1,2,3,4},1)),{1,1,2,1})&SMALL(--(MID($A1,{1,2,3,4},1)),{2,2,3,3})&SMALL(--(MID($A1,{1,2,3,4},1)),{3,4,4,4})),C1)+1)}</tr>
<tr><td><td align=right>E1 * <td align = left >{=SMALL(--(SMALL(--(MID($A1,{1,2,3,4},1)),{1,1,2,1})&SMALL(--(MID($A1,{1,2,3,4},1)),{2,2,3,3})&SMALL(--(MID($A1,{1,2,3,4},1)),{3,4,4,4})),FREQUENCY(--(SMALL(--(MID($A1,{1,2,3,4},1)),{1,1,2,1})&SMALL(--(MID($A1,{1,2,3,4},1)),{2,2,3,3})&SMALL(--(MID($A1,{1,2,3,4},1)),{3,4,4,4})),D1)+1)}</tr>
<tr><td><td align=right>F1 * <td align = left >{=SMALL(--(SMALL(--(MID($A1,{1,2,3,4},1)),{1,1,2,1})&SMALL(--(MID($A1,{1,2,3,4},1)),{2,2,3,3})&SMALL(--(MID($A1,{1,2,3,4},1)),{3,4,4,4})),FREQUENCY(--(SMALL(--(MID($A1,{1,2,3,4},1)),{1,1,2,1})&SMALL(--(MID($A1,{1,2,3,4},1)),{2,2,3,3})&SMALL(--(MID($A1,{1,2,3,4},1)),{3,4,4,4})),E1)+1)}</tr>
<tr><td><td align=right>D2 * <td align = left >{=SMALL(--(SMALL(--(MID($A2,{1,2,3,4},1)),{1,1,2,1})&SMALL(--(MID($A2,{1,2,3,4},1)),{2,2,3,3})&SMALL(--(MID($A2,{1,2,3,4},1)),{3,4,4,4})),FREQUENCY(--(SMALL(--(MID($A2,{1,2,3,4},1)),{1,1,2,1})&SMALL(--(MID($A2,{1,2,3,4},1)),{2,2,3,3})&SMALL(--(MID($A2,{1,2,3,4},1)),{3,4,4,4})),C2)+1)}</tr>
<tr><td><td align=right>E2 * <td align = left >{=SMALL(--(SMALL(--(MID($A2,{1,2,3,4},1)),{1,1,2,1})&SMALL(--(MID($A2,{1,2,3,4},1)),{2,2,3,3})&SMALL(--(MID($A2,{1,2,3,4},1)),{3,4,4,4})),FREQUENCY(--(SMALL(--(MID($A2,{1,2,3,4},1)),{1,1,2,1})&SMALL(--(MID($A2,{1,2,3,4},1)),{2,2,3,3})&SMALL(--(MID($A2,{1,2,3,4},1)),{3,4,4,4})),D2)+1)}</tr>
<tr><td><td align=right>F2 * <td align = left >{=SMALL(--(SMALL(--(MID($A2,{1,2,3,4},1)),{1,1,2,1})&SMALL(--(MID($A2,{1,2,3,4},1)),{2,2,3,3})&SMALL(--(MID($A2,{1,2,3,4},1)),{3,4,4,4})),FREQUENCY(--(SMALL(--(MID($A2,{1,2,3,4},1)),{1,1,2,1})&SMALL(--(MID($A2,{1,2,3,4},1)),{2,2,3,3})&SMALL(--(MID($A2,{1,2,3,4},1)),{3,4,4,4})),E2)+1)}</tr>
<tr><td colspan=3 align="center">Array formulas are confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac). </tr></table>
In case of duplicates, the errors at the end of the row can be hidden with Conditional formatting.

SMALL(--(MID(A1,{1,2,3,4},1)),1) is the least digit in A1
SMALL(--(MID(A1,{1,2,3,4},1)),2) is the second

this ordering is used to create the array
SMALL(--(MID(A1,{1,2,3,4},1)),{1,1,2,1}) _
& SMALL(--(MID(A1,{1,2,3,4},1)),{2,2,3,3}) _
& SMALL(--(MID(A1,{1,2,3,4},1)),{3,4,4,4})
of the four combinations with digits ascending.
This could be used as the ReferTo for a named array, numberArray

C1 is =MIN(numberArray)
D1 =SMALL(numberArray,FREQUENCY(numberArray,C1)+1) is the second smallest number in number array (without duplication.)
E1 =SMALL(numberArray,FREQUENCY(numberArray,D1)+1) is the third smallest.
etc.
 
Upvote 0
really appreciate ur coding bro. the code doesnt seem to work well if there is zero in the numbers.
combine1.JPG
 
Upvote 0
What would you expect from that input?
Formatting the cells "000" (no quotes) will show the 0's that are there.
 
Upvote 0
As to the case where the input number is < 1000:
Either those entries can be entered as text or A1 can be replaced with TEXT(A1,"0000") throughout.

Its beginning to look like Names are becoming nessesary to keep it editable.

Make C1 the Active Cell and define some names

Name: inputStr
RefersTo: =TEXT(Sheet1!$A1,"0000")

Name: numberArray
RefersTo: =(--(SMALL(--(MID(inputString,{1,2,3,4},1)),{1,1,2,1})&SMALL(--(MID(inputString,{1,2,3,4},1)),{2,2,3,3})&SMALL(--(MID(inputString,{1,2,3,4},1)),{3,4,4,4})))

Then put this (non-CSE) formula in C1 =MIN(numberArray)
This formula goes in D1 =SMALL(numberArray,FREQUENCY(numberArray,C1)+1)
and drag D1 right to F1.
Format C1:F1 to 000
drag C1:F1 downward.
 
Upvote 0
mikerickson said:
What would you expect from that input?
Formatting the cells "000" (no quotes) will show the 0's that are there.
Click to expand...
ya mike, you are correct. formatting those cell to 000 would bring out a zero. why didnt i think of it :) but there is a case when the number start with 0 eg 0925 then the formula wouldnt work, the cell will show #value! anyway to overcome this beside changing the data in the extreme left column. Thank you for being so helpful mike :)



combine2.JPG
 
Upvote 0
See
Powerset, Subset, and Combinations & Permutations
http://www.tushar-mehta.com/excel/tips/powerset.html

After installing the code on that page, select a 4 cell range (say D3:G3) and array-enter =UDFCombinations(TRANSPOSE(--MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)),3,"")

To array enter a formula, complete the formula with the CTRL+SHIFT+ENTER combination rather than just the ENTER or TAB key.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Excel Formula to build out Combinations/Permutations of a series of numbers
Replies
1
Views
209
Eric W
Eric W
G
  • Solved
Formula to efficiently find unique set from a number of numeric arrays
Replies
3
Views
107
Peter_SSs
Peter_SSs
M
  • Solved
Macro Help,
Replies
3
Views
269
offthelip
O
serge
  • Question Question
CF formula for rows.
Replies
6
Views
221
serge
serge
J
  • Question Question
How to display different values to ones entered
Replies
2
Views
299
kevin9999
K

Forum statistics

Threads
1,224,583
Messages
6,179,671
Members
452,937
Latest member
Bhg1984

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