Excel - Macro para 150.000 a 300.000 filas ????

 
Vista:
sin imagen de perfil
Val: 228
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Macro para 150.000 a 300.000 filas ????

Publicado por Eugenio (99 intervenciones) el 20/06/2019 21:13:54
Hola buenas.

Tengo una formula para contar coincidencias columna O, las coincidencias de J:M con B:G.

J:M puede tener entre 150.000 a 300.000 filas y B:G entre 10.000 a 30.000 filas.

La formula hasta 150.000 filas en J:M la desplazo hasta el final y trada unos 30 minutos en recalcular pero la macro se queda colgada.

Mi intencion es encontrar una macro que sea mas rapida que la formula.

Esta es la macro
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub BuscarCoincidencias()
 
Application.ScreenUpdating = False
Range(Range("N3"), Range("N3").End(xlDown)) = ""
For x1 = 3 To Range("B3").End(xlDown).Row
   For x2 = 3 To Range("J3").End(xlDown).Row
      c = 0
      For y = 10 To 12
         Set n = Range("B" & x1 & ":G" & x1).Find(Cells(x2, y), , , xlWhole)
         If Not n Is Nothing Then c = c + 1
      Next
      If c = 3 Then Range("N" & x2) = Range("N" & x2) + 1
   Next
Next
End Sub

Y esta es la formula

1
=SUMA(N(MMULT(N(CONTAR.SI(DESREF(B$3:G$3;FILA(B$3:B$10000)-FILA(B$3););J5:M5)>0);{1;1;1;1})=4))

Bueno en el libro se pueden ver



Un Saludo.
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
Imágen de perfil de Antoni Masana
Val: 4.151
Plata
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Macro para 150.000 a 300.000 filas ????

Publicado por Antoni Masana (1459 intervenciones) el 21/06/2019 08:20:22
La macro no se cuelga, simplemente el ordenador SOLO ejecuta la macro durante 30 minutos y por eso la sensación de cuelgue.

Para evitarlo solo hay que añadir esta línea que remarco:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub BuscarCoincidencias()
    Application.ScreenUpdating = False
    Range(Range("N3"), Range("N3").End(xlDown)) = ""
 
    For x1 = 3 To Range("B3").End(xlDown).Row
        For x2 = 3 To Range("J3").End(xlDown).Row
            c = 0
            For y = 10 To 12
                DoEvents
                Set n = Range("B" & x1 & ":G" & x1).Find(Cells(x2, y), , , xlWhole)
                If Not n Is Nothing Then c = c + 1
            Next
            If c = 3 Then Range("N" & x2) = Range("N" & x2) + 1
        Next
    Next
End Sub

Creo recordar que ya estuvimos hablando de esta tabla y no nos acabó de quedar claro que es lo que quieres hacer, con lo que es dificil ayudarte a optimizar la macro sin saber que se desea conseguir.

Saludos.
\\//_
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
sin imagen de perfil
Val: 228
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Macro para 150.000 a 300.000 filas ????

Publicado por Eugenio (99 intervenciones) el 21/06/2019 15:06:24
por partes

Me dieron esta macro inicialmente para contar coincidencias de 3 numeros, que daria el resultado que da la formula en O
Cuenta las coincidencias de J:M con B:G
Esta es la macro incial para 3 numeros

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub BuscarCoincidencias()
Application.ScreenUpdating = False
Range(Range("M3"), Range("M3").End(xlDown)) = ""
For x1 = 3 To Range("B3").End(xlDown).Row
   For x2 = 3 To Range("J3").End(xlDown).Row
      c = 0
      For y = 10 To 12
         Set n = Range("B" & x1 & ":G" & x1).Find(Cells(x2, y), , , xlWhole)
         If Not n Is Nothing Then c = c + 1
      Next
      If c = 3 Then Range("M" & x2) = Range("M" & x2) + 1
   Next
Next
End Sub

Despues yo la intente adaptar pa contar coincidencias de 4 numeros, que es la que esta en el hilo

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub BuscarCoincidencias()
 
Application.ScreenUpdating = False
Range(Range("N3"), Range("N3").End(xlDown)) = ""
For x1 = 3 To Range("B3").End(xlDown).Row
   For x2 = 3 To Range("J3").End(xlDown).Row
      c = 0
      For y = 10 To 12
         Set n = Range("B" & x1 & ":G" & x1).Find(Cells(x2, y), , , xlWhole)
         If Not n Is Nothing Then c = c + 1
      Next
      If c = 3 Then Range("N" & x2) = Range("N" & x2) + 1
   Next
