Link to home
Start Free TrialLog in
Avatar of ADRIANA P
ADRIANA PFlag for United States of America

asked on

4N Count by week

4N wDD wGTWK
29217947c.xlsb

1- count Range by DAY INcrement by WEEk

  a-Begings in MON AM then add MON PM then add TUE AM then ADD TUE PM
then ADD WED AM then ADD WED PM until finish the week  SAT PM

as shows User generated image
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

In what order are the range counts shown; ascending or descending?
How many hits in a range are necessary for the range to be shown; more than 0 or more than 1?

En qué orden se muestran los recuentos de rango; ascendente o descendente?
Cuántos aciertos en un rango son necesarios para que se muestre el rango; más de 0 o más de 1?
Avatar of ADRIANA P

ASKER

ascending or descending?

always  descending
 
more than 0 or more than 1?

 
more than 0


I have the range counts working. Now I need to apply the colors. It may take me a day or two to do that because I have personal things to do.

BTW, I could argue that the ranges in your picture are in ascending order from the top since the ones with the highest counts are at the bottom.

Tengo los recuentos de rango funcionando. Ahora necesito aplicar los colores. Puede que me lleve uno o dos días hacerlo porque tengo cosas personales que hacer.

Por cierto, podría argumentar que los rangos en su imagen están en orden ascendente desde la parte superior, ya que los que tienen los conteos más altos están en la parte inferior.
I actually have some time now so please explain why in column 'V' the 10 range is green and the 40 range is red.
You are rigth range is ascendent

actually as the button in sheet1 range

color as in Week TOP  Num

green same week

ligth red in the next week
I understand that green indicates that there's a match this week, but what cell does the 10 match?

Entiendo que el verde indica que hay una coincidencia esta semana, pero ¿qué celda coincide con el 10?
In this picture the ranges in a week are only compared with the ranges in the next week. I have not yet done the comparison in the current week. Please tell me if this is correct so far. If you find one cell that isn't colored correctly then stop looking and tell me the cell address of the incorrect cell and why it isn't correct.

En esta imagen, los rangos en una semana solo se comparan con los rangos en la próxima semana. Todavía no he hecho la comparación en la semana actual. Por favor, dígame si esto es correcto hasta ahora. Si encuentra una celda que no tiene el color correcto, deje de buscar y dígame la dirección de la celda incorrecta y por qué no es correcta.
User generated image
v5 v6 are not correct
because no pair with 81  or 48  in the next week
In your original question you said
count Range

Aren't we comparing ranges and not pairs? There's a 10 range in C27 and an 80 range in C23, If you really want to check pairs then there's a problem because I don't think I know how to tell that the 10 range was added because of 81.

En tu pregunta original dijiste
rango de recuento

¿No estamos comparando rangos y no pares? Hay un rango de 10 en C27 y un rango de 80 en C23. Si realmente desea verificar pares, entonces hay un problema porque no creo que sepa cómo decir que el rango de 10 se agregó debido a 81.
Also in your picture you show T5 as red. The 10 range in T5 came from C10 and there's no 13 in 2W2012 so why is it red?

También en su imagen muestra T5 en rojo. El rango 10 en T5 provino de C10 y no hay 13 en 2W2012, entonces, ¿por qué es rojo?
Don't see it as an 10 because it does't
no te entiendo
sorry why i have to repeat again ?
Because I don't understand what "Don't see it as an 10 because it does't" means.
you can CALL this the COLOR for HIts for RANGE 
10 = 1  same for all others
Yes, I understand that but then you say "pair with 81  or 48". If 10 = 1 why are you referring to pairs???????
Create RANGE is one step ( youdid Excellent)

then color RANGE  will be other Step (different)

looks to be the same but is not
Frequently I have difficulty understanding what you are saying and I believe that that is because you don't use Google Translate. I've attached a video which shows how to use it. What I need you to do is to start at the beginning and enter a full and complete description of all of the requirements in Spanish and then paste the English translation here.

Con frecuencia tengo dificultades para entender lo que estás diciendo y creo que se debe a que no utilizas Google Translate. Adjunto un video que muestra cómo usarlo. Lo que necesito que haga es comenzar por el principio e ingresar una descripción completa y completa de todos los requisitos en español y luego pegar la traducción al inglés aquí.
2021-06-13_11-36-49.mp4
Step 2 needs a lot more explanation.
I have to insist that you use Google Translate.
the only difference is a we need to look at 10 = 1

20 = 2

and so for all the other numbers that form the RANGE

