# 4n to be Modify to work with 3n DATA -- week num

4n to be  Modify   to work with  3n DATA--buttons in sheet byplay

29228882.xlsb

1- Modify   to work with  3n DATA --J1 button  "Week Top Num"

2- button  select TK

Microsoft ExcelVBAMicrosoft Office

Last Comment

8/22/2022 - Mon
Martin Liss

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.

Great Job !! Best Expert !

Identify that RANGE PAIRS AND TRI sections in BYPLAY need to be ADJUSTED as shown i did 32W2013 as sample

here how is now

here how should be

Martin Liss

If what I did is not correct, why did you select it as the answer?

Well the question is with two buttons and the answer has much more done

I just have to adjust some parts

Martin Liss

Is this a change to the requirements? If not please be specific as to what cells are wrong.

we have in tha range area this situation

is not counting the correct way

this is how now for 32W2013

this how should be for 32W2013

first day is 019    range is  00  10  because 01 19 09    so 00 then 10  are the first order then the sequence from Mayor to minor

then 480  is 48 80 40  so 40 is leading

as you see the hit numbers have more 40 than 00  that's the correction
00 is the second place in the week same for the others sequences weeks

Martin Liss

I need a reminder of how Week Range is supposed to work so for Mon PM of 32W2013 please tell me what ORDs give a hit for 00 and what ORDs for that week give a hit for 40.

we do one then add  the new until the last day

32W2013

019  =  01   10    09    =  range  =  00 10   =  then  TOTAL RANGE by TIME is  00  10  90 80 70 60 50 40 30 20

then

480 = 48 80 40   = range =  40 80  =   then we add to  TOTAL BY TIME   the count
=  40  00  80  10  90  70  60  50 30 20

then

255 = 25  55    =  range=  20 50 =  then we add to TOTAL RANGE BY TIME  40  00 80   20  10  50 90  70  60 30

then

011 =  01 11  = range  =  00 10  = then we  add    to TOTAL RANGE   00 40 10  80 20 90  50 70 60 30

then

437  = 43  37  47  = range = 40 30  40  then add to the Total RANGE by time = 40  00 10  80  20 30 90 50  60  70

as we one day happens then we keep adding the new ones Ranges.

019 =019
then
+
480 = 019 + 480

then
+
255 = 019 + 480 + 255

so until the las day in the week

Martin Liss

I was talking about MON PM, so shouldn't MON AM (019) be ignored?

I put together this little workbook. If what I have done is correct it looks like 40 has by far the most pairs.
Pairs.xlsm

you  miss 019 is the first  3N
Martin Liss

you  miss 019 is the first  3N
Are you saying that MON AM should be included in column T (MON PM)?

Yes Sure!!

give me  an chance i find an work book you did
so i will prepare an resume for week 32w2013

so get it more clear

you did an good resume but let shows working on 32w3013 now

I wanted to say that I found a way to show what I want to say and I am preparing an easier way to see it and understand it, it will just be an adjustment, nothing new

so we get this

this is what i means

 MON MON TUE TUE WED WED THU THU FRI FRI SAT SAT 30 30 30 30 30 30 50 50 50 50 20 30 50 50 60 60 60 60 10 20 30 50 70 70 70 70 70 70 20 50 50 80 80 10 10 10 10 10 10 50 80 80 10 10 80 80 80 20 20 80 80 10 10 20 20 20 20 20 80 80 10 00 00 40 00 00 40 00 00 00 00 00 00 40 40 00 40 40 00 40 40 40 40 40 019 019 019 019 019 019 019 019 019 019 019 019 480 480 480 480 480 480 480 480 480 480 480 255 255 255 255 255 255 255 255 255 255 011 011 011 011 011 011 011 011 11 437 437 437 437 437 437 437 437 201 201 201 201 201 201 201 079 079 079 079 079 079 480 480 480 480 480 465 465 465 465 244 244 244 801 801 400

Martin Liss

I'm sorry to say I have no idea why you posted all of that. And if you are asking me to change what is done on the BYPLAYS worksheet that's nor something i'm willing to do now.
Lamento decir que no tengo idea de por qué publicaste todo eso. Y si me está pidiendo que cambie lo que se hace en la hoja de trabajo BYPLAYS, eso no es algo que esté dispuesto a hacer ahora.