Next
End Sub

Que por cierto ahora veo que no da el resultado correcto.

Por lo tanto la pregunta ahora centrada se queda asi:

Una macro que de el resultado como la formula en columna O, que es contar las coincidencias J:M con B:G

Salud2...
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Antoni Masana
Val: 4.151
Plata
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Macro para 150.000 a 300.000 filas ????

Publicado por Antoni Masana (1459 intervenciones) el 24/06/2019 09:05:40
Haber si esta macro da mejor resultado.

En la columna P indica en que filas ha encontrado las coincidencias, Esto es útill en forma de depuración

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
Sub BuscarCoincidencias()
    Dim Fil_Orig As Long, Col_Orig As Long, Total As Long, _
        Fil_Dest As Long, Col_Dest As Long
 
    Range("N3:N" & 2 ^ 20).Select
    Selection.ClearContents
    Range("P3:P" & 2 ^ 20).Select
    Selection.ClearContents
    Range("N2").Select
 
    Fil_Orig = 3
    While Cells(Fil_Orig, "B") <> ""
 
        Fil_Dest = 3
        While Cells(Fil_Dest, "J") <> ""
 
            Total = 0
            For Col_Orig = 2 To 7
                For Col_Dest = 10 To 13
                    DoEvents
                    If Cells(Fil_Orig, Col_Orig) = Cells(Fil_Dest, Col_Dest) Then
                       Total = Total + 1
                    End If
                Next
            Next
            If Total = 4 Then
                Cells(Fil_Dest, "N") = Cells(Fil_Dest, "N") + 1
                Cells(Fil_Dest, "P") = Cells(Fil_Dest, "P") & Fil_Orig & " - "
            End If
 
            Fil_Dest = Fil_Dest + 1
        Wend
 
        Fil_Orig = Fil_Orig + 1
    Wend
    MsgBox "Fin"
End Sub

Saludos.
\\//_
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Antoni Masana
Val: 4.151
Plata
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Macro para 150.000 a 300.000 filas ????

Publicado por Antoni Masana (1459 intervenciones) el 24/06/2019 11:43:37
Con la macro que te he enviado soluciona el problema en dar la respuesta correcta.
El problema que hay ahora es que tarda muchísimo porque con 300.000 líneas en una tabla, 150.000 en la otra 6 y 4 columnas en las tablas la macro tiene que hacer la friolera de 900.000.000.000 comparaciones.

En el ejemplo que has enviado se tienen que hacer 9680 comparaciones que teniendo en cuenta que los ordenadores son muy rápidos tarda poco pero en realidad con unas pocas se consigue el mismo resultado.

Primero se hace una conversión de la primera tabla a esto y se ordena:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
01 02 03 04 05 18
01 02 03 04 05 46
01 02 03 04 08 34
01 02 03 04 25 34
01 02 03 04 25 43 
01 03 04 05 25 46
02 05 06 08 09 10
02 05 06 08 09 10
02 05 06 08 09 10
02 05 06 08 09 10
02 05 06 08 09 10
02 05 06 09 09 11
02 05 06 09 10 12
02 05 06 09 11 13
02 05 06 09 12 14
03 05 06 10 13 15
04 05 06 11 14 16
05 05 06 12 15 17
05 06 06 13 16 18
05 06 07 14 17 19

Y con la segunda tabla a esto y se ordena:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
01 02 03 04
01 02 03 05
01 02 03 06
01 02 03 07
01 02 03 08
01 02 03 09
01 02 03 10
01 02 03 11
01 02 03 12
01 02 03 13
01 02 03 14
01 02 03 15
01 02 03 16
01 02 03 17
01 02 03 18
01 02 03 19
01 02 03 20
01 02 03 21
01 02 03 22
01 02 03 23

Si observamos detalladamente las dos tablas, la primera tabla solo hace falta comparar con las 5 primeras filas de la primera tabla, con lo que el número de comparaciones es menor.

El razonamiento puede parecer un pelín complejo pero se puede hacer ahora la cuestión es si la macro la vas a usar una sola vez no vale la pena hacer otra porque quizás tarde menos en ejecutarse la macro que en hacer y probar otra macro.

Si debes ejecutarla más veces quizás se interesante hacer una más rápida que probablemente tarde menos pero no se cuanto menos.

Si te interesa hablamos.

Saludos.
\\//_
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar