+ Reply to Thread
Results 1 to 20 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
    169

    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 2016
    Posts
    6,506

    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
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,264

    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
    169

    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
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,460

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite 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
    169

    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
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,273

    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
    169

    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
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,273

    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
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,273

    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
    169

    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
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,273

    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
    169

    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
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,273

    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
    169

    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.

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

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

    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

  17. #17
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,273

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

    I didn't explain very well how to adjust the formula for starting on a different row. Where I said "12 plus" I was trying to say that in the formula there were 6 instances of -3 and 6 instances of +3. With a new start row of 87 those 3's need to become 9's. I have made this adjustment the attached workbook. The calculation is actually MOD(ROWS(1:87), 26) = 9

    I have also adjusted the formula to eliminate the duplicates you note like cell B51.
    With that change I noticed a couple of instances where I now disagree with your expected results at B96 and B124 In both of those cases it looks to me like the new formula has correctly eliminated duplicates. Let me know whether I'm understanding this correctly.

    The revised formula in B87 copied down is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Thanks for taking the time to explain you application - hopefully you will make a profit!

    Attached is the workbook with the above changes.
    Attached Files Attached Files

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

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

    Looks good, Im still looking at some more detailed Data, but for now my expected outcome from your new formula works perfect.

    I do have a question, how do you get your formulas in the formula bar to show each section of the formula in a different row on the formula bar instead of a continuous formula? (ALT+Enter) I figured that part out, lol

    it is so much easier to read the way you're doing it.

    When i went to copy and paste it the way you have it, it would not put the entire formula in my desired cell, it's like it put it in 3 consecutive cells

    If Im able to write a formula this way, how do i copy and paste it without this happening?

    Thanks
    Last edited by Brian.Aerojet; 04-24-2019 at 01:37 PM.

  19. #19
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,273

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

    Yes - you figured it out! - Alt-Enter

    To make sure the formula ends up in a single cell: double click the cell before pasting the formula or alternatively click in the formula bar and paste it there.

    Also, just in case you didn't know, you can drag the line immediately below the formula bar so that you can see multiple lines in the formula bar.

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

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

    sounds great, thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 05-29-2017, 11:03 AM
  2. Replies: 2
    Last Post: 02-24-2014, 08:20 PM
  3. Replies: 3
    Last Post: 12-05-2012, 11:03 PM
  4. How to show decimal position to two and then show them as only digits?
    By Ning in forum Access Programming / VBA / Macros
    Replies: 2
    Last Post: 05-15-2012, 09:25 AM
  5. Date formula if positive show, if negative don't show.
    By Cyberpawz in forum Excel General
    Replies: 8
    Last Post: 03-14-2012, 07:49 AM
  6. Replies: 2
    Last Post: 05-09-2011, 11:39 AM
  7. Formula to show only first two digits?
    By legepe in forum Excel General
    Replies: 3
    Last Post: 05-29-2010, 01:41 PM

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