# 提取个尾的“尾”组合（Extract the tail "tail" combination）



## q771021738 (May 28, 2019)

EXCEL表格：https：//1drv.ms/x/s！AiDf3QxUtCLkavUxZeMNKZL7DRM 

题目：
①计算结果以H2为首个单元格
②计算过程如下：
  （1）在G：G区域，数值取尾
  （2）A2：F2区域，尾数分别相加，结果取尾
  （3）假设RIGHT（A2 + B2）= RIGHT（G2）则把A2与B2的尾数，同放在一个单元格
③组合去重复：例如G4取尾是0 ，A4：F4区域，尾数相加等于0的，只有4和6，有两个4，因此形成46 46两个组合，最终保留一个46组合就可以
④A2：F2例举：
  （1）G2是135取尾是5 
  （2）在A2：F2中，两个尾相加，然后取尾的，只有右（D2 + E2）=右（G2），D2尾是5，E2尾是0，则最终提取05尾，顺序没要求
  （3）由以上，得出符合条件的组合是：“05”


----------



## q771021738 (May 28, 2019)

各位老师，由于我不太懂英文，只能用中文提问了，辛苦各位老师！


----------



## AlanY (May 28, 2019)

what do you expected in H with this set of data?


Book1ABCDEFG12101102103104105106109Sheet4


----------



## q771021738 (May 28, 2019)

*Re: 提取个尾的尾组合（Extract the tail "tail" combination）*


ABCDEFGHI121011021031041051061093645

<tbody>

</tbody>





中文解释：
G2取尾是9
A:F区域，任意两个尾数相加的和值，取尾；与G2的尾相同，则把相通的两位数提取
假设出现多个相同的尾数，则保留一个

Google Translate：
G2 tail is 9
A: F area, the sum of any two mantissas, and the tail; the same as the tail of G2, the two-digit number of the same is extracted
Assuming multiple identical mantissas, keep one


----------



## AlanY (May 28, 2019)

*Re: 提取个尾的尾组合（Extract the tail "tail" combination）*

this is what I came up with but with so many helping rows and columns I doubt that this is what you're after


Book1ABCDEFGHIJ11011021031041051061071625342345671635678254789345910611Sheet4Cell FormulasRangeFormulaB2=RIGHT($A$1)+RIGHT(B1)C3=RIGHT($B$1)+RIGHT(C1)D4=RIGHT($C$1)+RIGHT(D1)E5=RIGHT($D$1)+RIGHT(E1)F6=RIGHT($E$1)+RIGHT(F1)G2=IFERROR(ROW(A2)-ROW($A$1)&MATCH(RIGHT($G$1,1)+0,A2:F2,0),"")H1{=IFERROR(INDEX($G$2:$G$6,SMALL(IF(1-($G$2:$G$6=""),ROW($G$2:$G$6)-ROW($G$2)+1),COLUMN(H:H)-COLUMN($G:$G))),"")}Press CTRL+SHIFT+ENTER to enter array formulas.


----------



## AlanY (May 28, 2019)

*Re: 提取个尾的尾组合（Extract the tail "tail" combination）*

I'd reposted your Q in English here

https://www.mrexcel.com/forum/excel-questions/1099119-numbers-extraction.html#post5284315


----------



## MickG (May 28, 2019)

*Re: 提取个尾的尾组合（Extract the tail "tail" combination）*

Try this:-
 Data assumed to start "A1"

```
[COLOR="Navy"]Sub[/COLOR] MG28May05
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1", Range("A" & Rows.Count).End(xlUp))

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Col = 6
Num = Right(Dn.Offset(, 6), 1)
[COLOR="Navy"]For[/COLOR] Ac1 = 0 To 5
    [COLOR="Navy"]For[/COLOR] Ac2 = Ac1 + 1 To 5
        [COLOR="Navy"]If[/COLOR] Val(Right(Dn.Offset(, Ac1), 1) + Val(Right(Dn.Offset(, Ac2), 1))) = Num [COLOR="Navy"]Then[/COLOR]
            Col = Col + 1
            Dn.Offset(, Col) = Right(Dn.Offset(, Ac1), 1) & Right(Dn.Offset(, Ac2), 1)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Ac2
[COLOR="Navy"]Next[/COLOR] Ac1
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
```
Regards Mick


