+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 20

Formula to show Date(s) of when 2digts out of 3 digits show up in Cell(s)

  1. #1
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    156

    Formula to show Date(s) of when 2digts out of 3 digits show up in Cell(s)

    Hi Everyone,

    It has been a while since I posted a Thread, but I'm stuck on a formula I need

    I have added a Excel sheet to show example of what I'm looking to do.

    In my excel worksheet attachment I have a column with Dates, a Column with 3 Digit Numbers that are ongoing, a Column with 2 digit numbers (26 of them and will always be the same) and then a column for the formulas that will match the last time the 2 of 3 digit numbers matches the 2 digit number.

    I have a formula for Column A, I manually add column B 3 digit number (Only thing I do Manually on my Workbook), Column D was a one time add only (Don't need formula for this), and Column F will be where the new formula Im looking for, It will only have 26 formulas (Depending on what 2 digit number is in Column D)

    I will need the formula to show the LAST time 2 of the 3 digits matched with the ongoing column from column A and B.

    I have more notes explaining what im looking for in the attachment.

    Thanks,
    Attached Files Attached Files
    Brian

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    4,932

    Re: Formula to show Date(s) of when 2digts out of 3 digits show up in Cell(s)

    Try in cell F2:

    Please Login or Register  to view this content.
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  3. #3
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel365
    Posts
    2,220

    Re: Formula to show Date(s) of when 2digts out of 3 digits show up in Cell(s)

    Please try at F2
    =IFERROR(LOOKUP(2,1/(MMULT(--ISNUMBER(FIND(MID(D2,{1,2},1),B$2:B26)),{1;1})=2)/(LEN(SUBSTITUTE(SUBSTITUTE(B$2:B$26,LEFT(D2),),RIGHT(D2),))<2),A$2:A26),"")
    Attached Files Attached Files

  4. #4
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    156

    Re: Formula to show Date(s) of when 2digts out of 3 digits show up in Cell(s)

    Thanks to you both

    The suggestions worked like a charm exactly the way i wanted it.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    25,389

    Re: Formula to show Date(s) of when 2digts out of 3 digits show up in Cell(s)

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    Are you new to Power Query and don't know what to do with the code you've been given? Have a look here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    156

    Re: Formula to show Date(s) of when 2digts out of 3 digits show up in Cell(s)

    Sorry, It has been a while for me on here. Im showing it Solved now

  7. #7
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    741

    Re: Formula to show Date(s) of when 2digts out of 3 digits show up in Cell(s)

    Per moderation request the following thread is to be continued here.

    https://www.excelforum.com/excel-for...ht-middle.html

    The following matches your expected results from the workbook attached to the above thread.

    In column C2 and copied down:

    =NOT(ISERROR(VLOOKUP(--LEFT(B2,2), $D$2:$D$27, 1, FALSE))) +
    NOT(ISERROR(VLOOKUP(--RIGHT(B2,2), $D$2:$D$27, 1, FALSE))) +
    NOT(ISERROR(VLOOKUP(--(LEFT(B2,1)&RIGHT(B2,1)), $D$2:$D$27, 1, FALSE))) +
    IF(MID(B2, 2, 1)<>LEFT(B2,1), NOT(ISERROR(VLOOKUP(--(MID(B2, 2, 1)&LEFT(B2,1)), $D$2:$D$27, 1, FALSE))), 0) +
    IF(RIGHT(B2, 1)<>LEFT(B2,1), NOT(ISERROR(VLOOKUP(--(RIGHT(B2, 1)&LEFT(B2,1)), $D$2:$D$27, 1, FALSE))), 0) +
    IF(RIGHT(B2, 1)<>MID(B2,2, 1), NOT(ISERROR(VLOOKUP(--(RIGHT(B2, 1)&MID(B2,2, 1)), $D$2:$D$27, 1, FALSE))), 0)

  8. #8
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    156

    Re: Formula to show Date(s) of when 2digts out of 3 digits show up in Cell(s)

    Thanks GeoffW283
    Worked great.

    Could I get you to explain to me this formula broken down? (Show what each part of the formula means)
    Example would be like the 1st line of your formula, I would like to learn more on this type of formulas, but dont know where to start.

    You dont have to, but I thought it would be worth asking.

    Thank you for you help

  9. #9
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    741

    Re: Formula to show Date(s) of when 2digts out of 3 digits show up in Cell(s)

    The formula is repetitive rather than complex. I divided it into 6 lines, one for each permutation of two out of the three digits in col-B, so I think I only need to describe one line, the others work in the same way. I find the best way to understand a formula is to start at the inside and work outwards, so at the heart, as you guessed, is some combination of left(), mid() and right() that picks out one of the 6 two digit permutations of the the three digits in B2, so on the first line:
    • --left(B2, 2) yields "38". The so called double unary operator "--" coerces the text "38" into the number 38.
    • VLOOKUP(38, $D$2:$D$27, 1, FALSE)) - returns 38 if 38 is present in D2:D27 or an error if 38 does not appear in D2:D27
    • The iserror() wrapping the vlookup() returns TRUE if the VLOOKUP() returned an error (meaning than 38 does not appear in D2:D27) and FALSE otherwise. The not() wrapping the iferror() just inverts this logic so that in the end if 38 can be found in D2:D27 then TRUE, or 1, is returned otherwise FALSE, or 0, is returned.
    • The result of each of the 6 lines of the formula is added to give the final result.
    • That's it - except that lines 4 - 6 of the formula are wrapped in an if() which avoids double counting permutations like "88".
    I hope this helps. Let me know if you have specific questions.

  10. #10
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    741

    Re: Formula to show Date(s) of when 2digts out of 3 digits show up in Cell(s)

    And thanks for the feedback and rep

  11. #11
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    156

    Re: Formula to show Date(s) of when 2digts out of 3 digits show up in Cell(s)

    This new question could be similar to what this thread is about, so i will post it here for now. If I need to open a new thread let me know, thanks.

    I will add a new worksheet that also explains in it what im looking for.

    I have 3 digit numbers in column A (A1:A7) that i break down to 2 digits, example is 123, would be (12 or 21, 1st and 2nd number), (13 or 31, 1st and 3rd number), and (23 or 32, 2nd and 3rd number)

    my goal is I have seven 3 digit numbers that the formulas in the B column will look at, it will look at the next 19 cells in column A and match each of the 19 cells and count how many times there is a matching 2 digit number in it. Then it will start over with another 7 cells in Column A that will look and count the next 19 cells.

    You will see i have explained it in more detail on the attachment, please ask any questions if you dont understand what im looking for as my results.

    Thanks,
    BRIAN
    Attached Files Attached Files

  12. #12
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    741

    Re: Formula to show Date(s) of when 2digts out of 3 digits show up in Cell(s)

    Columns D:I are "helper" columns and compute the six two digit permutations of the three digit number in col-A. The formula in D1 is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formulas in E1:I1 are more or less similar. D1:I1 can be copied down as far as is needed.
    In rows 8 through 26 the helper columns G:I are not required and have been omitted.
    These helper columns can be relocated to wherever is convenient for your real workbook.

    Your required formula for the first block of calculations is in B8:B26. In B8 then copied down to B26:
    =SUMPRODUCT(($D$1:$I$7=D8)+($D$1:$I$7=E8)+($D$1:$I$7=F8))

    I don't know how many "blocks" of calculations you have. Your example has two: Rows 1 thru 26 and rows 27 thru 52. The above formula needs manual changes for each block. So in B34 then copied down to B52:
    =SUMPRODUCT(($D$27:$I$33=D34)+($D$27:$I$33=E34)+($D$27:$I$33=F34))

    If you have many calculation blocks you may want a more copy/paste-friendly formula.

    I moved your expected results to col-C. My formulas agree with your expected results.

    The attached workbook implements the above.

    Let me know if this works for you.
    Attached Files Attached Files

  13. #13
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    156

    Re: Formula to show Date(s) of when 2digts out of 3 digits show up in Cell(s)

    GeoffW283 Thanks for taking time to help me.

    Yes this will go in many blocks as you asked above.

    do you have any suggestions on a more copy/paste friendly formula as you mentioned above.

    this pattern will continue on and on, so over time i will have numerous blocks and would like the best formula to achieve this.

    You definitely understand what Im trying to do because your formulas in the attached work book are correct.

    Thanks,
    Brian

  14. #14
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    741

    Re: Formula to show Date(s) of when 2digts out of 3 digits show up in Cell(s)

    OK - here is a formula that is pasteable down col-B with no manual changes necessary. So in B3 copied down as far as needed:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I have added 2 blank rows at the top so that the formula accommodates the general case. In your final workbook, replace the twelve plus and minus 3's with the row number that your data starts on.

    The attached workbook has the above changes.

    Purely out of curiosity are you able to share the purpose of these calculations??

    Let me know if anything else is needed.
    Attached Files Attached Files

  15. #15
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    156

    Re: Formula to show Date(s) of when 2digts out of 3 digits show up in Cell(s)

    Hey Ge0ffW283,

    I'm working on inserting the formulas now in my workbook, I dont think I understand the twelve plus you are referring to.

    My starting point is row 87, so what would i put in the formula and where?

    As for what I'm working on is a hobby of mine that I've had for years.

    It is for Cash 3 lottery, I use to insert a lot of Data by hand, but the more familiar I get with excel, the more I can do with past history of Cash 3 lottery.

    As you can see in my data, I break down 3 digit numbers to 2 digit numbers and it helps me predict future drawings.

    I have had so many people helping me from Excel Forum website, it has helped me tremendously.

    Again, I love playing Cash 3 lottery and also love Excel formulas, so it is a great combination for me in learning both.




    Also, Is there anyway to add in the formula to not show duplicates in individual cells in column B?

    Example would be Cell B51 counts the 98 twice because it shows up in H33 and I35.

    If possible I just want to match or count example like this one time.

    Thanks
    Last edited by Brian.Aerojet; 04-23-2019 at 02:37 PM.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1