I understand
Martin Liss

I made a change to Week Range that I hope corrects the problem. If it does then I suspect that the 4N version is wrong. Please check on that and let me know. Are Pair Top and Triple Top in this workbook correct? In any case please don't give me any more points in this question.

Hice un cambio en el Week Range que espero que corrija el problema. Si es así, sospecho que la versión 4N es incorrecta. Por favor verifique eso y avíseme. ¿Son correctos Pair Top y Triple Top en este libro de trabajo? En cualquier caso, por favor no me des más puntos en esta pregunta.
29228918a.xlsb

4N needs to be adjusted for or at least verified

Are Pair Top and Triple Top correct in this workbook?

They don't need to be adjusted too

Martin Liss

Is Week Range in 4N correct? Yes or no please.
Are Pair Top and Triple Top in 3N correct? Yes or no please.

Is Week Range in 4N correct?  yes
Are Pair Top and Triple Top in 3N correct? No
Martin Liss

Is this correct? If not please show me one of the columns that is wrong.

This is the last 4N i knows was done

i have done 32w2013

we look for the repeat PAIRS only in PAIRS TOP when happens

 MON MON TUE TUE WED WED THU THU FRI FRI SAT SAT 1 1 0 0 0 0 9 8 9 9 1 4 8 8 8 9 7 7 7 1 6 6 6 7 5 5 5 6 4 4 4 5 3 3 3 3 2 2 2 2

i miss sat am  801
Martin Liss

Why is WED AM blank?

Why is WED AM blank?

It is blank because it does not have repetition of pairs at that moment

It is really important to see the pairs and count them at the moment that they only occur
Martin Liss

I don't understand what you just said but why isn't WED AM the same as WED PM?

in Pairs Top

I am only interested in counting the pairs that are repeated

if a pair repeats the moment it occurs

example

the first pair that repeats is 01

This repeats on Tuesday PM so I count and show that pair on TUESDAY PM

on Wednesday AM there is no repetition of pairs
therefore I have nothing to tell and I do not show anything

Then on Wednesday PM there is another repetition

So I count all the repeats of those pairs for Wednesday pm

for Thursday, Thursday AM there is a repetition so we count all the pairs the repetitions for Thursday AM that are repeated

for Thursday PM there is a repetition where a pair so we count all the repetitions of pairs for Thursday PM

when a repetition occurs we count all the pairs that are repeating

when no repetition occurs we do not count anything or show anything
Martin Liss

For WED AM the rows in 32W2013 are rows 1084 to 1089, so the ORDs are 019, 480, 255, 011, 437 and 201.

Pairs
019 -> 01, 19, 09
480 -> 48, 80, 40
255 -> 25, 55
011 -> 01, 11
437 -> 43, 37, 47
201 -> 20, 01, 21

So since there are four 40 pairs, shouldn't WED PM show the pair counts?

So since there are four 40 pairs, shouldn't WED PM show the pair counts?

in this situation we are not counting RANGE

we counting PAIRS  the exact PAIRS

019 ->  01, 19, 09  none here
480 ->  48, 80, 40  none here
255 ->  25, 55       none here
011 ->  01, 11                        01 2times============================= have to be count and shown
437 ->  43, 37, 47     none here
201 ->  20, 01, 21                  01 3 times ============================= have to be count and shown
079 --    07  70   09               we have here 09  two times plus 01 3 times  have to be ============counts and shows
480  --  48 80 40                 we have to count 48 80 40   plus 09  two times and 01  3times =======count and shown
465 --   46 65 45   none here
244  --  24 44      none  here
801  --  80  01  81       now we have 80  2times 01 4times  plus  09  2times  =============count and shown
400 -- 40 00                 now we have 40- 3times and  80 2 times ans  09 2 times and 01 4times  == count and show

when you count the repeats PAIRS it will give count by digit from top to minor

--

Martin Liss

201 ->  20, 01, 21                  01 3 times ============================= have to be count and shown

Then why is WED AM blank?