It is the only difference, from that everything is similar to WEEK TOP NUM
I'm sorry Adriana, but I don't know how to do what you want because I have no way of knowing what pair values caused the ranges to appear.
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Great Job

!Best Expert !
another way to see this is like this:

1- in the range column the value on the left is taken only

2- Taking only the value on the left, the same thing that was done in WEEK TOP NUM is done to make the match with ORD

the pairs or tri  or  4 digits   in sequence are identified  in ORD (as Done in WEEK TOP NuM)

User generated image
Okay but how does that result in 40 and 60 being red?
1- as you are only taking into account the values of the Left

they would be like this

1
8
4
6

we are looking for the sequence that are present in ORD in the same week or the following week in this example

sequence 64

we always look at the RANGE from the bottom up

6 4 is a sequence 48 is another sequence 81 is another sequence


but in this example in the second week we see a sequence of 46 in the ORD column for the second week and it is red because it is the second week


User generated image
So are you saying that I should
First  look for 1 and 8 and not find a match
Then look for 8 and 4 and not find a match (actually 4 and 8 since the ORDs are in ORD sequence)
Then look for 4 and 6 and find a match?
yes !!



Please remind me of something. If I'm coloring the ranges in column Z (THU PM) of 1W2012 based on what's in 2W2012, do I compare the 1st characters of the ranges with the ORDs starting with week 2 (MON AM) or starting with week 2 FRI AM?

Por favor, recuérdame algo. Si estoy coloreando los rangos en la columna Z (THU PM) de 1W2012 según lo que hay en 2W2012, ¿comparo los primeros caracteres de los rangos con los ORD que comienzan con la semana 2 (MON AM) o comienzan con la semana 2 FRI AM?
This assumes that I always compare the ranges with all the ORDs of the next week. If there is a cell that's wrong please tell me the cell address and why it is wrong.

Esto supone que siempre comparo los rangos con todos los ORD de la próxima semana. Si hay una celda incorrecta, por favor dígame la dirección de la celda y por qué es incorrecta.
User generated image
as shows
User generated image
Okay I thought we were looking for pairs like "23", but now it looks like we are looking for "2" and "3". Correct?
Better?
User generated image
Okay I thought we were looking for pairs like "23", but now it looks like we are looking for "2" and "3". Correct? 

not.

23 is an Pair in 2883    28 83  23  88

is not 2 and 3 as an Digit  alone


we look for pairs,  tri  or qua
Better? 

yes is better!!
but missing somes
In TUE PM for week 1 there are an even number of ranges, in this case 4.
10
80
40
60

If I understand the requirements I need to search the ORDs for 81, 48 and 46. Those are the numbers that represent the ordered version of the first characters of 10 and 80, 80 and 40, and 40 and 60.

But what do I do when there are an odd number of ranges like in MON PM of week 1 where these the ranges?
10
60
40

In this case I believe I should search for 61 and 46, but there's nothing after the 40 so do I search for just 4?

En TUE PM para la semana 1 hay un número par de rangos, en este caso 4.
10
80
40
60

Si entiendo los requisitos, necesito buscar en los ORD 81, 48 y 46. Esos son los números que representan la versión ordenada de los primeros caracteres de 10 y 80, 80 y 40, y 40 y 60.

Pero, ¿qué hago cuando hay un número impar de rangos como en el lunes por la tarde de la semana 1 donde estos son los rangos?
10
60
40

En este caso, creo que debería buscar 61 y 46, pero no hay nada después de 40, así que ¿busco solo 4?
In this case I believe I should search for 61 and 46, but there's nothing after the 40 so do I search for just 4? 
No

we just look for pairs TRi or Qua


but you give an great idea with this situation


a question

Could you show the numbers in red of those fields that have not been left

for example MON PM

10
60
40

these are the ranks that came out

but the ones that did not come out are in RED

00
90
80
70
50
30
20

these would go like this

00
90
80
70
50
30
20
10
60
40


and so we can see more where the blows come from
No

we just look for pairs TRi or Qua
what does "pairs Tri or Qua" mean?

Could you show the numbers in red of those fields that have not been left
No, not now. Let me first try and do what you originally asked for.
Is what I said about TUE PM correct?
You need to explain to me how the 40 in T7 becomes red.

¿Es correcto lo que dije sobre TUE PM?
Necesitas explicarme cómo el 40 en T7 se vuelve rojo
what does "pairs Tri or Qua" mean? 

pairs = 2 sequences  up or down

Tri = 3 sequences up or down

qua = 4 sequences up or down


in the ranges the left side is the way   for coloring the match


you can't use 40 or any range number alone

1
6
4    =  61  , 46   in ord  nothing more    


