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

**Brian.Aerojet** · 04-11-2019, 10:00 PM

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,

**bebo021999** · 04-11-2019, 10:55 PM

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.

**Bo_Ry** · 04-12-2019, 01:39 AM

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),"")

**Brian.Aerojet** · 04-12-2019, 11:24 AM

Thanks to you both

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

**AliGW** · 04-12-2019, 11:27 AM

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

**Brian.Aerojet** · 04-13-2019, 12:03 PM

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

**GeoffW283** · 04-18-2019, 01:58 AM

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)

**Brian.Aerojet** · 04-18-2019, 02:41 AM

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

**GeoffW283** · 04-18-2019, 11:57 AM

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.

**GeoffW283** · 04-18-2019, 02:01 PM

And thanks for the feedback and rep

**Brian.Aerojet** · 04-22-2019, 03:24 AM

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

**GeoffW283** · 04-22-2019, 02:26 PM

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:

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.

**Brian.Aerojet** · 04-22-2019, 06:49 PM

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

**GeoffW283** · 04-23-2019, 12:18 AM

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:

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.

**Brian.Aerojet** · 04-23-2019, 01:38 PM

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

**Brian.Aerojet** · 04-23-2019, 02:36 PM

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

**GeoffW283** · 04-24-2019, 12:18 AM

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:

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.

**Brian.Aerojet** · 04-24-2019, 01:31 PM

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

**GeoffW283** · 04-24-2019, 08:24 PM

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.

**Brian.Aerojet** · 04-24-2019, 09:04 PM

sounds great, thanks

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Simple formula to show text from another cell doesn't show font formatting in destination

[SOLVED] Help! If cell contains any sort of date, show green checkmark. Otherwise, show red...

Why Does The Function Bar Show Date in 4 Digits Vs 2 Digits

How to show decimal position to two and then show them as only digits?

Date formula if positive show, if negative don't show.

show first 2 digits from 8 digit number in separate cell

Formula to show only first two digits?

Bookmarks

Bookmarks

Posting Permissions