提取个尾的“尾”组合(Extract the tail "tail" combination)

q771021738

New Member
Joined
May 28, 2019
Messages
13

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

q771021738

New Member
Joined
May 28, 2019
Messages
13
Re: 提取个尾的“尾”组合(Extract the tail "tail" combination)

I've rearranged the formula horizontally here

https://drive.google.com/file/d/1xoEl79uoZFXGoa3jnhrtwG-ixZUo4WTY/view?usp=sharing

have a look if the works for you
When I was testing, when I entered new data, less than three digits, an error occurred.
https://drive.google.com/file/d/1a4jddPDg0kjBxx0t479LYH4YoqFHgmXl/view?usp=sharing

Look at this form, this is a friend from China to help.
He used three formulas to calculate, and there is no calculation process for unlocking.
But there is also a problem. When I insert a new column on the left side of column A, the result of the operation will be wrong.
https://drive.google.com/open?id=1JsGBeAHxKKsU629KaCQFaLQOREA19rn1
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
Re: 提取个尾的“尾”组合(Extract the tail "tail" combination)

the problem for your test is the 11, as none of the right between A:F added up to 1 so the calculations are correct.
you can see if you change G to 19

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">101</td><td style="text-align: right;;">102</td><td style="text-align: right;;">103</td><td style="text-align: right;;">104</td><td style="text-align: right;;">105</td><td style="text-align: right;;">106</td><td style="text-align: right;;">102</td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #FFF2CC;;"></td><td style="text-align: right;background-color: #FFF2CC;;"></td><td style="text-align: right;background-color: #FFF2CC;;"></td><td style="text-align: right;background-color: #FFF2CC;;"></td><td style="text-align: right;background-color: #FFF2CC;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">101</td><td style="text-align: right;;">102</td><td style="text-align: right;;">103</td><td style="text-align: right;;">104</td><td style="text-align: right;;">105</td><td style="text-align: right;;">106</td><td style="text-align: right;;">103</td><td style="text-align: right;background-color: #DDEBF7;;">12</td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #FFF2CC;;">12</td><td style="text-align: right;background-color: #FFF2CC;;"></td><td style="text-align: right;background-color: #FFF2CC;;"></td><td style="text-align: right;background-color: #FFF2CC;;"></td><td style="text-align: right;background-color: #FFF2CC;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">101</td><td style="text-align: right;;">102</td><td style="text-align: right;;">103</td><td style="text-align: right;;">104</td><td style="text-align: right;;">105</td><td style="text-align: right;;">106</td><td style="text-align: right;;">104</td><td style="text-align: right;background-color: #DDEBF7;;">13</td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #FFF2CC;;">13</td><td style="text-align: right;background-color: #FFF2CC;;"></td><td style="text-align: right;background-color: #FFF2CC;;"></td><td style="text-align: right;background-color: #FFF2CC;;"></td><td style="text-align: right;background-color: #FFF2CC;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">101</td><td style="text-align: right;;">102</td><td style="text-align: right;;">103</td><td style="text-align: right;;">104</td><td style="text-align: right;;">105</td><td style="text-align: right;;">106</td><td style="text-align: right;;">105</td><td style="text-align: right;background-color: #DDEBF7;;">14</td><td style="text-align: right;background-color: #DDEBF7;;">23</td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #FFF2CC;;">14</td><td style="text-align: right;background-color: #FFF2CC;;">23</td><td style="text-align: right;background-color: #FFF2CC;;"></td><td style="text-align: right;background-color: #FFF2CC;;"></td><td style="text-align: right;background-color: #FFF2CC;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">101</td><td style="text-align: right;;">102</td><td style="text-align: right;;">103</td><td style="text-align: right;;">104</td><td style="text-align: right;;">105</td><td style="text-align: right;;">106</td><td style="text-align: right;;">109</td><td style="text-align: right;background-color: #DDEBF7;;">36</td><td style="text-align: right;background-color: #DDEBF7;;">45</td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #FFF2CC;;"></td><td style="text-align: right;background-color: #FFF2CC;;"></td><td style="text-align: right;background-color: #FFF2CC;;">36</td><td style="text-align: right;background-color: #FFF2CC;;">45</td><td style="text-align: right;background-color: #FFF2CC;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;background-color: #FFC000;;">13</td><td style="text-align: right;background-color: #FFC000;;">107</td><td style="text-align: right;background-color: #FFC000;;">15</td><td style="text-align: right;background-color: #FFC000;;">223</td><td style="text-align: right;background-color: #FFC000;;">22</td><td style="text-align: right;background-color: #FFC000;;">18</td><td style="text-align: right;background-color: #FFC000;;">19</td><td style="text-align: right;background-color: #DDEBF7;;">25</td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #FFF2CC;;"></td><td style="text-align: right;background-color: #FFF2CC;;">25</td><td style="text-align: right;background-color: #FFF2CC;;"></td><td style="text-align: right;background-color: #FFF2CC;;"></td><td style="text-align: right;background-color: #FFF2CC;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br />
 