----------



## q771021738 (May 28, 2019)

*Re: 提取个尾的尾组合（Extract the tail "tail" combination）*

chaina中文：
目前VBA很懂不是
模拟题目，的英文答案正确的
假设模拟题目一：F区域中数字有102212115，那根据VBA计算结果就是25/25 
遇到这种情况，最终重复的只保留一个就可以。
如果能用公式最好，VBA我基本上搞不太懂。
非常感谢你的帮助！
Google Translate：
Currently VBA is not very understanding
Simulation question, the answer is correct
Suppose the simulation problem A: The number in the F area is 102, 212, 115, then the result is 5/25 according to the VBA calculation.
In this case, only one of the last duplicates can be retained.
If I can use the formula best, I basically don't understand VBA.
thank you very much for your help!


----------



## q771021738 (May 28, 2019)

*Re: 提取个尾的尾组合（Extract the tail "tail" combination）*



AlanY said:


> this is what I came up with but with so many helping rows and columns I doubt that this is what you're after
> 
> 
> ABCDEFGHIJ11011021031041051061071625342345671635678254789345910611
> ...


Chaina中文：
瞬间感觉国外的朋友特别热心。
首先非常感谢你热心的解答。
公式计算过程与计算结果都没有问题的。
其次，A:G纵向区域，是数据源，所以计算过程只能横向计算。
=IFERROR(ROW(A2)-ROW($A$1)&MATCH(RIGHT($G$1,1)+0,A2:F2,0),"")
我刚才横向测试的时候，这条公式计算结果是15，不清楚哪里出错了。
Google Translate：
Instantly feel that foreign friends are particularly enthusiastic.
First of all, thank you very much for your enthusiastic answer.
There is no problem with the formula calculation process and the calculation results.
Secondly, the A:G vertical area is the data source, so the calculation process can only be calculated horizontally.
=IFERROR(ROW(A2)-ROW($A$1)&MATCH(RIGHT($G$1,1)+0,A2:F2,0),"")
When I was testing horizontally, this formula calculated 15 and it was not clear what went wrong.


----------



## AlanY (May 28, 2019)

*Re: 提取个尾的尾组合（Extract the tail "tail" combination）*

have you entered the formula with shift-control-enter?

have a look of the file in 

https://drive.google.com/file/d/18uR7LLob4744mK5RXKPjaY86j_e1LxzC/view?usp=sharing


----------



## q771021738 (May 28, 2019)

EXCEL表格：https：//1drv.ms/x/s！AiDf3QxUtCLkavUxZeMNKZL7DRM 

题目：
①计算结果以H2为首个单元格
②计算过程如下：
  （1）在G：G区域，数值取尾
  （2）A2：F2区域，尾数分别相加，结果取尾
  （3）假设RIGHT（A2 + B2）= RIGHT（G2）则把A2与B2的尾数，同放在一个单元格
③组合去重复：例如G4取尾是0 ，A4：F4区域，尾数相加等于0的，只有4和6，有两个4，因此形成46 46两个组合，最终保留一个46组合就可以
④A2：F2例举：
  （1）G2是135取尾是5 
  （2）在A2：F2中，两个尾相加，然后取尾的，只有右（D2 + E2）=右（G2），D2尾是5，E2尾是0，则最终提取05尾，顺序没要求
  （3）由以上，得出符合条件的组合是：“05”


----------



## q771021738 (May 28, 2019)

*Re: 提取个尾的尾组合（Extract the tail "tail" combination）*

[QUOTE = AlanY; 5284353]您是否使用shift-control-enter输入了公式？

看看

https://drive.google.com/file/d/18uR7LLob4744mK5RXKPjaY86j_e1LxzC/view?usp=sharing [/ QUOTE] 中的文件

You see, this horizontal yun, will be a little difficult.
Because the A:G area has a lot of raw data, the operation process cannot be performed.
https://drive.google.com/open?id=1R1uxCHDWExW8pZMw5TQ8PfYKUQmCuW0g


----------



## AlanY (May 29, 2019)

*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


----------



## q771021738 (May 30, 2019)