this is WED
AM       437 ->  43, 37, 47     none here
PM       201 ->  20, 01, 21                  01 3 times ============================= have to be count and shown

if not PAIR repeat not count shown
we only shown the count when an PAIR is repeat
Martin Liss

I believe you are wrong. The ORDs that are searched for pairs for WED AM are in rows 1084 to 1089. 019 has one "01", 011 has a second "01" and 201 has a third "01" so WED AM should not be blank.

Best Expert !!

What happens is that Wednesday AM there is no repetition of any couple

They are only taken into account when there is a repetition of one of the couples by the week

It is a rule to only see when there is a change in the repetitions of the pairs
Martin Liss

I believe I am doing exactly what 4N does and since I see 3 repetitions of the 01 couple I don't understand you. Please explain why 019 201 and 201 don't result in the "01" couple having a count of 3.
Martin Liss

It is a rule to only see when there is a change in the repetitions of the pairs.
Are you saying that WED AM is blank because it would have the same values as TUE PM? If so then look at this picture from 4N. It doesn't do that.

we have togoes by each ocurrence

MON  AM   019
MON  PM   480
TUE   AM    255
TUE   PM    011    here we count and shown 01  because here is where it happens 2 times  so we count here and showsn here
WED AM    437
WED PM    201   here we count because 01 repeat 3 times  then we count and we shown
THU AM    079     here we count because 09 repeat 2 times so me shown the 09 2 times plus 01  3times

but in 3N is more easy to see it first
Martin Liss

Are you saying that WED AM is blank because it would have the same values as TUE PM?

yes!
Martin Liss

Okay now we are getting somewhere.

In your picture that you show here, shouldn't WED PM also be blank because it's the same as TUE PM?

I also think that your counts for THU AM and THU PM are wrong because this is what I get now.

maybe you are confusing but that photo is not mine

If there is a repeating couple, it is added to the ones that already are

we have to goes by each ocurrence

MON  AM   019
MON  PM   480
TUE   AM    255
TUE   PM    011    here we count and shown 01  because here is where it happens 2 times  so we count here and showsn here
WED AM    437
WED PM    201   here we count because 01 repeat 3 times  then we count and we shown
THU AM    079     here we count because 09 repeat 2 times so me shown the 09 2 times plus 01  3times
THU PM   480      Here  we count  because  48 2times  80 2times   40  2times   and add to 09 2times  and 01 3times  andshows

FRI AM 801   HERE 80  AND 01  repeat so me add to the count
FRI PM  400     HERE  40  repeat and add to the pairs count and shown

Martin Liss

maybe you are confusing but that photo is not mine
Of course it is! It's from the comment where you said "This is the last 4N i knows was done".
Martin Liss

Please see the Pairs Top worksheet.
Pairs.xlsm

 MON AM MON PM TUE AM TUE PM WED AM WED PM THU AM THU PM FRI AM FRI PM SAT AM SAT PM 019 019 019 019 019 019 019 019 019 019 019 019 480 480 480 480 480 480 480 480 480 480 480 255 255 255 255 255 255 255 255 255 255 011 011 011 011 011 011 011 011 011 437 437 437 437 437 437 437 437 201 201 201 201 201 201 201 079 079 079 079 079 079 480 480 480 480 480 465 465 465 465 244 244 244 801 801 400 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 09 09 01 01 09 09 48 48 48 48 48 48 80 80 80 80 80 80 80 80 40 40 40 40 40 40 09 40 09 09 09 0 0 0 0 0 0 1 1 1 4 8 8 9 9 9 1 4 4 8 8 8 9 1 1 7 7 7 8 9 9 6 6 6 7 7 7 5 5 5 6 6 6 4 4 4 5 5 5 3 3 3 3 3 3 2 2 2 2 2 2

Pairs (1).xlsm

Here as needed
Martin Liss

It seems like you are saying that given an ORD like 019 that you don't break it down into 01, 19 and 09 (the 1st 2 digits, the 2nd 2 digits and the first and last digit, you just break it up into 01 and 19 (the 1st 2 digits and the last 2 digits). Is that true? If so 4N is wrong too.

011   =  01   11

in this case we just looking for the repeats   so the 01  is the repeat  so we used it  to be counted

