Select similiar range numbers

ADRIANA P
ADRIANA P used Ask the Experts™
on
create the key

fom column G  only the same range in this sample 40 is the range

with the same range digit

example  40 41 42 ect...

as show
d111.PNGpairmacth.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I don't understand your question. Please write it in Spanish and then use Google Translate to translate it into english. Also the workbook you attached is an xlsx fuke which means that it has no code; please attach a workbook that has the code.

No entiendo tu pregunta. Escríbalo en español y luego use Google Translate para traducirlo al inglés. Además, el libro de trabajo que adjuntó es un fuke xlsx, lo que significa que no tiene código; adjunte un libro de trabajo que tenga el código.
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
ok
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Martin

Column G will have a couple per week (13 rows)

if in that week there are more than one pair in the range of the sayings
that is 41 47 then 4147 0 417 is the number that I have to post as a key
===========================================================

La columna G va a tener unos pares por semana (13 rows)

si en esa semana hay mas de una par en el rango de los decimos
es decir  41 47  entonces 4147   0  417 es el numero qtengo q postear como llave
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
"fuke" should have been file/archivo.
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Im looking for the same range

example

23 28

are in the 20 range
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I still need a workbook that has code. In other words an xlsm file.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
And if you have a workbook where we've done something similar before, that would be helpful.
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
the code in this workbook

don't do the requested answer in this question

but make the pairs and tri
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
In your question you attached pairmacth.xlsx. I assume that Sheet1 in that workbook came from an xlsm file and I need that file before I can do anything.

En su pregunta, adjuntó pairmacth.xlsx. Supongo que Sheet1 en ese libro proviene de un archivo xlsm y necesito ese archivo antes de poder hacer qual cualquier cosa.
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Martin
the file that make the pair is upload

but my question don't ask for  it
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
the file that make the pair is upload
Are you talking about pairmacth.xlsx? If so that is one sheet copied from another workbook. I need the workbook it came from.

¿Estás hablando de pairmacth.xlsx? Si es así, es una hoja copiada de otro libro de trabajo. Necesito el libro de trabajo del que vino.
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
here
3n_code_AAA.xlsm

have the code

but  this question is not for that
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Please answer Yes or No. Is pairmatch.xlsx the workbook you want changed? If so then I assume that the data in columns G to N were calculated. In that case they can never be changed by code because you haven't included that code, and in any new rows that are added, those columns will be blank because there is no code. I need the workbook that contained the sheet that you posted because that workbook will contain the code.

Responda Sí o No. ¿Es pairmatch.xlsx el libro de trabajo que desea cambiar? Si es así, supongo que se calcularon los datos en las columnas G a N. En ese caso, nunca se pueden cambiar por código porque no ha incluido ese código, y en las filas nuevas que se agreguen, esas columnas estarán en blanco porque no hay código. Necesito el libro que contenía la hoja que publicaste porque ese libro contendrá el código.
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
3n_code_AAA.xlsm

have the code
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
If I use pairmacth.xlsx that means that in addition to what you are requesting, I need to reproduce the code for columns G, H, I, L and N and I won't do that, at least not now. If you post the workbook that pairmacth.xlsx came from, I won't need to do that! Why can't you do that?

Si uso pairmacth.xlsx, eso significa que además de lo que está solicitando, necesito reproducir el código para las columnas G, H, I, L y N y no lo haré, al menos no ahora. Si publica el libro de trabajo del que proviene pairmacth.xlsx, ¡no necesitaré hacer eso! ¿Por qué no puedes hacer eso?
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
3n_code_AAA.xlsm is the original like you say

but this question is not for that

this question is for create  the keys

 in the pairmacth.xlsx  file

because pairmacth.xlsx is the file needed not 3n_code_AAA.xlsm
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
when i say the key

i mean the capture the same range numbers

range are 00 , 10, 20, 30, 40, 50, 60 , 70, 80 , 90

lets say  47 49  are show in the column G same week  
then an key have to be create
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
4749

479
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I understand all that but why won't you attach the workbook that pairmacth.xlsx came from?????

Entiendo todo eso, pero ¿por qué no adjuntas el libro de trabajo del que vino pairmacth.xlsx ?????
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Martin
3n_code_AAA.xlsm  is the source

i take the data to create pairmacth.xlsx from  3n_code_AAA.xlsm


like you say
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Check everything carefully.
29168056.xlsm
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Martin

Count pair by week is not working  properly
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Please explain what you mean and/or show me a week that's wrong and what that week should look like,

Por favor explique a qué se refiere y/o muéstreme una semana que está mal y cómo debería ser esa semana,
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Martin this way
but still need be fixed
c1123.PNG
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
In your picture in row 14 you show '10   2'. Please explain how there are 2 numbers in the 10 range.
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
well

i understand should be  

26 21 29

because 261  299

then range 20 have 3 not 2
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
10  is 17  19 pairs
because  179

10 is 2 (2 pairs in range 10)
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Okay I understand now.

I also just noticed that 999 doesn't show up in 3n_code_AAA.xlsm either.
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
;)
Please refer to the attached. Although not the prettiest formula out there, this one appears to work. This code would be pasted in L265:
=CONCATENATE(IF(OR(VALUE(G257)>$M$5+9,VALUE(G257)<$M$5),"",G257)&IF(OR(VALUE(G258)>$M$5+9,VALUE(G258)<$M$5),"",G258)&IF(OR(VALUE(G259)>$M$5+9,VALUE(G259)<$M$5),"",G259)&IF(OR(VALUE(G260)>$M$5+9,VALUE(G260)<$M$5),"",G260)&IF(OR(VALUE(G261)>$M$5+9,VALUE(G261)<$M$5),"",G261)&IF(OR(VALUE(G262)>$M$5+9,VALUE(G262)<$M$5),"",G262)&IF(OR(VALUE(G263)>$M$5+9,VALUE(G263)<$M$5),"",G263)&IF(OR(VALUE(G264)>$M$5+9,VALUE(G264)<$M$5),"",G264)&IF(OR(VALUE(G265)>$M$5+9,VALUE(G265)<$M$5),"",G265))

Open in new window

And this code in N265:
=LEFT(L265,2)&MID(L265,4,1)&MID(L265,6,1)&MID(L265,8,1)&MID(L265,10,1)&MID(L265,12,1)

Open in new window

They are based on the figure that appears in M5, which I made as a drop down to eliminate incorrect entries. Let me know if this is what you were looking for!

Paul
pairmacth122819.xlsx
"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Try this. I rewrote the code that counts the ranges. It assumes that
  • An ORD like 299 should give one hit (29) in the '20' range and one hit in the '90' range (99)
  • An ORD like 446 should give two hits (44 and 46) in the '40' range
  • An ORD like 999 should give one hit (99) in the '90' range

999 doesn't show up in 2W2012 because there's only one hit and we only show the hits > 1
29168056a.xlsm
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Thanks

Paul

for the great Job !
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Great Job Expert !
You're welcome! I'm glad it worked out for you!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial