Use Weekday function with (Countif Sumproduct, +Index) also If(IsError(search(mid +Index

Hello Everyone,

I need help with Offset or Index Function.

R26 is the answer between F26 to F22.
R26 is taking the 3 digit number in F26 and seeing if any of the 3 "single" digits match any of the 3 "single" digits in F22, If one of the 3 "single" digits does match, the formula should return Yes in R26, and No if none of the single digits match.

In T26, same principal as R26 except it corresponds with F26 and F13. So it takes F26 and F13 together to see if any of the digits match and then returns a Yes or No in T26.

V26 same thing, except it corresponds with F26 and F3.

Next is the part of the formula Im having trouble with.
The part of the formula i don't have should know to skip 1 day a week(Sunday) and also the Yes and No's will index or offset according to the weekday formula that is in Column F.
This way the Yes and No's in Columns R,T, and V will always stay across from the 3 digit number that is in F column.

The same rules as above will go in AK, AM, AO but they look at the Y Column for the matching 3 digit numbers, instead of F column.
So AK27 looks at Y27 and Y23 for a match, AM looks at Y27 and Y14, then AO looks at Y27 and Y4.

The only difference in the last columns BC, BE, BG is it does not skip (Sunday) the Yes and No's will look at Sunday as well as the other 6 days. These 3 will look in the AQ column for matches.
So, BC21 looks at AQ21 and AQ18 for match, BE21 looks at AQ21 and AQ9, BG looks at AQ21 and AQ 2 for a match.

The pattern from the above formulas are dependent on the day that is in formulas of
column F for R, T and V.
column Y for AK, AM, and AO
column AQ for BC, BE and BG

Feel free to ask questions

Thanks in advance,

Brian

I have updated my original post hoping to better explain what I'm looking for.

Thanks,
Brian

Try this

in Column W ( I used this as a "helper" but it could be any column you choose)

in W2

=IF($F2="","",MAX($W$1:$W1)+1)

In R26

Please Login or Register  to view this content.

in T26

Please Login or Register  to view this content.

in V26

Please Login or Register  to view this content.

John,
It worked like a charm.

Do you care to work out the BC, BE, BG Columns, they're a little different since they use all 7days. I will upload what I have.

I made the helper column BH

Thanks,
Brian

I had an issue one time with my formulas just up and quit working. I was under the impression it was because I was on the 5000 row when it quit working and I had $5000 in the formula. So in other words, I guess I thought when I get down to the 1000 row, the formula would quit working.
Sorry, Im still pretty new to this, but Im addicted to excel and trying to learn everything I can.
I jump around to much with it though, I miss some of the simple things.

Anyway, Im working on the last set right now BC, BE, and BG.

I will let you know when I get it

Thanks again big time for your help.

Brian

Put the appropriate formula in BC21, BE21 and BG21 (see below for BC21: change $BH21-1 to $BH21-4 and $BH21-7 for BE anf BG)

Please Login or Register  to view this content.

you can then copy the formula down as far as required.

The above has a maximum range of row 10000: if your range is much greater/much less than 10000, change as required. Generally do not make ranges bigger than necessary to avoid unnecessary performance hits.

If your range varies significantly then there is a feature called [Dynamic] Named Range ([D]NR) which, as the name suggests, dynamically alters the range (rows).

Another option is the use of TABLES (of which I am not a particular fan but others love 'em!) which has the "dynamic" feature "built in".

As far as learning goes, we have all been where you are (and the learning never stops!) so keep on "trucking"; having a go oneself is the best (if sometimes painful) way to learn.

Thanks John, that last formula you just sent worked a lot better. Now when I drag it down, it puts everything in proper place.

I do have a question, What is a good way for me to learn how to write a formula that skips the rows like you have for me?

I have several other formulas that also skip 2, 3, and even 12 rows. I think I have the part down if Im just pulling from a different sheet.

For example, If I pull a date from one of my sheets to a 2nd sheet, but in the 2nd sheet i need the date to skip 4 rows ever time i drag down the formula I use something like this
INDEX('sheet1'!$A:$A,2+INT((ROWS($2:454)-1)/4))

What I'm trying to figure out is, if I did not have another sheet and needed to calculate the date to do this without a place to pull from?
Or like in the sheet you helped me with, I do some different things regarding back for all seven numbers. Example is I see how many times 1 of the numbers out of the 3 digit numbers show in last 7 numbers and I record that across a 0-9 number line. if for example 856 and 946 was 2 of the seven and this was the only 2 times that 6 shows in the seven numbers, then a 2 would go in under 6 on the number line.

Its confusing to even me, but I do have a formula for it, I just need to change it a little to do like you did for me. When i used the formula before it just went row after row, now it goes along with the NO, Yes that we already completed.

I just can't get this skipping of rows in my formulas correctly.

This is the formula I'm using for the F column

SUMPRODUCT(COUNTIF(G$1,MID($F3:$F22,{1,2,3},1)))

Oh, by the way. EXCELLENT JOB with the formulas you helped me with.

Thanks,
Brian

Thanks John,

That does help, I will practice it some to see if I understand it correctly.

I will go ahead and show thread solved and add reputation for you.

Once again, Thanks for the help.