Find matching unique numbers in any order or find duplicate numbers in any order

    Hello Fellow Ozgrids:


    I have been have a tough time on finding a solution to my problem. I’ve search both Ozgrid Excel help forum and YouTube, but could not seem to find a resolve. Here’s the issue:


    I am trying to find matches of any order from the Draw 4 or Draw 3 listed in columns E and K when compared against the solution columns D and J.


    The formula that I currently have here is:
    =SUMPRODUCT(--ISNUMBER(SEARCH(E3,D3)))=COUNTA(E3)……copied down seems to only work if the solution number is in the exact order of the corresponding draw 4 or draw 3 number.


    This is not what I need the formula to do. I need the formula to be able to provide a result if the solution is in any order or exact order of the DRAW 3 or 4. Therefore, the draw 4 and draw 3 results would have also included the highlighted cells as TRUE. When the formula is working like I require the results would show 3 TRUE results for both Draw 4 and Draw 3.


    As an example: Solution #4 in cell D4 is 252333; hence a TRUE result is provided only because the formula finds Draw 4 2333 in exact order of the solution number.


    However, Solution#9 in cell D9 is 33213; and Solution#11 in cell D11 is 73529335 both results in a FALSE match because it’s not in order of Draw 4, but it does contain all four numbers to yield a resulting match.


    As an example: Solution #1 in cell J1 is 45901; hence a TRUE result is provided only because the formula finds Draw 3 459 in exact order of the solution number.


    However, Solution#3 in cell J3 is 349654; and Solution#13 in cell J13 is 456894 both results in a FALSE match because it’s not in order of Draw 3, but it does contain all three numbers to yield a resulting match.


    Sample file attached to illustrate the issue.

    Deciog,


    You are the MAN!!! Thanks a million!!!!!!!!!! :congrats::tumble::sing::dance:


    The formula works perfectly as I always imagined that it would. I could not have figured this out without your expertise. Would you mind explaining to me how each element of the formula works for future reference?

    I apologize, Ingles is not my native language, I am from Brazil, Portuguese language, I use Google Translator, it can translate wrong.


    Formula ""*"&MID($E3,ROW(INDIRECT("$1:$"&LEN($E3))),1)&"*"" transforms into {"* 4 *", "* 5 *", "* 9 *"}


    Transforms Word into each letter


    Formula "SEARCH("*"&MID($E3,ROW(INDIRECT("$1:$"&LEN($E3))),1)&"*",D3,1)" transforms in {1; 1; 1}


    Search if you have the same in the text, each letter, finding mark number 1


    Formula "IFERROR(SEARCH("*"&MID($K5,ROW(INDIRECT("$1:$"&LEN($K5))),1)&"*",J5,1),0)" transform into {1; #VALOR!; 1}


    Transforms error in value zero (0)


    Formula "SUMPRODUCT(IFERROR(SEARCH("*"&MID($K5,ROW(INDIRECT("$1:$"&LEN($K5))),1)&"*",J5,1),0))" , the result of the search results of sum is 3


    compares with the amount of character that has the word


    If equal then correct has all the characters


    I hope I have helped, click on Like so is a way of thanking


    Decio

    Hi Deciog,


    My apologies on saying that the formula works perfect as I did not evaluate against duplicate numbers. I just did some random number entries and it does not provide a correct answer of "No" when the number is a duplicate, however the any order part is working properly so don’t change that part of the formula.


    As an example:


    Draw 4 =1213. The ones are duplicate numbers, so the formula should only provide a “Yes” if it finds all four numbers in any order, (e.g. is must find two (1)’s) to answer correctly, otherwise the result is No).


    Solution #8 in cell D10 is 346112; hence a “YES” result is provided because the formula finds all 4 numbers from the Draw 4 in cell E10 “1213” in any order of the solution number.


    However, Solution#9 in cell D11 is 33213; it provides a “Yes”, but this is incorrect because the solution number does not contain two ‘1’s as a match to the Draw 4 in cell E11. It does contain 3 of 4 numbers from the solution number in cell E11 (e.g. 2, 1, 3) I think the formula is seeing the number “1” and since it is there it gives the yes, but it should be looking for two number “1’s. Therefore only cell G10 would result in a “Yes”.


    As an example:


    Draw 3 =121. The ones are duplicate numbers, so the formula should only provide a “Yes” if it finds all three numbers in any order, (e.g. is must find two (1)’s) to answer correctly, otherwise the result is No).


    Solution#5 in cell J7 is 21199 and Solution#8 in cell J10 is 346112; hence a “YES” result is provided in cells M7 and M10 because the formula finds all 3 numbers from the Draw 3 “121” in any order of the solution number.


    However, Solution#6 in cell J8 is 4125 and Solution#9 in cell J11 is 33213; it provides a “Yes”, for both, but this is incorrect, because neither solution number has two ‘1’s as a match to the Draw 3 in cells K8 or K11. Both solution numbers only contain 2 of 3 numbers from cells K8 or K11 (e.g. 2, 1). I think the formula is seeing the number “1” and since it is there it gives the yes, but it should be looking for two number “1’s. Therefore only cells M7 and M10 would result in a “Yes”.


    Sample file attached to illustrate the issue.

    I managed to understand the translation but I am not finding a solution to this problem, I did not give up I am doing tests, but if any member of this forum find the solution please post.


    Decio

    Try this Array* formula in F3:


    =ISNUMBER(FIND(TEXT(SUM(SMALL(--MID(E3,ROW(INDIRECT("1:"&LEN(E3))),1),ROW(INDIRECT("1:"&LEN(E3))))*10^(LEN(E3)-ROW(INDIRECT("1:"&LEN(E3))))),REPT("0",LEN(E3))),TEXT(SUM(SMALL(--MID(D3,ROW(INDIRECT("1:"&LEN(D3))),1),ROW(INDIRECT("1:"&LEN(D3))))*10^(LEN(D3)-ROW(INDIRECT("1:"&LEN(D3))))),REPT("0",LEN(D3)))))


    copied down


    This method sorts digits in the respective cells from smallest to largest and then looks for an exact match of the draw number in the solution number.


    [arf]*[/arf]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Hi NBVC,


    Thanks for your reply and formula from the attachment in this thread.


    It seems that your formula only provides a correct TRUE most of the times for duplicate numbers, however in some combination variances with duplicate numbers it did not indicate the correct number of TRUE’s at all.
    .
    For example: If the DRAW 4 # is 1213; 4546; 2991; 1612; or 9151 or any combination of these numbers the formula provides the correct # of TRUE’s.


    However, if the DRAW 4 # is 8644 the result should equal two (2) True’s, instead it provides all False.


    Next, if the DRAW 4 # is 3325 the result should equal two (2) True’s, instead it provides all False.


    Next, if the DRAW 4 # is 1612 the result should equal one (1) True, instead it provides all False.


    Furthermore, the formula only provides the first True only when the DRAW 4 # is all single numbers (e.g. no duplicate numbers).


    For example: If the DRAW 4 # is 3952; 6894 or 3614 or any combination of these numbers the formula provides the first TRUE only. Each of these numbers should have had two (2) True’s.


    Deciog, formula seems to work really well for DRAW 4 with only single numbers (e.g. no duplicate numbers).


    Please advise on a fix?

    Give this one a go in F3, copied down...


    =IF(SUMPRODUCT(--((LEN(E3)-LEN(SUBSTITUTE(E3,CHAR(ROW(INDIRECT("48:57"))),"")))>(LEN(D3)-LEN(SUBSTITUTE(D3,CHAR(ROW(INDIRECT("48:57"))),""))))),"X",E3)=E3

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Hi Deciog,


    I'm not sure I totally understand your questions, possibly the translator is not providing a good understanding of your questions. Thank you for not wanting to give up and your continue efforts on trying to find a resolve.


    What I was trying to make you understand is that your formula works perfect as long as the DRAW 4 or DRAW 3 contains all single digits (e.g. 1,2,3 or 1,2,3,4) no repeat numbers.


    Whenever it contains duplicate numbers (e.g. 1,1,2 or 1,2,2,3) the formula does not work properly. The reason why is because your formula looks for only one instance of a number not multiple in the case when a duplicate number exists.


    I need the formula to accurately provide a true/false or yes/no on duplicates or single digit DRAW 3 or DRAW 4 for each solution provided.


    Hopefully the translator provides you a better understanding of my requirements.


    Thanks for your help! :)

    Hello NBVC,


    Your are my HERO!!!!! Thanks you so much for clearly understanding the need. I have tried multiple combinations and the formula works PERFECT!!!!! I had a good feeling that both the logic from the previous formulas could help make a resolve.


    Now this last formula is the right formula: =IF(SUMPRODUCT(--((LEN(E3)-LEN(SUBSTITUTE(E3,CHAR(ROW(INDIRECT("48:57"))),"")))>(LEN(D3)-LEN(SUBSTITUTE(D3,CHAR(ROW(INDIRECT("48:57"))),""))))),"X",E3)=E3


    Could you kindly explain the various elements of how it works as it is quite different, especially the part that shows CHAR and" 48:57" ?


    Thanks again! :congrats::thumbcoo::thanx::cheers::yourock:


    Sincerely,
    Scratchmaster

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login