we are  doing  now  PAIRS Top  = repeat PAIRS  for the week

we looking for the PAIRS that repeat for the week then count and shown only the PAIRS that repeat
Martin Liss

What about a number like 019?

019  =  01 19  09

we do not change from where the 3N or 4N pairs belong (in the case of 4N) we only identify the PAIRS that are repeated during the week

Here what we are doing is counting as they occur and cumulatively. We count all the PAIRS that REPEAT but have to be in each occurrence in which it is repeated (occurs)

Martin Liss

In my examples I've been talking about pairs that occur 3 or more times. Are your examples doing that?

Great Question !!!
you in the way Best Expert

here is not the case of 4N

here we are in 3N  we see Pairs the repeat 2 times or more

4N have a lot of PAIRS so  we try to take the top

but 3N is not the same scenario we take the  2 times or more  so we can count and shown as they occurs

we only count when an PAIR repeat then add to the others that already are ( if the case)

that why

 MON AM MON PM TUE AM TUE PM WED AM WED PM THU AM 019 019 019 019 019 019 019 480 480 480 480 480 480 255 255 255 255 255 011 011 011 011 437 437 437 201 201 079 none repeat none repeat none repeat none repeat YES REPEAT HERE YES REPEAT HERE YES!! 01 01 01 01 01 01 01 01 09 09 count as shown count as shown count as shown 0 2 times 0 3times 0 5 times 1 2 times 1 3 times 1 3 times 9 2 times then then then 0 0 0 1 1 1 9 9 9 8 8 8 7 7 7 6 6 6 5 5 5 4 4 4 3 3 3 2 2 2

Martin Liss

In 4N you are asked "Count pairs that occur 3 or more times or count pairs that occur 2 or more times?". Do I ask that question in 3N?

in 4n  3 times or more
Martin Liss

En 4N se le pregunta "¿Cuenta los pares que ocurren 3 o más veces o cuenta los pares que ocurren 2 o más veces?". ¿Hago esa pregunta en 3N?

for 3n  The pairs that happens 2 times or more to be count and shown

for 4N the Pairs that happens 3 times or more   to be count and shown
Martin Liss

for 4N the Pairs that happens 3 times or more   to be count and shown
That's not what you asked for when we developed 4N but I'll change it.

that would be an opportunity to choose as necessary

and it would be good

i was tested now

you're right, i didn't understand

you're right it's a selector

for pairs that repeat 2 or more times

or pairs that repeat 3 or more times

it's a good idea

and the best way to go
as i can adjust as necessary
Martin Liss

This is getting ridiculous.

In the existing 4N workbook you can already choose between 2 or more occurrences or 3 or more occurrences. Do you want me to change that so it's always 3 or more occurrences? Yes or No please.

Esto se está poniendo ridículo.

En el libro de trabajo de 4N existente, ya puede elegir entre 2 o más ocurrencias o 3 o más ocurrencias. ¿Quieres que cambie eso para que siempre sean 3 o más apariciones? Si o no por favor.

No don't change it

4n gives me the opportunity to choose 2 pairs or 3 pairs that are repeated

doing it like this in 3N would be fine

I apologize is that I had not realized my mistake

but something like that of choosing 2 pairs or more that are repeated

or 3 pairs or more that are repeated is good for me

What I do want to explain is the mechanics of counting the pairs and how they should be shown and why the pairs are chosen
Martin Liss

In 3N do you want to be able to choose between 2 or more occurrences or 3 or more occurrences? Yes or no please.

¿En 3N quieres poder elegir entre 2 o más ocurrencias o 3 o más ocurrencias? Si o no por favor.

Yes!
Martin Liss

For 2 or more occurrences I get this.
Para 2 o más ocurrencias obtengo esto.

But since you answered "Yes" to my "Are you saying that WED AM is blank because it would have the same values as TUE PM" question the final result is this.
Pero como respondiste "Sí" a mi pregunta "¿Estás diciendo que WED AM está en blanco porque tendría los mismos valores que TUE PM?", El resultado final es este.

ok having a selector that gives me the opportunity to choose between 2 or 3 repeating pairs is one thing

