Excel - Macro "Comparar listas de 2 hojas con condiciones y usando colores de verificacion (Instrucción If..

Pos: 14

Val: 179

Macro "Comparar listas de 2 hojas con condiciones y usando colores de verificacion (Instrucción If..

Publicado por Quico (106 intervenciones) el 27/12/2018 10:35:04

Necesito ayuda para resolver una macro en la que a partir de una lista con valores "DE ORIGEN" importados desde csv (con comas) a una hoja que llamo CSV, pueda comparar nuevas entradas de otros CSV.

Es decir, una macro que compare la informacion de 2 listas a partir de 2 rangos que se llaman 'Issue key' y 'Summary', para poder hallar las diferencias y similitudes.

La macro tendría que reflejar con colores (preferiblemente cambiando el color del texto y no la celda) los resultados en función de las siguientes CONDICIONES:

a) Si el valor del rango 'Issue key' y 'Summary' (que aparece en la nueva hoja) se repite en los valores de origen 'Issue key' y 'Summary' , sigue todo igual y el texto en NEGRO.

b) Si existe un nuevo valor 'Issue key' y 'Summary' en la nueva hoja, se añadirá a la lista de 'origen' en COLOR VERDE.
Y a ser posible, ordenada numéricamente respecto al rango 'Issue key' (que es numérico)

c) Si en una nueva lista con nuevos valores, se repite el valor que estaba antes en VERDE (porque en ese momento era nuevo), ya no será nuevo y se pondrá de color 'NEGRO' como todos los demás.

d) Si algun valor desaparece en las nuevas listas (osea, que no coincide con la lista principal), se marcará en la lista principal en color ROJO, porque ha desaparecido. ¡Han quitado esa referencia, pero necesito saber que ha desparecido!

Creo que el concepto se entiende,aunque no se si se me he liado demasiao. Por si acaso, os dejo unos excel con 'anotaciones más claras' en la base macro (JIRA_gestion.xlsm) y 3 listas de valores (CSV_Origen.csv, CSV_Semana1.csv, CSV_Semana2.csv)

Agradecería que alguien pudiera ayudarme a hacerlo y así poder adaptarlo a futuros rangos, etc....
Muchas gracias de antemano.