1
6
4   = 461  TRI  ( 3 digits in sequence)


so 40  with 60 with 10 is  TRI

461


QUAD are 4 digits sequence 







Necesitas explicarme cómo el 40 en T7 se vuelve rojo
You need to explain to me how the 40 in T7 becomes red. 

is red because  is in the next week  and is making 46 pair in ORD num 4617  

as shows:User generated image



you can't use 40 or any range number alone

1
6
4    =  61  , 46  PAIRS  in ord  nothing more    


1
6
4   = 461  TRI  ( 3 digits in sequence)


so 40  with 60 with 10 is  TRI

461


QUAD are 4 digits sequence  
Oh, I never understood before what "up and down" meant in this question.

As for TRI and QUAD, can you show me an example where a cell would get colored only because there was a TRI or QUAD match?

Oh, nunca antes había entendido lo que significaba "up and down" en esta pregunta.

En cuanto a TRI y QUAD, ¿puede mostrarme un ejemplo en el que una celda se colorearía solo porque había una coincidencia de TRI o QUAD?
this is an TRI

you have RANGE 

1
6
4


then ORD have  461 in number  4617

User generated image
Are you saying that to color the cells of column V I need to look in the ORDs for these? Did I miss any?
1486
148
468
81
48
46
Yes Sir !!

theses too can be 
41
61
86

861  
461


You never before indicated that we needed to also search for non-contiguous combinations.

Nunca antes indicaste que necesitábamos buscar también combinaciones no contiguas.
well
you can always go deeper

the idea behind all this is to see practical results

We are working on that
we try to identify sequences in RANGE

that appear in ORD


in range we expect an order either down or up


but in ORD it is more flexible in ORD we hope that it appears in any order of the number in ORD


in RANGE it is a sequence up or down

that is in any position in ORD
Sorry but I don't understand that at all.
1- you have to look in Range first (left side only)
The sequence in RANGE can be UP or Down

2- the you compare it (the range sequence) to the ORD num
(ORD num can have the sequence in any order)
Week 4W2012 column AD winds up with all ten possible ranges. This will take a lot of work but please tell me all the 1st character combinations that I need to look for in 5W2012 and then tell me things like "AD44 is colored red because xx matches yy in Cnnnn" for each of the 10 ranges.

La columna AD de la semana 4W2012 termina con los diez rangos posibles. Esto requerirá mucho trabajo, pero por favor dígame todas las combinaciones de 1er carácter que necesito buscar en 5W2012 y luego dígame cosas como "AD44 es de color rojo porque xx coincide con yy en Cnnnn" para cada uno de los 10 rangos.
 User generated image
in column AD for next week i see

--------------------------------------
A-
----------------------------------------------
00
40
60   =TRI =  460 in ORD (4609 )  is  ligth red because is in he next week
---------------------------------------
B-
-------------------------------------
00
40 =PAIR  UP =  40  in ORD in 4039   is  ligth red because is in he next week
-----------------------------------------
C-
--------------------------------------

40
60 = PAIR DOWN  = 46  in ORD 4679  is  ligth red because is in he next week

-----------------------------------------------------------------------------------------------------------

the first thing we see that appears is a TRI
then you only see 2 pairs of pairs

that by the way they already appear in the TRi

But I'm showing you how to watch the hits


Those are the sequences that are in RANGE and are identified in ORD

1-RANGE has the sequences

2- then those sequences are searched in ORD
so we see a hit per play

What we are seeing is from which area the numbers listed in RANGE that appear in ORD are coming out

I don't believe you did as I asked.
please tell me all the 1st character combinations that I need to look for in 5W2012 and then tell me things like "AD44 is colored red because xx matches yy in Cnnnn" for each of the 10 ranges.
please tell me all the 1st character combinations that I need to look for in 5W2012 and then tell me things like "AD44 is colored red because xx matches yy in Cnnnn" for each of the 10 ranges.

sorry i get lost. provide an sample

but the first place to look in  is in column AD  i see many sequences
AD40 have an sequence with 046 ( this case donwload)

then i look ORD to identify the sequences that belong from RANGE

