Comparar varias columnas en VBA
Resuelto
Pazzaki
Mensajes publicados
10
Estado
Miembro
-
Pazzaki Mensajes publicados 10 Estado Miembro -
Pazzaki Mensajes publicados 10 Estado Miembro -
Bonjour,
Voici la traduction en espagnol fidèle au texte fourni, sans ajouter ni modifier le sens:
Hola,
aquí está mi problema,
Me gustaría comparar 2 listas de 4 columnas cada una que contengan Nombre, fecha, hora y tipo (llamado bloque).
cada lista está compuesta de los mismos bloques y mi objetivo es comparar estas dos listas para encontrar posibles errores (1 bloque de la lista A no coincide con el bloque de la lista B).
He logrado hacer un programa en VBA ayudándome de diversos foros pero mi programa solo funciona si los bloques están en el mismo orden en las dos listas.
aquí está lo que da mi programa:
El objetivo final es tener una página de Excel donde se vean las dos listas lado a lado con los bloques que serían incorrectos en color opcionalmente.
Si hay preguntas, no duden en hacerlas, sabiendo que mi nivel de programación es muy bajo :/
Esperando que alguien pueda ayudarme :)
aquí está mi problema,
Me gustaría comparar 2 listas de 4 columnas cada una que contengan Nombre, fecha, hora y tipo (llamado bloque).
cada lista está compuesta de los mismos bloques y mi objetivo es comparar estas dos listas para encontrar posibles errores (1 bloque de la lista A no coincide con el bloque de la lista B).
He logrado hacer un programa en VBA ayudándome de diversos foros pero mi programa solo funciona si los bloques están en el mismo orden en las dos listas.
aquí está lo que da mi programa:
El objetivo final es tener una página de Excel donde se vean las dos listas lado a lado con los bloques que serían incorrectos en color opcionalmente.
Si hay preguntas, no duden en hacerlas, sabiendo que mi nivel de programación es muy bajo :/
Esperando que alguien pueda ayudarme :)
Sub Résultat()
Dim DerLig As Long, Ligne As Long 'variables
With Worksheets("Feuil1") 'elegir la hoja
DerLig = .Range("B" & Rows.Count).End(xlUp).Row 'determinar el N° de fila de la última fila no vacía de la columna B en la variable dernLign
For Ligne = 1 To DerLig 'desde la primera fila hasta la última
If Range("A" & Ligne).Value <> " " And Range("F" & Ligne).Value <> " \" _
And Range("B" & Ligne).Value <> " \" And Range("G" & Ligne).Value <> " \" _
And Range("C" & Ligne).Value <> " \" And Range("H" & Ligne).Value <> " \" _
And Range("D" & Ligne).Value <> " \" And Range("I" & Ligne).Value <> " \" _
Then 'si valeur des 2 colones differentes alors dans colone K -> Different
If Range("A" & Ligne).Value = Range("F" & Ligne).Value _
And Range("B" & Ligne).Value = Range("G" & Ligne).Value _
And Range("C" & Ligne).Value = Range("H" & Ligne).Value _
And Range("D" & Ligne).Value = Range("I" & Ligne).Value Then 'si valeurs des 2 colonnes = alors dans k-> OK
Range("K" & Ligne).Value = "OK"
Else
Range("K" & Ligne).Value = "Different"
End If
Else
Range("K" & Ligne).Value = " "
End If
Next Ligne
End With
End Sub
10 respuestas
Hola Pazzaki
¿Por qué en VBA cuando una simple formateación condicional haría el trabajo: Selecciona las 3 columnas ABC - Inicio - Formato condicional - Nueva regla - Utilizar una fórmula .. y escribe la siguiente fórmula: =Y($A1<>"";CONT.SI.CONJUNTO($F$1:$F$5000;$A1;$G$1:$G$5000;$B1;$H$1:$H$5000;$C1)=0) respetando bien los $ en los lugares correctos, antes de elegir el color de relleno. Repite la operación para las columnas FGH con la fórmula =Y($G1<>"";CONT.SI.CONJUNTO($A$1:$A$5000;$G1;$B$1:$B$5000;$H1;$C$1:$C$5000;$H1)=0)
Modifica las referencias si tu tabla se va por debajo o por encima de la fila 5000
Cordialmente
Via
¿Por qué en VBA cuando una simple formateación condicional haría el trabajo: Selecciona las 3 columnas ABC - Inicio - Formato condicional - Nueva regla - Utilizar una fórmula .. y escribe la siguiente fórmula: =Y($A1<>"";CONT.SI.CONJUNTO($F$1:$F$5000;$A1;$G$1:$G$5000;$B1;$H$1:$H$5000;$C1)=0) respetando bien los $ en los lugares correctos, antes de elegir el color de relleno. Repite la operación para las columnas FGH con la fórmula =Y($G1<>"";CONT.SI.CONJUNTO($A$1:$A$5000;$G1;$B$1:$B$5000;$H1;$C$1:$C$5000;$H1)=0)
Modifica las referencias si tu tabla se va por debajo o por encima de la fila 5000
Cordialmente
Via
Re
Me ausenté esta tarde
me gustaría reutilizar el código en otros archivos de Excel
ok con la condición de que la información esté colocada en las mismas columnas; si no, tendrás que adaptar el código de la macro de todos modos
Macro que utiliza el mismo principio que la MFC, por un NB.SI.ENS (Countifs en VBA) se contabilizan las filas con los mismos ítems, si es 0, coloración en rojo y visualización en la última columna de Diferente o OK
La macro fonctionne correctement si los dos grupos de 4 columnas tienen el mismo número de filas
En caso contrario habría que añadir entre las dos partes de código que hacen las verificaciones :
Saludos
Via
--
"La imaginación es más importante que el conocimiento." A. Einstein
Me ausenté esta tarde
me gustaría reutilizar el código en otros archivos de Excel
ok con la condición de que la información esté colocada en las mismas columnas; si no, tendrás que adaptar el código de la macro de todos modos
Macro que utiliza el mismo principio que la MFC, por un NB.SI.ENS (Countifs en VBA) se contabilizan las filas con los mismos ítems, si es 0, coloración en rojo y visualización en la última columna de Diferente o OK
Sub Résultat() Dim DerLig As Long, Ligne As Long 'variables 'escoger la hoja aquí With Worksheets("Feuil1") DerLig = .Range("B" & Rows.Count).End(xlUp).Row 'determinar el N° de fila de la última fila no vacía de la columna B en la variable dernLign For Ligne = 1 To DerLig 'a partir de la primera fila hasta 'a la última ' verif que el bloque en ABCD existe en FGHI If Application.WorksheetFunction.CountIfs(.Range("F1:F" & DerLig), .Range("A" & Ligne), .Range("G1:G" & DerLig), .Range("B" & Ligne), .Range("H1:H" & DerLig), .Range("C" & Ligne), .Range("I1:I" & DerLig), .Range("D" & Ligne)) = 0 Then .Range("E" & Ligne) = "Différent" .Range("A" & Ligne & ":D" & Ligne).Select Selection.Interior.Color = 255 Else .Range("E" & Ligne) = "OK" .Range("A" & Ligne & ":D" & Ligne).Interior.Pattern = xlNone End If ' verif que el bloque en FGHI existe en ABCD If Application.WorksheetFunction.CountIfs(.Range("A1:A" & DerLig), .Range("F" & Ligne), .Range("B1:B" & DerLig), .Range("G" & Ligne), .Range("C1:C" & DerLig), .Range("H" & Ligne), .Range("D1:D" & DerLig), .Range("I" & Ligne)) = 0 Then .Range("J" & Ligne) = "Différent" .Range("F" & Ligne & ":I" & Ligne).Select Selection.Interior.Color = 255 Else .Range("J" & Ligne) = "OK" .Range("F" & Ligne & ":I" & Ligne).Interior.Pattern = xlNone End If Next End With End Sub La macro fonctionne correctement si los dos grupos de 4 columnas tienen el mismo número de filas
En caso contrario habría que añadir entre las dos partes de código que hacen las verificaciones :
Next DerLig = .Range("F" & Rows.Count).End(xlUp).Row 'determinar el N° de fila de la última fila no vacía de la columna F en la variable dernLign For Ligne = 1 To DerLig 'a partir de la primera fila hasta la última Saludos
Via
--
"La imaginación es más importante que el conocimiento." A. Einstein
Hola,
Antes que nada gracias por tomarte el tiempo para ayudarme! El problema es que tengo el mismo inconveniente con vuestro programa (que dicho sea de paso funciona mejor que el mío) que con el mío. Es decir, si los bloques no están en el orden, entonces me indica que son diferentes.
Mi objetivo es saber si los bloques de la lista de columnas A B C D también aparecen en la lista de columnas F G H I. (los que no aparezcan serían entonces incorrectos). perdón si me expresé mal.
Si tienen alguna idea para resolverlo, la agradecería, ¡gracias!
saludos
Paz
Antes que nada gracias por tomarte el tiempo para ayudarme! El problema es que tengo el mismo inconveniente con vuestro programa (que dicho sea de paso funciona mejor que el mío) que con el mío. Es decir, si los bloques no están en el orden, entonces me indica que son diferentes.
Mi objetivo es saber si los bloques de la lista de columnas A B C D también aparecen en la lista de columnas F G H I. (los que no aparezcan serían entonces incorrectos). perdón si me expresé mal.
Si tienen alguna idea para resolverlo, la agradecería, ¡gracias!
saludos
Paz
De hecho, al seguir buscando me di cuenta de que quizá no necesito 4 columnas. Necesito que una sola línea sea un bloque entero.
Entonces que nombre, fecha, hora (utc) se busquen en un único bloque de la lista 1 hacia la lista 2.
Eso cambia bastante las cosas y hay que decir que estoy un poco perdido.
Lo siento, ¿podrías orientarme hacia una solución?
cdt
Paz
Entonces que nombre, fecha, hora (utc) se busquen en un único bloque de la lista 1 hacia la lista 2.
Eso cambia bastante las cosas y hay que decir que estoy un poco perdido.
Lo siento, ¿podrías orientarme hacia una solución?
cdt
Paz
Hola
Mi código compara bien los 4 valores juntos y funciona sin problemas
En tu ejemplo los bloques no están desordenados; son, de hecho, diferentes
por ejemplo línea 2 (las 4 primeras columnas): Mbd2006 24/10/2019 12.52 (utc) que es muy diferente de la línea 5 (las otras 4 columnas Mbd2006 27/10/2019 15.52 (utc)
Cordialmente
Via
Mi código compara bien los 4 valores juntos y funciona sin problemas
En tu ejemplo los bloques no están desordenados; son, de hecho, diferentes
por ejemplo línea 2 (las 4 primeras columnas): Mbd2006 24/10/2019 12.52 (utc) que es muy diferente de la línea 5 (las otras 4 columnas Mbd2006 27/10/2019 15.52 (utc)
Cordialmente
Via
Hola,
De hecho, había definido mal la última columna para escribir "Différent" en la siguiente.
Para cambiar el número de columnas, solo hay una variable a modificar.
Debo ausentarme, les envío las modificaciones y las explicaciones a primera hora de la tarde.
De hecho, había definido mal la última columna para escribir "Différent" en la siguiente.
Para cambiar el número de columnas, solo hay una variable a modificar.
Debo ausentarme, les envío las modificaciones y las explicaciones a primera hora de la tarde.
Hola,
¿podrías dejar una copia de tu cuaderno, eso facilitaría mi trabajo (trabajar con datos reales)?
para ello
¿Existe la posibilidad, sin demasiados problemas, de comparar tus 2 bloques de alturas distintas
sobre todo si tienes un gran número de líneas (¿cuántas?)
adjunto un ejemplo de comparación de 2 listas de 1 columna en 10000 líneas en menos de un segundo y que, por supuesto, sería adecuado modificar para adaptarlo a tu problema
https://mon-partage.fr/f/SPyJRJWN/
En espera
¿podrías dejar una copia de tu cuaderno, eso facilitaría mi trabajo (trabajar con datos reales)?
para ello
Coloca el cuaderno sin datos confidenciales como archivo adjunto en
https://mon-partage.fr
Enlace de descarga
haz clic derecho - copiar la dirección del enlace y pegarla en tu mensaje en curso en ccm
¿Existe la posibilidad, sin demasiados problemas, de comparar tus 2 bloques de alturas distintas
sobre todo si tienes un gran número de líneas (¿cuántas?)
adjunto un ejemplo de comparación de 2 listas de 1 columna en 10000 líneas en menos de un segundo y que, por supuesto, sería adecuado modificar para adaptarlo a tu problema
https://mon-partage.fr/f/SPyJRJWN/
En espera
Aquí está el código modificado para 2 veces 4 columnas a comparar, la 1ª serie separada de la 2ª por una columna vacía.
Option Explicit
Dim i%, n%, dl%, x%, dc%
Sub Compare()
' i = n° fila
'n = n° 1ª serie de columnas
'dl = última fila
'dc = última columna
'x = n + 4 (columnas a comparar)
dl = Range("A65536").End(xlUp).Row
dc = Cells(1, Columns.Count).End(xlToLeft).Column
For n = 1 To 4
For i = 1 To dl
x = n + 5
If Cells(i, n) <> Cells(i, x) Then
Cells(i, n).Font.ColorIndex = 3
Cells(i, x).Font.ColorIndex = 3
Cells(i, dc + 1) = "Différent"
End If
Next i
Next n
End Sub
Efectivamente, tu código funciona realmente. como ya respondí a via55 arriba, mi problema es un poco diferente al final. Se trataría de comparar toda una línea de la lista 1 con las líneas de la lista 2 (en desorden, claro) :/
mensaje de arriba :
Pazzaki - 4 dic. 2019 a las 09:08
Además, al buscar me di cuenta de que quizá no necesitaba 4 columnas. Necesito que una línea sea un bloque entero.
Por lo tanto, que nombre, fecha, hora, (utc) se busquen en un solo bloque de la lista 1 hacia la lista 2.
Eso cambia bastante las cosas y debo decir que estoy un poco perdido.
Lo siento, ¿podrías orientarme hacia una solución?
cordialement, Paz
mensaje de arriba :
Pazzaki - 4 dic. 2019 a las 09:08
Además, al buscar me di cuenta de que quizá no necesitaba 4 columnas. Necesito que una línea sea un bloque entero.
Por lo tanto, que nombre, fecha, hora, (utc) se busquen en un solo bloque de la lista 1 hacia la lista 2.
Eso cambia bastante las cosas y debo decir que estoy un poco perdido.
Lo siento, ¿podrías orientarme hacia una solución?
cordialement, Paz
En principio, si bien entiendo la respuesta, el código funciona para una serie de las primeras X columnas comparadas con una misma cantidad de columnas de una 2.ª serie.
Ahora se trataría de comparar los datos de 2 columnas, a priori nada más fácil, pero indican:
No es necesario adjuntar un archivo, pero sí al menos un ejemplo preciso y explícito.
Ahora se trataría de comparar los datos de 2 columnas, a priori nada más fácil, pero indican:
Se trataría de comparar toda una fila de la lista 1 con las filas de la lista 2 (en desorden, por lo tanto)y ahí, hay una necesidad de precisión.
No es necesario adjuntar un archivo, pero sí al menos un ejemplo preciso y explícito.
Leí tu respuesta: para el caso en que hubieras compactado las 4 primeras columnas en una sola y las 4 restantes también, sería aún más simple, por supuesto, pero ¿es ese el caso?
Aquí, he previsto una columna vacía entre las dos otras, si no es así basta modificar :
"pc+2" por "pc+1" (dos veces) y pc+3 por pc+2
Option Explicit
Dim pl%, pc%, dl%
Sub Compare()
pl = 1 'n° primera fila
pc = 1 'primera columna
dl = Range("A65536").End(xlUp).Row
'aquí asignamos el color negro a los datos de las columnas 1 y 3, luego eliminamos la columna 4
'para el caso en que hayas modificado y quieras volver a ejecutar
Range(Cells(pl, pc), Cells(dl, pc+2)).Select
Selection.Font.ColorIndex = 0
Columns(4).Delete Shift:=xlToLeft
For i = pl To dl
If Cells(i, pc) <> Cells(i, pc + 2) Then
Cells(i, pc).Font.ColorIndex = 3
Cells(i, pc + 2).Font.ColorIndex = 3
Cells(i, pc + 3) = "Différent"
End If
Next i
End Sub
Aquí, he previsto una columna vacía entre las dos otras, si no es así basta modificar :
"pc+2" por "pc+1" (dos veces) y pc+3 por pc+2
Option Explicit
Dim pl%, pc%, dl%
Sub Compare()
pl = 1 'n° primera fila
pc = 1 'primera columna
dl = Range("A65536").End(xlUp).Row
'aquí asignamos el color negro a los datos de las columnas 1 y 3, luego eliminamos la columna 4
'para el caso en que hayas modificado y quieras volver a ejecutar
Range(Cells(pl, pc), Cells(dl, pc+2)).Select
Selection.Font.ColorIndex = 0
Columns(4).Delete Shift:=xlToLeft
For i = pl To dl
If Cells(i, pc) <> Cells(i, pc + 2) Then
Cells(i, pc).Font.ColorIndex = 3
Cells(i, pc + 2).Font.ColorIndex = 3
Cells(i, pc + 3) = "Différent"
End If
Next i
End Sub
La idea sería tener siempre los datos espaciados en 4 columnas (finalmente solo por práctica/presentación) pero que se pueda tomar en cuenta como un único bloque. En verdad el nombre, la fecha, la hora deben funcionar juntos. no sé si soy lo suficientemente claro.
Reexplico mi problema con capturas por si acaso.
Me gustaría, a la escala de unas 500 líneas aproximadamente, saber para cada bloque de la lista de la izquierda si están bien en la lista de la derecha. Entonces, incluso si “Mbd12020 12/11/2019 ...etc está en la línea 19 de la lista de la izquierda, tener un “ok” en la columna E si efectivamente forma parte de la lista derecha (incluso si está en la línea 24, por ejemplo)
aquí en la celda 19E aparecería escrito “ok”
Reexplico mi problema con capturas por si acaso.
Me gustaría, a la escala de unas 500 líneas aproximadamente, saber para cada bloque de la lista de la izquierda si están bien en la lista de la derecha. Entonces, incluso si “Mbd12020 12/11/2019 ...etc está en la línea 19 de la lista de la izquierda, tener un “ok” en la columna E si efectivamente forma parte de la lista derecha (incluso si está en la línea 24, por ejemplo)
aquí en la celda 19E aparecería escrito “ok”
Hola a todos,
Está en pruebas en alrededor de 30 líneas, columna A: no es necesario la columna de prueba J
Debo ausentarme algunas horas, gracias por ser pacientes
pero si tienes varias miles de líneas, habría que abordarlo de otra manera por rapidez
--
Michel
Está en pruebas en alrededor de 30 líneas, columna A: no es necesario la columna de prueba J
Debo ausentarme algunas horas, gracias por ser pacientes
pero si tienes varias miles de líneas, habría que abordarlo de otra manera por rapidez
--
Michel
Hola,
Adjunto la propuesta con una maqueta
https://mon-partage.fr/f/ryuY3BL1/
Si te funciona, puedo mejorarla con procedimientos parametrizados para desahogar el conjunto (similaridad de código entre los 2 bloques). Dices...
Edit10:40H
¡Ups!... :-/
despiste en la macro, limpiar en el módulo de rutinas; escribir
Sub limpiar()
Cells.Interior.Color = xlNone
Range("A1").Select
End Sub
Hola,
Como escribe Via55, si el número de líneas es constante para los dos grupos de tres columnas, debería funcionar:
Por supuesto se puede modificar el número de columnas (valor de "n").
Como escribe Via55, si el número de líneas es constante para los dos grupos de tres columnas, debería funcionar:
Option Explicit
Dim i%, n%, dl%, x%
Sub Compare()
dl = Range("A65536").End(xlUp).Row
For n = 1 To 3
For i = 1 To dl
x = n + 4
If Cells(i, n) <> Cells(i, x) Then
Cells(i, n).Font.ColorIndex = 3
Cells(i, x).Font.ColorIndex = 3
Cells(i, n + 1) = "Différent"
End If
Next i
Next n
End Sub
Por supuesto se puede modificar el número de columnas (valor de "n").
En VBA porque me gustaría reutilizar el código en otros archivos de Excel. Con una regla tendría que volver a hacer la manipulación cada vez, ¿me parece? El objetivo es estar lo más optimizado posible (proyecto +/- profesional).
Gracias por tu respuesta