q771021738

New Member
Joined
May 28, 2019
Messages
13
Re: 提取个尾的“尾”组合(Extract the tail "tail" combination)

the problem for your test is the 11, as none of the right between A:F added up to 1 so the calculations are correct.
you can see if you change G to 19

ABCDEFGHIJKLMNOPQ
1101102103104105106102
21011021031041051061031212
31011021031041051061041313
410110210310410510610514231423
510110210310410510610936453645
613107152232218192525
7

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet4
If "G6" is changed to 19, the result of "H6" should be 27.
——————————
Because the "G6" tail is 9
"B6+E6"=7+2=9
 

q771021738

New Member
Joined
May 28, 2019
Messages
13
Re: 提取个尾的“尾”组合(Extract the tail "tail" combination)

[QUOTE = AlanY; 5285590]你的测试的问题是11,因为A:F之间的任何一个加起来都不是1,所以计算是正确的。
你可以看到你是否将G改为19

一个CdËFGH一世Ĵķ大号中号ñØPQ
1101102103104105106102
21011021031041051061031212
31011021031041051061041313
410110210310410510610514231423
10110210310410510610936453645
613107152232218192525
7

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet4

[/引用]

If "G6" is changed to 19, the result of "H6" should be 27.
——————————
Because the "G6" tail is 9
"B6+E6"=7+2=9
 

q771021738

New Member
Joined
May 28, 2019
Messages
13
Re: 提取个尾的“尾”组合(Extract the tail "tail" combination)

[QUOTE = AlanY; 5285590]你的测试的问题是11,因为A:F之间的任何一个加起来都不是1,所以计算是正确的。
你可以看到你是否将G改为19

一个CdËFGH一世Ĵķ大号中号ñØPQ
1101102103104105106102
21011021031041051061031212
31011021031041051061041313
410110210310410510610514231423
10110210310410510610936453645
613107152232218192525
7

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet4

[/引用]

If "G6" is changed to 19, the result of "H6" should be 27.
——————————
Because the "G6" tail is 9
"B6+E6"=7+2=9
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
Re: 提取个尾的“尾”组合(Extract the tail "tail" combination)

ok, try again

https://drive.google.com/drive/folders/0B1h-Y2fdTl08R0dwYTdSb2pHVkU?usp=sharing

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">101</td><td style="text-align: right;;">102</td><td style="text-align: right;;">103</td><td style="text-align: right;;">104</td><td style="text-align: right;;">105</td><td style="text-align: right;;">106</td><td style="text-align: right;;">102</td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">101</td><td style="text-align: right;;">102</td><td style="text-align: right;;">103</td><td style="text-align: right;;">104</td><td style="text-align: right;;">105</td><td style="text-align: right;;">106</td><td style="text-align: right;;">103</td><td style="text-align: right;background-color: #DDEBF7;;">12</td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">101</td><td style="text-align: right;;">102</td><td style="text-align: right;;">103</td><td style="text-align: right;;">104</td><td style="text-align: right;;">105</td><td style="text-align: right;;">106</td><td style="text-align: right;;">104</td><td style="text-align: right;background-color: #DDEBF7;;">13</td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">101</td><td style="text-align: right;;">102</td><td style="text-align: right;;">103</td><td style="text-align: right;;">104</td><td style="text-align: right;;">105</td><td style="text-align: right;;">106</td><td style="text-align: right;;">105</td><td style="text-align: right;background-color: #DDEBF7;;">14</td><td style="text-align: right;background-color: #DDEBF7;;">23</td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">101</td><td style="text-align: right;;">102</td><td style="text-align: right;;">103</td><td style="text-align: right;;">104</td><td style="text-align: right;;">105</td><td style="text-align: right;;">106</td><td style="text-align: right;;">109</td><td style="text-align: right;background-color: #DDEBF7;;">36</td><td style="text-align: right;background-color: #DDEBF7;;">45</td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">13</td><td style="text-align: right;;">107</td><td style="text-align: right;;">15</td><td style="text-align: right;;">223</td><td style="text-align: right;;">22</td><td style="text-align: right;;">18</td><td style="text-align: right;;">19</td><td style="text-align: right;background-color: #DDEBF7;;">72</td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br />
 

