Problema con macro contar coincidencias

[eugeniocol]

Buenas de Nuevo.

Tengo esta macro hecha por mí con grabadora (no doy más de sí) trabaja con esta formula

=SUMA(N(MMULT(N(CONTAR.SI(DESREF(C$5:H$5;FILA(C$5:C$10000)-FILA(C$5););$L1:$Q1)>0);{1;1;1;1;1;1})=4))

Para contar coincidencias de 2, 3, 4, 5,6 números de las familias en de valores de L: CL con ? H
El rango real es mínimo en L: CL es de 150.000 claro cada grupo tarda +- 50 minutos, habría otra macro que fuese mucho más rápida ¿?

Ahora tengo otra sorpresa que acabo de ver y esta fórmula la daba por buena y es que veo que no hace la función que yo pensaba.

Ejemplo:
EN L:R SEGUNDA FILA DEBERIA
SER EN R 1 COINCIDENCIA DE 4
NUMEROS CON FILA 5 DE C:H

Subo Libro.

Salud2....

Foro CONTAR COINCIDENCIAS_2.xlsmFetching info...

[bigpetroman]

amigo @eugeniocol intente ayudar, pero de verdad que no entiendo realmente que es lo que hace, por favor explica mejor exactamente que buscas y coloca los resultados de una par de ejemplo.

en lo que describes, los dos rangos que dices (están en verde) yo veo que coinciden 6 números y no 4 como comentas, entonces  no entiendo

suerte

[eugeniocol]

A ver ahora.

Mira Hoja 2

Formula en R

  Cita

=SUMA(N(MMULT(N(CONTAR.SI(DESREF(A$5:F$5;FILA(A$5:A$10000)-FILA(A$5););$L1:$Q1)>0);{1;1;1;1;1;1})=4))

Expand  

L: R en fila 2 busca contar cuatro coincidencias con C: H encontrando las coincidencias en fila 5 y 8, resultado en R2 =2

L: R en fila 5 busca contar cuatro coincidencias con C: H encontrando las coincidencias en fila 7, pero……. resultado en R2 =0 ¿???

Sé que si pongo en })=4)) esto })=5)) pues cuenta 1, pero como busco contar 4 coincidencias ¿??
 

Y la segunda parte es buscar mayor rapidea a esta macro con grabadora.

Y en Hoja 1.

Formulas visibles en R y Z

Plantilla original con la macro por grabadora, que tomo la fórmula en color amarillo como referencia para correrla a mano y después copio pego a valores para que el libro sea menos pesado al abrirlo de nuevo.

Son 150.000 mínimo filas x 10 bloques.

Y como ya comente al ejecutar la macro de grabadora por bloque 50 minutos y a mano bloque a bloque puf……..

Espero que se entienda ahora.

Subo Libro.

Un Saludo.

 

 

 

 

Foro CONTAR COINCIDENCIAS_2.xlsmFetching info...

[bigpetroman]

amigo @eugeniocol revisa el anexo, yo duplique los bloques hasta 10 filas y lo realizo en 30 segundo mas o menos

cualquier problema subes un archivo con mas datos unas 100 lineas, que den diferentes resultados y así hacer mas pruebas

 

Foro CONTAR COINCIDENCIAS_2.xlsmFetching info...

[eugeniocol]

  En 20/8/2019 at 22:45 , bigpetroman dijo:

amigo @eugeniocol revisa el anexo, yo duplique los bloques hasta 10 filas y lo realizo en 30 segundo mas o menos

cualquier problema subes un archivo con mas datos unas 100 lineas, que den diferentes resultados y así hacer mas pruebas

 

Foro CONTAR COINCIDENCIAS_2.xlsm 27 kB · 4 descargas

Expand  

En el libro este ejemplo 1º parece ok, pero..... lo paso al libro mas real y no va.

Veras que en Hoja1 las columnas a rellenar en Naranja la dejo en blanco , le das al boton donde pone tu nombre y lo veras.

El libro pesa un poco lo tengo que poner en este enlace

https://www.dropbox.com/s/m8ews9s73tn5fgh/CONTAR%20COINCIDENCIA%20PLANTILLA%20ORIGINAL.rar?dl=0

Salud2....

 

[bigpetroman]

Saludos amigo @eugeniocol realmente es complicado todo este proceso son muchos registros a procesar, cualquier solucion va a tardar bastante, realice algunas pruebas y la mejor estimacion es que tarde alrededor de 17 minutos por tabla. tienes que procesar 7 mil filas 150 mil veces cada una (1.050.000.000 de evaluaciones x tabla), no se, suponiendo que el equipo tarje una millonesima de segundo en cada evaluacion, serian 1050 segundo, que serian 17 minutos mas o menos.

tambien intente realizando un filtrado para dejar solo los posibles candidatos (donde el menor numero de la fila a evaluar, iniciara como maximo en el tercer campo del registro evaluado), con esto se pudiera recudir considerablemente los candidatos en cada una de las evaluaciones, en vez de evaluar 150 mil registros, tal vez solo se evaluan 56000 o mas o menos; al final eso también lograria reducir el tiempo total.

bueno, al final no termine nada, solo haciendo pruebas, si tengo tiempo en estos dias lo reviso nuevamente, sino, esperemos a ver si alguien mas tiene una mejor idea, suerte

[eugeniocol]

  En 22/8/2019 at 13:35 , bigpetroman dijo:

Saludos amigo @eugeniocol realmente es complicado todo este proceso son muchos registros a procesar, cualquier solucion va a tardar bastante, realice algunas pruebas y la mejor estimacion es que tarde alrededor de 17 minutos por tabla. tienes que procesar 7 mil filas 150 mil veces cada una (1.050.000.000 de evaluaciones x tabla), no se, suponiendo que el equipo tarje una millonesima de segundo en cada evaluacion, serian 1050 segundo, que serian 17 minutos mas o menos.

tambien intente realizando un filtrado para dejar solo los posibles candidatos (donde el menor numero de la fila a evaluar, iniciara como maximo en el tercer campo del registro evaluado), con esto se pudiera recudir considerablemente los candidatos en cada una de las evaluaciones, en vez de evaluar 150 mil registros, tal vez solo se evaluan 56000 o mas o menos; al final eso también lograria reducir el tiempo total.

bueno, al final no termine nada, solo haciendo pruebas, si tengo tiempo en estos dias lo reviso nuevamente, sino, esperemos a ver si alguien mas tiene una mejor idea, suerte

Expand  

Y una macro para solo el booque L:R hasta fila 700.000 ??? que pueda acoplarse para contar coincidencias 3,4 y 5, pero individualmente no que cuente a la vez 3,4 y 5.

Salud2....

[bigpetroman]

Saludos amigo,  lo mejor que logre hacer está en el archivo anexo, le elimine muchas filas para poder subirlo, simplemente completa los datos de las columna A:F (los 6 mil y algo de datos a comparar) y de la K:P (En esta van los 750 mil registros).

con las pruebas que realice (hasta 10 mil registros en K:P), la mejor espectativa son 107 minutos x los 750 mil registros, con esto te ahorrarias mas del 50% del tiempo, que segun tu indicas, es de mas o menos 250 minutos x los 750 mil registros; puedes hacer pruebas colocando el total de filas a procesar en la celda W2, si colocas 0 procesa todos los registros de una vez

suerte

Contar prueba Final.xlsmFetching info...

[eugeniocol]

Gracias.

Ando un poco liado, pero en cuanto pueda la pruebo.

 

Salud2...