Macro_Comparador-Condiciones.zip(68,7 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 "Comparar listas de 2 hojas con condiciones y usando colores de verificacion (Instrucción If..

Publicado por Antoni Masana (2562 intervenciones) el 27/12/2018 13:07:45

Primer problema:

Al importar los ficheros CSV veo que lo hace 'mal' y pongo la palabra entre comillas porque lo hace como debe hacerlo pero supongo que no como se desea que lo haga. Me explico, algunas líneas tienen como primer carácter unas comillas dobles lo que interpreta que es un texto hasta las siguientes comillas dobles y no reconoce la coma después de la palabra Story.

Segundo problema:

Estas operaciones no sirven:

fin = Application.CountA(.Range("A:A"))

final = Application.CountA(Sheets("CSV").Range("B:B"))

col_1 = .Cells(1, Cells.Columns.Count).End(xlToLeft).Column

col_2 = Sheets("CSV").Cells(1, Cells.Columns.Count).End(xlToLeft).Column

fin Devuelve 39 y la última fila es la 43
final Devuelve 65 y la última fila es la 67
col_1 Devuelve 1 y no entiendo que sentido tiene saber el numero de columna de la hoja MACRO si sabemos que son dos A y B
col_2 Devuelve 173 y no entiendo que sentido tiene saber el numero de columna de la hoja CVS si sabemos que tenemos que tratar la B y D.

De hecho es tan liado que he realizado uno nuevo

100

101

102

103

104

105

106

107

108

Sub Compara_Hojas()

    Dim Fila_Mac As Long, Story As String, Cmp_Story As String, Falta As Boolean, _

        Fila_Cvs As Long, Titul As String, Cmp_Titul As String, Texto_Color As Single

    Const COLOR_ROJO = 1

    Const COLOR_VERDE = 2

    Const COLOR_NEGRO = 3

    ' ---&---  Comprueba que los Textos de la hoja "Macro"

    '                             estan en la hoja "CSV"

    '          y si falta los marca en Rojo

    Sheets("Macro").Select

    Fila_Mac = 7

    While Cells(Fila_Mac, "A") <> ""

        Story = Cells(Fila_Mac, "A") & "^" & Cells(Fila_Mac, "B")

        Sheets("CSV").Select: Texto_Color = COLOR_ROJO

        Fila_Cvs = 2

        While Cells(Fila_Cvs, "A") <> ""

            Titul = Cells(Fila_Cvs, "B") & "^" & Cells(Fila_Cvs, "D")

            If Titul = Story Then Texto_Color = COLOR_NEGRO

            Fila_Cvs = Fila_Cvs + 1

        Wend

        Sheets("Macro").Select

        ' ---&--- Colorea la Celda de ROJO o NEGRO

        Range("A" & Fila_Mac & ":B" & Fila_Mac).Select

        With Selection.Font

            If Texto_Color = COLOR_ROJO Then .Color = -16776961

            If Texto_Color = COLOR_NEGRO Then .ColorIndex = xlAutomatic

            .TintAndShade = 0

        End With

        Fila_Mac = Fila_Mac + 1

    Wend

    ' ---&---  Comprueba que los Textos de la hoja "CSV"

    '                            Faltan en la hoja "MACRO"

    '          y los copia en Verde

    Sheets("CSV").Select

    Fila_Csv = 7

    While Cells(Fila_Csv, "A") <> ""

        Cmp_Story = Cells(Fila_Csv, "B")

        Cmp_Titul = Cells(Fila_Csv, "D"): Story = Cmp_Story & "^" & Cmp_Titul

        Sheets("MACRO").Select: Falta = True

        Fila_Mac = 2

        While Cells(Fila_Mac, "A") <> ""

            Titul = Cells(Fila_Mac, "A") & "^" & Cells(Fila_Mac, "B")

            If Titul = Story Then Falta = False

            Fila_Mac = Fila_Mac + 1

        Wend

        If Falta Then

           Cells(Fila_Mac, "A") = Cmp_Story

           Cells(Fila_Mac, "B") = Cmp_Titul

            ' ---&--- Colorea la Celda de Verde

            Range("A" & Fila_Mac & ":B" & Fila_Mac).Select

            With Selection.Font

                .Color = -11489280

                .TintAndShade = 0

            End With

        End If

        Sheets("CSV").Select

        Fila_Csv = Fila_Csv + 1

    Wend

    ' ---&--- Ordena la Hoja Macro

    Sheets("MACRO").Select

    Fila_Mac = 7

    While Cells(Fila_Mac + 1, "A") <> "" ' Busco la ultima fija

        Fila_Mac = Fila_Mac + 1

    Wend

    Range("A6:B" & Fila_Mac).Select

    ActiveWorkbook.Worksheets("Macro").Sort.SortFields.Clear

    ActiveWorkbook.Worksheets("Macro").Sort.SortFields.Add _

                                       Key:=Range("A6"), _

                                       SortOn:=xlSortOnValues, _

                                       Order:=xlAscending, _

                                       DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Macro").Sort

        .SetRange Range("A7:B" & Fila_Mac)

        .Header = xlNo

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

    Range("A7").Select

End Sub

Y creo que hace lo que quieres:

PASO - 1 -- Si los datos de la columnas A y B de la hoja MACRO no existen en la hoja CSV en B y D los marca en rojo y si existe en negro

PASO - 2 -- Si los datos de la hoja CSV de la columnas B y D no existen en la hoja MACRO los añade a esta ultima y los marca de verde.

PASO - 3 -- Ordena la hoja MACROS por la columna A.

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: 14

Val: 179

Macro "Comparar listas de 2 hojas con condiciones y usando colores de verificacion (Instrucción If..

Publicado por Quico (106 intervenciones) el 27/12/2018 15:06:30

¡Que fiera Antoni!
No he podido probarlo aún, pues estoy leyendo su post, pero por lo que me comenta y su nivel de experiencia, seguro que ha dado en el clavo.
Yo he comenzado justo hace 1 semana a hacer cositas con excel. Para mi, lo de las macros me parece brutalmente últuil, pero carezco de conocimientos. Lo que hago es buscar en foros mis ideas, tratar de adaptar código, pero en este caso....me superaba. Lo de las condiciones se me escapan completamete de las manos, y tampoco he podido encontrar en ningún foro lo que exactamente trataba de hacer,
Si comparaciones, pero normalmente entre columnas de una misma (como mucho 2) página y solo la condicion de poenr una celda en rojo.

Trataré de mirar todo lo que me ha indicado, y la verdad es que me encantaría (poco a poco) tener más idea de que cosas se pueden hacer, porque me empiezo a dar cuenta, que para muchas cosas, el límite solo es la imaginación.

Nuevamente, muchas gracias.

Por cierto, pensaba que la importación CSV era correcta, y que se introducían bien en las columnas de la página principal.
¿Que tendría que hacer para el problema de las comillas que me indica???

Valora esta respuesta

Comentar

Pos: 14

Val: 179

Macro "Comparar listas de 2 hojas con condiciones y usando colores de verificacion (Instrucción If..

Publicado por Quico (106 intervenciones) el 27/12/2018 15:47:32

Hola de nuevo, con la emoción de ver ese código en marcha, me he puesto a ver qtal funciona.

Creo, que excepto un par de detalles, ha podido entender perfectamente mis intenciones. No obstante, he encontrado estos errores y he mirado para ver si sabia tocar código dónde tocaba........

1. Con el inserto del primer CSV (CSV_Origen.csv) dentro de la página "Macro", todo esta OK. Aún no ha comenzado la comparación.

2. Al importar el primer CSV comparativo (CSV_Semana1.csv), veo que me muestra "3 entradas verdes" (11583, 11563, 11532) fuera del rango que en teoria comienza en la celda A7 de "Macro" hacia abajo. Sin embargo, veo que estan mismas entradas aparecen en 'negro' en el rango A7:A47. ¿No termino de ver en el código el porque las puso arriba de A7 y porque estan en negro al final del rango.....¡Se me escapa completamente!

3. Al importar un segundo CSV comparativo (y pueden ser bastantes más) (CSV_Semana2.csv) y hacer click sobre el botón "Comparador", continuan arriba de la posición A7 algunos datos.

Por lo demás, creo que todo es correcta segun las condiciones.
ROJO: Si desaparece de la página 'CSV' una entrada que esta en página 'Macro'
NEGRO: cuando se introducen por primera vez o cuando dej ade ser nueva 'Verde' en comparaciones posteriores.
VERDE: Cuando hay nuevas entradas en 'CSV' y no aparecen el 'Macro', que se introducen ordenadas en color VERDE en 'Macro'.

Adjunto los nuevos ficheros, ya que he visto (tenias razón) que algunas entradas CSV tenianunas comillas que no tendrían que estar. Las que quitado. Gracias.

Macro-adaptado-Comparador.zip(67,4 KB)

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

Macro "Comparar listas de 2 hojas con condiciones y usando colores de verificacion (Instrucción If..

Publicado por Antoni Masana (2562 intervenciones) el 28/12/2018 07:33:56

Hay tres fallos que no había visto:

Línea 54
Línea 90
Línea 94

100

101

102

103

104

105

106

107

108

Sub Compara_Hojas()

    Dim Fila_Mac As Long, Story As String, Cmp_Story As String, Falta As Boolean, _

        Fila_Cvs As Long, Titul As String, Cmp_Titul As String, Texto_Color As Single

    Const COLOR_ROJO = 1

    Const COLOR_VERDE = 2

    Const COLOR_NEGRO = 3

    ' ---&---  Comprueba que los Textos de la hoja "Macro"

    '                             estan en la hoja "CSV"

    '          y si falta los marca en Rojo

    Sheets("Macro").Select

    Fila_Mac = 7

    While Cells(Fila_Mac, "A") <> ""

        Story = Cells(Fila_Mac, "A") & "^" & Cells(Fila_Mac, "B")

        Sheets("CSV").Select: Texto_Color = COLOR_ROJO

        Fila_Cvs = 2

        While Cells(Fila_Cvs, "A") <> ""

            Titul = Cells(Fila_Cvs, "B") & "^" & Cells(Fila_Cvs, "D")

            If Titul = Story Then Texto_Color = COLOR_NEGRO

            Fila_Cvs = Fila_Cvs + 1

        Wend

        Sheets("Macro").Select

        ' ---&--- Colorea la Celda de ROJO o NEGRO

        Range("A" & Fila_Mac & ":B" & Fila_Mac).Select

        With Selection.Font

            If Texto_Color = COLOR_ROJO Then .Color = -16776961

            If Texto_Color = COLOR_NEGRO Then .ColorIndex = xlAutomatic

            .TintAndShade = 0

        End With

        Fila_Mac = Fila_Mac + 1

    Wend

    ' ---&---  Comprueba que los Textos de la hoja "CSV"

    '                            Faltan en la hoja "MACRO"

    '          y los copia en Verde

    Sheets("CSV").Select

    Fila_Csv = 7

    While Cells(Fila_Csv, "A") <> ""

        Cmp_Story = Cells(Fila_Csv, "B")

        Cmp_Titul = Cells(Fila_Csv, "D"): Story = Cmp_Story & "^" & Cmp_Titul

        Sheets("MACRO").Select: Falta = True

        Fila_Mac = 7

        While Cells(Fila_Mac, "A") <> ""

            Titul = Cells(Fila_Mac, "A") & "^" & Cells(Fila_Mac, "B")

            If Titul = Story Then Falta = False

            Fila_Mac = Fila_Mac + 1

        Wend

        If Falta Then

           Cells(Fila_Mac, "A") = Cmp_Story

           Cells(Fila_Mac, "B") = Cmp_Titul

            ' ---&--- Colorea la Celda de Verde

            Range("A" & Fila_Mac & ":B" & Fila_Mac).Select

            With Selection.Font

                .Color = -11489280

                .TintAndShade = 0

            End With

        End If

        Sheets("CSV").Select

        Fila_Csv = Fila_Csv + 1

    Wend

    ' ---&--- Ordena la Hoja Macro

    Sheets("MACRO").Select

    Fila_Mac = 7

    While Cells(Fila_Mac + 1, "A") <> "" ' Busco la ultima fija

        Fila_Mac = Fila_Mac + 1

    Wend

    Range("A7:B" & Fila_Mac).Select

    ActiveWorkbook.Worksheets("Macro").Sort.SortFields.Clear

    ActiveWorkbook.Worksheets("Macro").Sort.SortFields.Add _

                                       Key:=Range("A7"), _

                                       SortOn:=xlSortOnValues, _

                                       Order:=xlAscending, _

                                       DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Macro").Sort

        .SetRange Range("A7:B" & Fila_Mac)

        .Header = xlNo

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

    Range("A7").Select

End Sub

Esto soluciona los problemas de la hoja MACRO.

Saludos.
\\//_

Valora esta respuesta

Comentar

Pos: 14

Val: 179

Macro "Comparar listas de 2 hojas con condiciones y usando colores de verificacion (Instrucción If..

Publicado por Quico (106 intervenciones) el 28/12/2018 09:02:20

Muchas gracias Antoni.
Ayer por la noche encontre porque se salia del rango, pero no conseguía averiguar los otros errores.

Muchas gracias.

Una última consulta.
He añadido una macro que me genera 'carpetas' con el nombre de las entradas de la columna A.
Pero hay 2 cosas que no consigo encontrar en ningun foro:

1. Que me pregunte en que lugar quiero generarlas. (Para mi son rutas variables)
Solo encuentro que lo haga en el mismo sitio dónde se guarda el libro, o rutas fijas tipo C:/Mis Documentos/Lo que sea

2. Me genera todas las entradas de la columna A, y me interesaría discriminar las entradas 'rojas', pues al final el rojo me visualiza un error de una entrada que no va a ser operativa y no me interesa generar.

Aunque he adjuntado el fichero 'macro', esta es la programación que uso:

Sub CrearCarpetas()

'Ocultamos el procedimiento

 Application.ScreenUpdating = False

'llamamos al objeto FileSystemObject

 Set fso = CreateObject("Scripting.FileSystemObject")

'Pasamos a una variable, la ruta donde se encuentra el fichero de Excel donde se está ejecutando este código

 ruta = ActiveWorkbook.Path

'Seleccionamos la primera celda que contiene los nombres de las carpetas (en mi caso la A7)

 Range("A7").Select

'Recorremos toda la columna, hasta que encuentre una fila vacía, y por cada fila con texto, crearemos una carpeta con el nombre de ese mismo texto

 Do While Not IsEmpty(ActiveCell)

'Si el fichero no existe, entonces lo creamos

 If Not fso.FolderExists(ruta & "\" & ActiveCell.Value) Then

 fso.CreateFolder (ruta & "\" & ActiveCell.Value)

End If

'Pasamos a la fila siguiente, y volvemos a recorrer el bucle

 ActiveCell.Offset(1, 0).Select

Loop

'Limpiamos el objeto

 Set fso = Nothing

'Mostramos el procedimiento

 Application.ScreenUpdating = True

MsgBox "Se han generado con éxito, carpetas con el nombre de la 'Story'." & Chr(10) & "xxxxxx Texto por decidir segun la ubicacion donde se guarde al final."

End Sub

PD: Puede el texto mostrado con 'MsgBox' mostrarme la ubicación (variable en mi caso) dónde se generaron las carpetas?

Saludos y gracias.
Si no le respondo, es porque marcho unos días de vacaciones.....sin ordenador! :-) gracias de nuevo.

Modulo7.zip(780,0 B)

Valora esta respuesta

Comentar

tengo esta macro que genera combinaciones de números y quería otra que combinara letras también

¿cómo autosumar y restar horas?