*Re: 提取个尾的尾组合（Extract the tail "tail" combination）*



AlanY said:


> 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 (May 30, 2019)

*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


Book1ABCDEFGHIJKLMNOPQ110110210310410510610221011021031041051061031212310110210310410510610413134101102103104105106105142314235101102103104105106109364536456131071522322181925257Sheet4


----------



## q771021738 (May 30, 2019)

*Re: 提取个尾的尾组合（Extract the tail "tail" combination）*



AlanY said:


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



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 (May 30, 2019)

*Re: 提取个尾的Â“尾Â”组合（Extract the tail "tail" combination）*

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


一个乙CdËFGH一世Ĵķ大号中号ñØPQ11011021031041051061022101102103104105106103121231011021031041051061041313410110210310410510610514231423五101102103104105106109364536456131071522322181925257

<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 (May 30, 2019)

*Re: 提取个尾的Â“尾Â”组合（Extract the tail "tail" combination）*

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


一个乙CdËFGH一世Ĵķ大号中号ñØPQ11011021031041051061022101102103104105106103121231011021031041051061041313410110210310410510610514231423五101102103104105106109364536456131071522322181925257

<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 (May 31, 2019)

*Re: 提取个尾的尾组合（Extract the tail "tail" combination）*

ok, try again

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


Book1ABCDEFGHIJKL110110210310410510610221011021031041051061031231011021031041051061041341011021031041051061051423510110210310410510610936456131071522322181972Sheet4


----------



## q771021738 (May 31, 2019)

*Re: 提取个尾的Â“尾Â”组合（Extract the tail "tail" combination）*



AlanY said:


> ok, try again
> 
> https://drive.google.com/drive/folders/0B1h-Y2fdTl08R0dwYTdSb2pHVkU?usp=sharing
> 
> ...



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 (May 31, 2019)

*Re: 提取个尾的尾组合（Extract the tail "tail" combination）*

one more go

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


Book1ABCDEFGHIJKL11011021031041051061022101102103104105106103123101102103104105106104134101102103104105106105142351011021031041051061093645613107152232218197271011021031041051061046Sheet4


----------



## q771021738 (May 28, 2019)

EXCEL表格：https：//1drv.ms/x/s！AiDf3QxUtCLkavUxZeMNKZL7DRM 

题目：
①计算结果以H2为首个单元格
②计算过程如下：
  （1）在G：G区域，数值取尾
  （2）A2：F2区域，尾数分别相加，结果取尾
  （3）假设RIGHT（A2 + B2）= RIGHT（G2）则把A2与B2的尾数，同放在一个单元格
③组合去重复：例如G4取尾是0 ，A4：F4区域，尾数相加等于0的，只有4和6，有两个4，因此形成46 46两个组合，最终保留一个46组合就可以
④A2：F2例举：
  （1）G2是135取尾是5 
  （2）在A2：F2中，两个尾相加，然后取尾的，只有右（D2 + E2）=右（G2），D2尾是5，E2尾是0，则最终提取05尾，顺序没要求
  （3）由以上，得出符合条件的组合是：“05”


----------



## q771021738 (May 31, 2019)

*Re: 提取个尾的尾组合（Extract the tail "tail" combination）*

我认为“有些词汇”的翻译不是很标准，我哭了。


----------



## AlanY (May 31, 2019)

*Re: 提取个尾的尾组合（Extract the tail "tail" combination）*



q771021738 said:


> 我认为“有些词汇”的翻译不是很标准，我哭了。



does the latest version works?


----------



## q771021738 (May 31, 2019)

*Re: 提取个尾的Â“尾Â”组合（Extract the tail "tail" combination）*

[QUOTE = AlanY; 5286193]还有一个去

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


一个乙CdËFGH一世Ĵķ大号110110210310410510610221011021031041051061031231011021031041051061041341011021031041051061051423五1011021031041051061093645613107152232218197271011021031041051061046

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

</thead><tbody>

</tbody>*Sheet4*​
[/引用]

Just saw this form. This is no problem.
Thank you very much!


----------



## yky (Jun 6, 2019)

*Re: 提取个尾的尾组合（Extract the tail "tail" combination）*

問題解決否？


----------

