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

Pos: 16

Val: 158

Macro para 150.000 a 300.000 filas ????

Publicado por Eugenio (105 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

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

=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.

CONTAR-COINCIDENCIAS-FILAS-FORO-2.rar(15,6 KB)

Valora esta pregunta

Me gusta: Está pregunta es útil y esta clara

No me gusta: Está pregunta no esta clara o no es útil

Responder

Pos: 2

Val: 4.908

Macro para 150.000 a 300.000 filas ????

Publicado por Antoni Masana (2477 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:

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 clara

No me gusta: Está respuesta no esta clara o no es útil

Comentar

Pos: 16

Val: 158

Macro para 150.000 a 300.000 filas ????

Publicado por Eugenio (105 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

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

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

Comentar

Pos: 2

Val: 4.908

Macro para 150.000 a 300.000 filas ????

Publicado por Antoni Masana (2477 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

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.
\\//_

CONTAR-COINCIDENCIAS-FILAS-FORO-2_V2.zip(18,9 KB)

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

Macro para 150.000 a 300.000 filas ????

Publicado por Antoni Masana (2477 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:

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:

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

Comentar

Hice un macro en vba ya funciona pero me manda un error

Actualizar vinculos con macro