Extract combination number from the result

K

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
Hello To All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
Nice UDF solution facilitated by Shg, which generated all possible combinations of the football <o:p></o:p>
From 0 to 4.782.968 For example, if I put in A1 the number 0 I get the first combination in B1 as 1,1,1,1,1,1,1,1,1,1,1,1,1,1. As when I put the number 12 in A3 I get result in B3 1,1,1,1,1,1,1,1,1,1,1,X,X,1 and so on. This is shown in example table 1 below. <o:p></o:p>
<o:p></o:p>
Example Table 1:<o:p></o:p>
<TABLE style="BACKGROUND: white; mso-cellspacing: 0cm; mso-padding-alt: 0cm 2.0pt 0cm 2.0pt" border=1 cellSpacing=0 cellPadding=0 bgColor=white><TBODY><TR><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; BACKGROUND: #cacaca; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; BACKGROUND: #cacaca; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
A<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; BACKGROUND: #cacaca; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
B<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; BACKGROUND: #cacaca; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
1<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
0<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">1,1,1,1,1,1,1,1,1,1,1,1,1,1<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; BACKGROUND: #cacaca; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
2<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
7<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">1,1,1,1,1,1,1,1,1,1,1,1,2,X<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; BACKGROUND: #cacaca; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
3<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
12<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">1,1,1,1,1,1,1,1,1,1,1,X,X,1<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; BACKGROUND: #cacaca; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
4<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
20<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">1,1,1,1,1,1,1,1,1,1,1,2,1,2<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; BACKGROUND: #cacaca; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
5<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
100000<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">1,1,1,X,2,1,1,2,1,X,X,2,1,X<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; BACKGROUND: #cacaca; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
6<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
1250000<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">1,2,X,1,1,X,X,X,2,1,1,1,2,2<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; BACKGROUND: #cacaca; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
7<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
3500000<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">2,1,X,2,1,2,X,X,1,1,2,X,2,2<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; BACKGROUND: #cacaca; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
8<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
4782968<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">2,2,2,2,2,2,2,2,2,2,2,2,2,2<o:p></o:p>
</TD></TR></TBODY></TABLE>
<o:p></o:p>
<TABLE style="BORDER-BOTTOM: lime 3pt groove; BORDER-LEFT: lime 3pt groove; BACKGROUND: #fffcf9; BORDER-TOP: lime 3pt groove; BORDER-RIGHT: lime 3pt groove; mso-cellspacing: 1.5pt; mso-border-alt: three-d-engrave lime 3.0pt" border=1 cellPadding=0 bgColor=#fffcf9><TBODY><TR><TD style="BORDER-BOTTOM: lime; BORDER-LEFT: lime; PADDING-BOTTOM: 0.75pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BORDER-TOP: lime; BORDER-RIGHT: lime; PADDING-TOP: 0.75pt">Spreadsheet Formulas<o:p></o:p>
</TD></TR><TR><TD style="BORDER-BOTTOM: lime; BORDER-LEFT: lime; PADDING-BOTTOM: 0.75pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BORDER-TOP: lime; BORDER-RIGHT: lime; PADDING-TOP: 0.75pt"><TABLE style="mso-cellspacing: 0cm; mso-padding-alt: 1.5pt 1.5pt 1.5pt 1.5pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; PADDING-BOTTOM: 1.5pt; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BACKGROUND: #cacaca; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; PADDING-TOP: 1.5pt">Cell<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; PADDING-BOTTOM: 1.5pt; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BACKGROUND: #cacaca; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; PADDING-TOP: 1.5pt">Formula<o:p></o:p>
</TD></TR><TR><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; PADDING-TOP: 1.5pt">B1<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; PADDING-TOP: 1.5pt">=Kishan(A1;14)<o:p></o:p>
</TD></TR><TR><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; PADDING-TOP: 1.5pt">B2<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; PADDING-TOP: 1.5pt">=Kishan(A2;14)<o:p></o:p>
</TD></TR><TR><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; PADDING-TOP: 1.5pt">B3<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; PADDING-TOP: 1.5pt">=Kishan(A3;14)<o:p></o:p>
</TD></TR><TR><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; PADDING-TOP: 1.5pt">B4<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; PADDING-TOP: 1.5pt">=Kishan(A4;14)<o:p></o:p>
</TD></TR><TR><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; PADDING-TOP: 1.5pt">B5<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; PADDING-TOP: 1.5pt">=Kishan(A5;14)<o:p></o:p>
</TD></TR><TR><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; PADDING-TOP: 1.5pt">B6<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; PADDING-TOP: 1.5pt">=Kishan(A6;14)<o:p></o:p>
</TD></TR><TR><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; PADDING-TOP: 1.5pt">B7<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; PADDING-TOP: 1.5pt">=Kishan(A7;14)<o:p></o:p>
</TD></TR><TR><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; PADDING-TOP: 1.5pt">B8<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; PADDING-TOP: 1.5pt">=Kishan(A8;14)<o:p></o:p>
</TD></TR></TBODY></TABLE><o:p></o:p>
</TD></TR></TBODY></TABLE>
<o:p></o:p>
<o:p></o:p>
Rich (BB code): 
<o:p></o:p>
Rich (BB code):
Rich (BB code): 
 Function Kishan(ByVal d As Double, _<o:p></o:p>
                iLen As Long) As String<o:p></o:p>
    Dim i As Long<o:p></o:p>
  <o:p></o:p>
    For i = iLen To 1 Step -1<o:p></o:p>
        Kishan = Choose(d - Int(d / 3#) * 3# + 1#, ",1", ",X", ",2") & Kishan<o:p></o:p>
        d = Int(d / 3#)<o:p></o:p>
    Next i<o:p></o:p>
 <o:p></o:p>
    Kishan = Mid(Kishan, 2)<o:p></o:p>
End Function<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
Now, I want to have an inverse solution. If I put in A1 through A8 list of combinations will it be possible to get in column B the corresponding number of those combinations as shown in example table 2 below.<o:p></o:p>
<o:p></o:p>
Example Table 2:<o:p></o:p>
<TABLE style="BACKGROUND: white; mso-cellspacing: 0cm; mso-padding-alt: 0cm 2.0pt 0cm 2.0pt" border=1 cellSpacing=0 cellPadding=0 bgColor=white><TBODY><TR><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; BACKGROUND: #cacaca; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; BACKGROUND: #cacaca; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
A<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; BACKGROUND: #cacaca; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
B<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; BACKGROUND: #cacaca; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
1<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">1,1,1,1,1,1,1,1,1,1,1,1,1,1<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
0<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; BACKGROUND: #cacaca; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
2<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">1,1,1,1,1,1,1,1,1,1,1,1,2,X<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
7<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; BACKGROUND: #cacaca; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
3<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">1,1,1,1,1,1,1,1,1,1,1,X,X,1<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
12<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; BACKGROUND: #cacaca; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
4<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">1,1,1,1,1,1,1,1,1,1,1,2,1,2<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
20<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; BACKGROUND: #cacaca; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
5<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">1,1,1,X,2,1,1,2,1,X,X,2,1,X<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
100000<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; BACKGROUND: #cacaca; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
6<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">1,2,X,1,1,X,X,X,2,1,1,1,2,2<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
1250000<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; BACKGROUND: #cacaca; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
7<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">2,1,X,2,1,2,X,X,1,1,2,X,2,2<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
3500000<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; BACKGROUND: #cacaca; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
8<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">2,2,2,2,2,2,2,2,2,2,2,2,2,2<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">
4782968<o:p></o:p>​
</TD></TR></TBODY></TABLE>
<o:p></o:p>
<o:p></o:p>
Thanks in advance,<o:p></o:p>
Kishan
 
Sort by date Sort by votes
Gotta be confirmed with Ctrl+Shift+Enter, Biz.
 
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi

Another option, also array entered:

=SeriesSum(3,13,-1,Match(A2:N2,{1,"x",2},0)-1)

Remark: I'm assuming the 1's and 2's in the table are numbers. If they are text, use the array {"1","2","3"}.

Also, independent of whether the values are numeric or text (or also mixed, some numeric and some text):

=seriessum(3,13,-1,MATCH(A4:N4&"",{"1","x","2"},0)-1)
 
Last edited:
Upvote 0
pgc01 said:
Hi

Another option, also array entered:

=SeriesSum(3,13,-1,Match(A2:N2,{1,"x",2},0)-1)

Remark: I'm assuming the 1's and 2's in the table are numbers. If they are text, use the array {"1","2","3"}.

Also, independent of whether the values are numeric or text (or also mixed, some numeric and some text):

=seriessum(3,13,-1,MATCH(A4:N4&"",{"1","x","2"},0)-1)
Click to expand...

Nice formulas mate.
 
Upvote 0
Maybe to generalize,

=SERIESSUM(LEN("1X2"), COLUMNS(A1:N1)-1, -1, SEARCH(A1:N1, "1X2") - 1)

Blanks are treated as zero, which doesn't seem unreasonable.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
613
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
Adding a custom message.
Replies
1
Views
659
Micron
M
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
920
epactheactor
E

Forum statistics

Threads
1,224,506
Messages
6,179,159
Members
452,892
Latest member
yadavagiri

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