q771021738

New Member
Joined
May 28, 2019
Messages
13
Re: 提取个尾的“尾”组合(Extract the tail "tail" combination)

ok, try again

https://drive.google.com/drive/folders/0B1h-Y2fdTl08R0dwYTdSb2pHVkU?usp=sharing

ABCDEFGHIJKL
1101102103104105106102
210110210310410510610312
310110210310410510610413
41011021031041051061051423
51011021031041051061093645
6131071522322181972

<tbody>
</tbody>
Sheet4
You are looking at it, the sum of the A:F areas, and finally the tail?
——————————————
I don’t know whether to call your teacher or call your friend.
In short, thank you.
https://drive.google.com/open?id=1a4jddPDg0kjBxx0t479LYH4YoqFHgmXl
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
Re: 提取个尾的“尾”组合(Extract the tail "tail" combination)

one more go

https://drive.google.com/file/d/1lWryuzH7LjFOo7YTJkFHh4KuaAHelLjN/view?usp=sharing

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">101</td><td style="text-align: right;;">102</td><td style="text-align: right;;">103</td><td style="text-align: right;;">104</td><td style="text-align: right;;">105</td><td style="text-align: right;;">106</td><td style="text-align: right;;">102</td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">101</td><td style="text-align: right;;">102</td><td style="text-align: right;;">103</td><td style="text-align: right;;">104</td><td style="text-align: right;;">105</td><td style="text-align: right;;">106</td><td style="text-align: right;;">103</td><td style="text-align: right;background-color: #DDEBF7;;">12</td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">101</td><td style="text-align: right;;">102</td><td style="text-align: right;;">103</td><td style="text-align: right;;">104</td><td style="text-align: right;;">105</td><td style="text-align: right;;">106</td><td style="text-align: right;;">104</td><td style="text-align: right;background-color: #DDEBF7;;">13</td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">101</td><td style="text-align: right;;">102</td><td style="text-align: right;;">103</td><td style="text-align: right;;">104</td><td style="text-align: right;;">105</td><td style="text-align: right;;">106</td><td style="text-align: right;;">105</td><td style="text-align: right;background-color: #DDEBF7;;">14</td><td style="text-align: right;background-color: #DDEBF7;;">23</td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">101</td><td style="text-align: right;;">102</td><td style="text-align: right;;">103</td><td style="text-align: right;;">104</td><td style="text-align: right;;">105</td><td style="text-align: right;;">106</td><td style="text-align: right;;">109</td><td style="text-align: right;background-color: #DDEBF7;;">36</td><td style="text-align: right;background-color: #DDEBF7;;">45</td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">13</td><td style="text-align: right;;">107</td><td style="text-align: right;;">15</td><td style="text-align: right;;">223</td><td style="text-align: right;;">22</td><td style="text-align: right;;">18</td><td style="text-align: right;;">19</td><td style="text-align: right;background-color: #DDEBF7;;">72</td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">101</td><td style="text-align: right;;">102</td><td style="text-align: right;;">103</td><td style="text-align: right;;">104</td><td style="text-align: right;;">105</td><td style="text-align: right;;">106</td><td style="text-align: right;;">10</td><td style="text-align: right;background-color: #DDEBF7;;">46</td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td><td style="text-align: right;background-color: #DDEBF7;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,101,783
Messages
5,482,900
Members
407,368
Latest member
FunkyFriedChicken

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top