but the mechanics of choosing pairs is another

that is, if it is wrong, it does not help us

this is the way to work with

Martin Liss

I'm having difficulty developing a foolproof way of determining couples. As I see it there are 4 cases.

1. All three digits in the ORD are the same. Example 444. That should yield just one couple: 44
2. The right two digits are the same. Example 011. That should yield 2 couples: 01 and 11
3. The left two digits are the same. Example 440. That should yield 2 couples: 44 and 40
4. All three digits are different. Example 019. That should yield 3 couples: 01, 19 and 09

Please just answer correct or incorrect to the four statements. For any incorrect statement just tell me what the couple(s) should be.

Estoy teniendo dificultades para desarrollar una forma infalible de determinar las parejas. A mi modo de ver, hay 4 casos.

1. Los tres dígitos del ORD son iguales. Ejemplo 444. Eso debería producir solo una pareja: 44
2. Los dos dígitos de la derecha son iguales. Ejemplo 011. Eso debería producir 2 parejas: 01 y 11
3. Los dos dígitos de la izquierda son iguales. Ejemplo 440. Eso debería producir 2 parejas: 44 y 40
4. Los tres dígitos son diferentes. Ejemplo 019. Eso debería producir 3 parejas: 01, 19 y 09

Por favor, responda correcta o incorrectamente a las cuatro afirmaciones. Para cualquier afirmación incorrecta, dígame qué pareja (s) debería (s) ser.

1-All three digits in the ORD are the same. Example 444. That should yield just one couple: 44

Correct

2-The right two digits are the same. Example 011. That should yield 2 couples: 01 and 11

Correct

3-The left two digits are the same. Example 440. That should yield 2 couples: 44 and 40

Correct

4-All three digits are different. Example 019. That should yield 3 couples: 01, 19 and 09

Correct
Martin Liss

I implemented those 4 rules and my results don't agree with yours
TUE PM  - Shown
2 1's
2 0's
WED AM - Not show because it's the same as TUE PM
2 1's
2 0's
WED PM - Not show because it's the same as WED AM
3 1's
3 0's
THU AM  - Shown
5 0's
3 1's
2 9'2
THU PM  - Shown
9 0's
4 8's
4 4's
3 1's
2 9's
FRI  AM - Not show because it's the same as THU PM
9 0's
4 8's
4 4's
3 1's
2 9's
FRI PM - Not show because it's the same as FRI AM
9 0's
4 8's
4 4's
3 1's
2 9's
SAT AM  - Not show because it's the same as FRI PM
11 0's
5 8's
4 4's
4 1's
2 9's
SAT PM  - Not show because it's the same as SAT AM
2 0's
5 8's
5 4's
4 1's
2 9's

Time to Rest !

Tomorrow is another  Day

In 3N do you want to be able to choose between 2 or more occurrences or 3 or more occurrences? Yes or no please

This can be forgotten is that the problem

Let's work the best recommendation !!
the option that is closest to those results

I thought that identifying the pairs would not be so difficult
pairs that are repeated more than twice in the same week and counted

Identifying the pairs that repeat more than 2 times is the most important thing and counting them

your recommendation when you are more relaxed and rested

have Great rest !! please !
Martin Liss

Please let me know if the picture in this comment of mine (where I said "my results don't agree with yours") shows the correct results.

Then for Triple Top please show me what week 22W2015 (2 or more times) should look like,

Please let me know if the picture in this comment of mine (where I said "my results don't agree with yours") shows the correct results.

ok

Then for Triple Top please show me what week 22W2015 showing 2 or more times should look like,

 MON MON TUE TUE WED WED THU THU FRI FRI SAT SAT 9 5 1 8 7 6 4 3 2 0

Did you give me an idea in the case of TRI have 1 week or 2 week selector
Martin Liss

Did you give me an idea in the case of TRI have 1 week or 2 week selector
I don't know if you are asking me something or telling me something but at this point I'm not willing to add new features.

ok
Martin Liss

I thought that identifying the pairs would not be so difficult
I identifying pairs was not difficult, but understanding the requirements was.

This contains changes to both Pair Top and Triple Top on the BYPLAY worksheet.
29228918b.xlsb