example the first  sequence (not ONLY digit I match is 0 4 6  because it appear in ORD

so my first place to see as RANGE sequences in this case SAT PM

then i move to ORD to see what ORD have to in that sequence
you can begins  to see the sequences from  RANGE from AD44 down then compare to ORD by each number

 if I understood the question of where to start
As i did here in 4W2012 I need to know every 1st character range combination in column AD that I need to look for in 5W2012.

I also need to know which ranges in column AD would become red, and if so the 1st character range combination and ORD that caused it.

Como hice aquí en 4W2012, necesito saber cada combinación de rango de 1er carácter en la columna AD que necesito buscar en 5W2012.

También necesito saber qué rangos en la columna AD se volverían rojos y, de ser así, la combinación del primer rango de caracteres y el ORD que lo causó.
if  I am understanding well

start from top to bottom in the column you are going to work on

in this case the column AD SAT PM  RANGE

9
3
5
7
0
4
6
8
2
1

top to bottom checking sequences


93  appears 2 times in num  4039   and 2039  in the next week35

      then next sequence

35 not sequence in ord

     then next sequence is 57

57   = it appears in  4579 in ORD

    then next sequence

70  not sequence in ord

     then

04 = it appears in 4609 and  4039  and  in ORD

     then

46 = it shows in 4679 and 4609  in ORD notice 2 pairs in sequence means an TRI as this is an TRI Number

     then

68  =  not sequence in ord  for this sequence


82 =  not sequence in ord  for this sequence

21 = not sequence in ord  for this sequence


1-Moving down the RANGES Column (left value) we see the pairs that come out in ORD if these are cotinuas they have a TRI or a QUA







Why didn't you look for 07 or 39?
Why didn't you look for 07 or 39? 

i say 93 because that the order from up to down but is 39 in  ORD  but i mention it
(just try  to shows how i take the pairs )  but you rigth should be as ORD


"93  appears 2 times in num  4039   and 2039  in the next week35"

      then next sequence


but will be an best way see the  RANGE from up to down like ORD so will be more easy to match


07 is not in RANGE Sequence


07 is not in sequence from top to bottom

I look at the sequences in order from top to bottom

In 4W2012, column T winds up with just two ranges: 90 and 00. When I look for pairs forward in 5W2012, 90 gets colored because 09 is in cell C45 and 90 is the first range from the top. When I go backward I still look for 90 and still get a match in C45, but in this case do I color '00' because it's the first range from the bottom?

En 4W2012, la columna T termina con solo dos rangos: 90 y 00. Cuando busco pares hacia adelante en 5W2012, 90 se colorea porque 09 está en la celda C45 y 90 es el primer rango desde la parte superior. Cuando retrocedo, sigo buscando 90 y aún obtengo una coincidencia en C45, pero en este caso, ¿coloreo '00' porque es el primer rango desde abajo?
i  see 

when we talk about T44 and  T45  we have 90 in range (09 to ORD and 00 to ORD)
we have

90
00

we only have here (the left side to work with)

9
0     so we look for 09 in any ORD number as an PAIR (pair is the basic unit in tihis situation)

90
00  is not an pair for us in this situation  i means (90 or 00  alone are not useful for us)


This is so frustrating. When you said "up and down" I assumed that in the case of column T in 1W2012 that I should look first down from the top of the ranges so I'd
look for the ordered arrangement of 1 and 6 (61) and get a match with C27. I would then color 10.
look for the ordered arrangement of 6 and 4 (46) and get a match with C27.  I would then color 60.
That's as far as I can look down so I assumed I would have to look up from the bottom of the ranges and
look for the ordered arrangement of 4 and 6 (46) and get a match with C27.  I would then color 40.

If the underlined step isn't correct then how can 40 get colored?????
1- do not get frustrated

2-"When you said "up and down" I assumed that in the case of column T in 1W2012 that I should look first down from the top of the ranges so I'd "

ok always do .just move down only  that's

remember

when we have an range
you have

example column T
( you see this )


10
60
40

(but works like this)
this equals to

1
6
4

you then look for the pair 16 for that week (and is present)

you then you goes to the next  to see  64  (and is present)

as you find the 2 near each other that is an TRI

so you have the 2 pais with colors making an TRI


you're doing super good keep looking down

and the couples that arise from below are fine

do not look up because they are already identified


(I might try to explain up and down to you
when we were talking about a range in the middle of the column
but with the pattern you are doing from top to bottom you can create all the necessary pairs)

you already have it !

 





 





3-

One more question about coloring the cells. In my code when I find a match I always color just the first range. For example when I find a match for 61 I color the 10 because that's the first range of the pair, and when I find a match for 46 I color the 60 because that's the first range of the pair. Is that the correct thing to do?
yes!! 
I'm sorry if we've  talked about this before but assume there there 4 ranges in a column:
10
20
30
40

When I look for pairs I currently look for
the ordered value of 12 = 21
the ordered value of 23 = 23
the ordered value of 34 = 43

Did I miss any?

When I look for triples I currently look for
the ordered value of 123 = 213
the ordered value of 234 = 423

Did I miss any?
When I look for pairs I currently look for
the ordered value of 12 = 21
the ordered value of 23 = 23
the ordered value of 34 = 43

Did I miss any? 
Not

When I look for triples I currently look for
the ordered value of 123 = 213
the ordered value of 234 = 423

Did I miss any?

Not

Then why is V8 colored in your picture?
please give more information

as possible Please
In this picture you show V8 (60) colored and I don't see any pair or tri match for that cell.
here best expert!

 
It is repeated because the couple that comes out is 46

but  It was already marked in column T7

User generated image





In some cases I'm coloring the wrong ranges and I'm having trouble figuring out a foolproof way of doing it. Here are a couple of examples in 1W2012 column T.
Currently when I find a match for a pair I always color the first range in the pair. So for example when I find a match for 61 I color the 10 because it's the first range in the pair. Please let me know if you think that that is the way to do it.  However when I find a match for the 461 tri I want to color the 40 but that's not the first range. What rule would tell me to color 40?

En algunos casos, estoy coloreando los rangos incorrectos y tengo problemas para encontrar una forma infalible de hacerlo. Aquí hay un par de ejemplos en la columna T del 1W2012.
Actualmente, cuando encuentro una coincidencia para un par, siempre coloreo el primer rango del par. Entonces, por ejemplo, cuando encuentro una coincidencia para 61, coloreo el 10 porque es el primer rango del par. Por favor, avíseme si cree que esa es la forma de hacerlo. Sin embargo, cuando encuentro una coincidencia para el 461 tri, quiero colorear el 40, pero ese no es el primer rango. ¿Qué regla me diría que coloreara 40?
go to the Pairs and leave the TRi aside
I would tell you Martin to observe this rule
I explain to you

1-when there is a TRI there are two consecutive pairs
therefore it is a tri marked by two consecutive pairs

2-when there is a there are three consecutive pairs
therefore it is a QUA marked by three consecutive pairs

When I say consecutive I mean Sequence which is what we are identifying


If this is so, you should not look for TRI or Qua

because they are going to be consecutive pairs

Maybe something that we should try to identify is when a PAIR or a Couple is repeated on more than one occasion because that is another indicator that it is there in that area taking the ORDs
(I mean where is the next number going to be from)

Do you think this would work? In column T there are three ranges and I currently look for just two pairs: 10 and 60 (61) and 60 and 40 (46). 10 gets colored from the the 61 and 60 get colored from 46. When there are an odd number of ranges I could do one "up" match starting with the last range which would be 40 and 60 (46) which matches just like it did previously but in this case I would color the last range rather than the first.

¿Crees que esto funcionaría? En la columna T hay tres rangos y actualmente busco solo dos pares: 10 y 60 (61) y 60 y 40 (46). 10 se colorea desde el 61 y 60 se colorea desde el 46. Cuando hay un número impar de rangos, podría hacer una coincidencia "ascendente" comenzando con el último rango, que sería 40 y 60 (46), que coincide exactamente como lo hizo. anteriormente, pero en este caso colorearía el último rango en lugar del primero.
Or simpler. If there's an odd number of ranges and the next to the last one is colored then the last one also gets colored.

O más simple. Si hay un número impar de rangos y el siguiente al último está coloreado, el último también se colorea.
ok

Are you seeing that this is repeated?

in others weeks situations ??





Do you agree with these?
User generated image
Yes !
Great now remind me of something else. When I compare the current week's pairs with the current week's ORDs do I start at the ORD that's after the current pair? In other words for 26W2013 column S do I start looking with ORD 2619?

Genial ahora me recuerdan a otra cosa. Cuando comparo los pares de la semana actual con los ORD de la semana actual, ¿empiezo en el ORD que sigue al par actual? En otras palabras, para la columna S del 26W2013, ¿empiezo a buscar con ORD 2619?
yes
Going back to Week Top Num for a minute, do you need the light green background color like in G5?
do you need the light green background color like in G5?


Yes.
I prefer it because it helps me
in something if I need a help

for example

every time I used the Plays in BYPLAY sheet button

the marks that I do are erased

that is, I manually color some values in WEEK TOP NUM

and they are erased, can you help me not to erase them?
I already have a version that changes what Plays does. In the new version it only copies the data from Sheet1 and does not clear anything.
Thank you !!  ;)

that way I can manually color what I need and it will not be erased
the same can be with BYYEARS sheet ?
In a new question I can probably do anything you want.
;)

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Great Job !!
Best Expert !
Best Expert !

can you  fixed the black fill in the  Range

i can't see the numbres 
Black fill?
as shows

User generated image
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
:)