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

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

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.
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.908
Oro
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

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

Publicado por Antoni Masana (2464 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:
1
2
3
4
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

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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 claraNo me gusta: Está respuesta no esta clara o no es útil
2
Comentar
sin imagen de perfil
Val: 179
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

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
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
1
Comentar
sin imagen de perfil
Val: 179
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

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.
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.908
Oro
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

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

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

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
1
Comentar
sin imagen de perfil
Val: 179
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

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:


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