Conciliar números
Tengo una tabla formada por dos columnas, la primera contiene números, correspondientes a una cuenta bancaria, la segunda contiene los números pero de la cuenta de la empresa. Tengo que detectar las diferencias entre las dos. Las dos columnas de números al final deben ser iguales. Para buscar las diferencias utilizo funciones, así si el mismo número aparece una sola vez en ambas columnas no hay problema, es correcto, o si aparece en una sí y en otra no, se identifica el número fácil. El problema es si aparece dos o más veces en una y dos o más veces en otra y el nº de veces entre ambas no coincide, esto significa que el número no está las mismas veces repetido en las dos columnas, luego hay un error, el problema es saber que número o números son y las veces que falta en una u otra columna. ¿Se pueden encontrar de manera automática con alguna rutina de vba?
3 Respuestas
Respuesta de Elsa Matilde
1
Muy bien no supe qué hacer con la información. A ver si te sirve lo siguiente:
Si en la col A están las cuentas de Bancos, y en la col B las de empresas, la macro crea las siguientes columnas:
a) Viendo desde el punto de vista de las cuentas de bancos:
Col D: los números (bcos) y en col E: las veces que aparecen
col F: las veces que esas cuentas aparecen en la lista de empresas
b) Viendo desde el punto de vista de las cuentas de empresas.
Col G: los números (empresas) y en col H las veces que aparecen
col I: las veces que esas cuentas aparecen en la lista de bcos.
¿A medida que? ¿Limpies? las columnas A y B, ¿Podes borrar las col D hasta I y volver a ejecutar la macro para volver a revisar si no quedó nada por? ¿Limpiar?.
(Por limpieza me refiero a si vas a eliminar las que figuran de más)
IMPORTANTE: previo a la ejecución de esta macro, debes ordenar cada columna (por separado) en forma ascendente.
Con Alt+F11 activas el Editor, doble clic en la hoja donde están tus datos, y pega lo siguiente:
Sub conciliacion()
Dim conta As Integer
Dim veces As Integer
Dim fila As Integer
Dim col As Integer
Dim valor1 As Variant
veces = 1
While veces < 3
If veces = 1 Then
col = 4
Range("A2").Select
Else
col = 7
Range("B2").Select
End If
fila = 2
conta = 1
valor1 = ActiveCell.Value
While ActiveCell.Value <> 0
If ActiveCell.Offset(1, 0).Value <> valor1 Then
Cells(fila, col).Value = valor1
Cells(fila, col + 1).Value = conta
fila = fila + 1
ActiveCell.Offset(1, 0).Select
valor1 = ActiveCell.Value
conta = 1
Else
conta = conta + 1
ActiveCell.Offset(1, 0).Select
End If
Wend
veces = veces + 1
Wend
'RUTINA QUE EVALUA LAS DIFERENCIAS (deberás ajustar el rango que en el ejemplo va de F2 a F13 y en la segunda va de I2 a I13)
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=+IF(ISNA(VLOOKUP(RC[-2],R2C7:R13C8,2,FALSE)),0,VLOOKUP(RC[-2],R2C7:R13C8,2,FALSE))"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F13"), Type:=xlFillDefault
Range("F2:F13").Select
Range("I2").Select
ActiveCell.FormulaR1C1 = _
"=+IF(ISNA(VLOOKUP(RC[-2],R2C4:R13C5,2,FALSE)),0,VLOOKUP(RC[-2],R2C4:R13C5,2,FALSE))"
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I13"), Type:=xlFillDefault
Range("I2:I13").Select
End Sub
(Espero que todo se haya copiado bien (he visto que el sitio a veces reemplaza las comillas por signos de interrogación)).
Espero haber entendido bien cual es el resultado que buscas. Si no, podes solicitar una aclaración y lo seguimos tratando.
Si en la col A están las cuentas de Bancos, y en la col B las de empresas, la macro crea las siguientes columnas:
a) Viendo desde el punto de vista de las cuentas de bancos:
Col D: los números (bcos) y en col E: las veces que aparecen
col F: las veces que esas cuentas aparecen en la lista de empresas
b) Viendo desde el punto de vista de las cuentas de empresas.
Col G: los números (empresas) y en col H las veces que aparecen
col I: las veces que esas cuentas aparecen en la lista de bcos.
¿A medida que? ¿Limpies? las columnas A y B, ¿Podes borrar las col D hasta I y volver a ejecutar la macro para volver a revisar si no quedó nada por? ¿Limpiar?.
(Por limpieza me refiero a si vas a eliminar las que figuran de más)
IMPORTANTE: previo a la ejecución de esta macro, debes ordenar cada columna (por separado) en forma ascendente.
Con Alt+F11 activas el Editor, doble clic en la hoja donde están tus datos, y pega lo siguiente:
Sub conciliacion()
Dim conta As Integer
Dim veces As Integer
Dim fila As Integer
Dim col As Integer
Dim valor1 As Variant
veces = 1
While veces < 3
If veces = 1 Then
col = 4
Range("A2").Select
Else
col = 7
Range("B2").Select
End If
fila = 2
conta = 1
valor1 = ActiveCell.Value
While ActiveCell.Value <> 0
If ActiveCell.Offset(1, 0).Value <> valor1 Then
Cells(fila, col).Value = valor1
Cells(fila, col + 1).Value = conta
fila = fila + 1
ActiveCell.Offset(1, 0).Select
valor1 = ActiveCell.Value
conta = 1
Else
conta = conta + 1
ActiveCell.Offset(1, 0).Select
End If
Wend
veces = veces + 1
Wend
'RUTINA QUE EVALUA LAS DIFERENCIAS (deberás ajustar el rango que en el ejemplo va de F2 a F13 y en la segunda va de I2 a I13)
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=+IF(ISNA(VLOOKUP(RC[-2],R2C7:R13C8,2,FALSE)),0,VLOOKUP(RC[-2],R2C7:R13C8,2,FALSE))"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F13"), Type:=xlFillDefault
Range("F2:F13").Select
Range("I2").Select
ActiveCell.FormulaR1C1 = _
"=+IF(ISNA(VLOOKUP(RC[-2],R2C4:R13C5,2,FALSE)),0,VLOOKUP(RC[-2],R2C4:R13C5,2,FALSE))"
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I13"), Type:=xlFillDefault
Range("I2:I13").Select
End Sub
(Espero que todo se haya copiado bien (he visto que el sitio a veces reemplaza las comillas por signos de interrogación)).
Espero haber entendido bien cual es el resultado que buscas. Si no, podes solicitar una aclaración y lo seguimos tratando.
Respuesta de César Mera
1
Sí se puede hacer con rutina de VBA (una macro) pero me parece que es demasiado para resolver el problema, pienso que tu problema se puede resolver con una formulita que reste el número de apariciones de cada valor en la primera columna respecto de la segunda... a ver si me explico mejor, contar el número de veces que aparece el número de cuenta en la primera columna y restarselo al número de veces que aparece en la segunda... lo que quiere decir que si no da 0 tenemos un problema.
En resumen, la fórmula que describo a continuación resta lo descrito para el valor de la celda A1
=CONTAR.SI(A1:A6,A1)-CONTAR.SI(B1:B6,A1)
Si definitivamente crees que es mejor con VBA me avisas y me dices que resultados quieres que arroje o que haga con los números en los que encuentre problema ... otra opción sería desarrollar una función personalizada que arroje verdadero o falso de acuerdo con los parámetros.
Me cuentas.
En resumen, la fórmula que describo a continuación resta lo descrito para el valor de la celda A1
=CONTAR.SI(A1:A6,A1)-CONTAR.SI(B1:B6,A1)
Si definitivamente crees que es mejor con VBA me avisas y me dices que resultados quieres que arroje o que haga con los números en los que encuentre problema ... otra opción sería desarrollar una función personalizada que arroje verdadero o falso de acuerdo con los parámetros.
Me cuentas.
Respuesta de fejoal
1
Entendí, creo, la situación; pero me falta saber qué hacer con los números repetidos asimétricamente.
Por lo pronto, te propondré como solución marcarlos con colores, luego me dirás si esto te sirve o prefieres otra acción. Es más, hasta podría ser que conociendo la lógica de la solución, tu mismo puedas adaptarla a tu situación particular.
Al igual que en tu pregunta anterior, no será necesario que desarrolles macro alguna para este proceso. Como verás, la idea es echar mano a VBA cuando agotas las alternativas implícitas en MS Excel. Desde luego, esto implica conocer bastante de las potencias de MS Excel antes de volcarse a programar.
En este caso particular, la solución viene de la mano de los "Formatos Condicionales".
Claro, estos tienen un límite de tres condiciones que considerando el formato normal de la celda te las eleva a cuatro y, creo, habría cuatro situaciones posibles, así que nos servirá.
Veamos:
Lo primero que haremos es darle un fondo rojo a la primera celdas de ambas columnas (por ejemplo, F1 y G1).
Luego, usaremos el "Formato condicional".
Este se encuentra debajo de la opción "Formato" del menú principal. Cuando la activas, habiendo seleccionado la celda donde quieres aplicarlo, una pantalla de diálogo te pedirá que ingreses la condición.
Esta puede ser evaluada sobre la misma celda (Valor de la celda) o -como en tu caso- sobre otra/s celda/s distinta/s a la actual (opción Fórmula).
Para la primera casilla asumiré que, en algún momento la celda pueda estar en blanco, sin datos. Por eso la primer fórmula a introducir (en el casillero a la derecha de "Fórmula") será:
=ESBLANCO(F1)
Luego presiona el botón "Formato" y busca la solapa de "Tramas" y elige el color blanco de la paleta. Presiona Aceptar para volver a la pantalla anterior.
De esta manera habrás definido la primer condición para esa celda que cambiará el color rojo de base por blanco si esta celda estuviera vacía.
Ahora, presiona "Agregar>>" e ingresaremos la segunda condición (que opera si no se cumple la primera). También, selecciona la opción "Fórmula" en el menú de la izquierda y en la caja de la derecha pega esta fórmula:
=CONTAR.SI($G$1:$G$11,F1)=0
[Considera si sueles usar comas o punto y coma para separar argumentos de las funciones. Yo usé ","]
Bien. Esta segunda fórmula determina si el valor de F1 no se encuentra en la columna de al lado.
Si no estuviese, pintará la celda del color que le indiques con el botón "Formato", busca la solapa de "Tramas" y elige -por ejemplo- el color amarillo de la paleta.
Definido el segundo caso, vamos al tercero.
De la lectura de tu pregunta infiero que si un valor se encuentra una vez en cada columna esto estaría OK. También sería válido que esté varias veces, en cuanto se repitiera la misma cantidad en ambas columnas. (?)
Para evaluar estas dos situaciones correctas, deberás aplicar la siguiente fórmula:
=CONTAR.SI($F$1:$F$11;F1)=CONTAR.SI($G$1:$G$11;F1)
Es decir si el conteo del valor en la celda es igual en ambas columnas (una o más veces) esto está bien. De la paleta de colores de trama selecciona el verde.
Presiona "Aceptar" dos veces y saldrás de este cuadro de diálogo aceptando estos tres formatos para la primera celda.
Tratándose -precisamente- de un formato, podrás copiarlo a otras celdas como cualquier otro formato de celda.. Copia esta celda y con Pegado Especial...[Formato] aplícaselo a la celda a la derecha de esta. Con Formato | "Formato Condicional" accederás a la misma pantalla que estuviste trabajando antes, pero esta vez para la celda G1. Aquí deberemos modificar la segunda condición para que lea la columna a su izquierda: el rango $F$1:$F$11
Es decir que deberás reemplazar la fórmula que halla allí por la siguiente:
=CONTAR.SI($F$1:$F$11;G1)=0
Las otras dos fórmulas seguirán siendo válidas, por lo cual no es necesario que las modifiques, pero no está de más que las controlen para que lean los rangos correctos.
Bien, finalmente basa que copies este par de celdas y pegues sus formatos al resto de las que tengas debajo de ellas.
Por lo tanto, tendrás un semáforo que
Si la celda tiene un dato (si está vacía, quedará blanca)...
Si el valor se encuentra en ambas columnas (sino mostrará amarillo)...
Si la cantidad de veces en cada columna fuese diferente (caso contrario, se pondrá en verde)...
... quedará con el color rojo que le habías dado originalmente, indicando que tienen un problema.
Cierto es largo de explicar es realmente simple armarlo. También podría hacerlo una macro, pero no sería instantáneo como esto y, además, deberías recordar ejecutarla cada vez que haya un cambio.
Espero no haberte aburrido y que esto sirva para tu propósito.
Un abrazo!
Fernando
Por lo pronto, te propondré como solución marcarlos con colores, luego me dirás si esto te sirve o prefieres otra acción. Es más, hasta podría ser que conociendo la lógica de la solución, tu mismo puedas adaptarla a tu situación particular.
Al igual que en tu pregunta anterior, no será necesario que desarrolles macro alguna para este proceso. Como verás, la idea es echar mano a VBA cuando agotas las alternativas implícitas en MS Excel. Desde luego, esto implica conocer bastante de las potencias de MS Excel antes de volcarse a programar.
En este caso particular, la solución viene de la mano de los "Formatos Condicionales".
Claro, estos tienen un límite de tres condiciones que considerando el formato normal de la celda te las eleva a cuatro y, creo, habría cuatro situaciones posibles, así que nos servirá.
Veamos:
Lo primero que haremos es darle un fondo rojo a la primera celdas de ambas columnas (por ejemplo, F1 y G1).
Luego, usaremos el "Formato condicional".
Este se encuentra debajo de la opción "Formato" del menú principal. Cuando la activas, habiendo seleccionado la celda donde quieres aplicarlo, una pantalla de diálogo te pedirá que ingreses la condición.
Esta puede ser evaluada sobre la misma celda (Valor de la celda) o -como en tu caso- sobre otra/s celda/s distinta/s a la actual (opción Fórmula).
Para la primera casilla asumiré que, en algún momento la celda pueda estar en blanco, sin datos. Por eso la primer fórmula a introducir (en el casillero a la derecha de "Fórmula") será:
=ESBLANCO(F1)
Luego presiona el botón "Formato" y busca la solapa de "Tramas" y elige el color blanco de la paleta. Presiona Aceptar para volver a la pantalla anterior.
De esta manera habrás definido la primer condición para esa celda que cambiará el color rojo de base por blanco si esta celda estuviera vacía.
Ahora, presiona "Agregar>>" e ingresaremos la segunda condición (que opera si no se cumple la primera). También, selecciona la opción "Fórmula" en el menú de la izquierda y en la caja de la derecha pega esta fórmula:
=CONTAR.SI($G$1:$G$11,F1)=0
[Considera si sueles usar comas o punto y coma para separar argumentos de las funciones. Yo usé ","]
Bien. Esta segunda fórmula determina si el valor de F1 no se encuentra en la columna de al lado.
Si no estuviese, pintará la celda del color que le indiques con el botón "Formato", busca la solapa de "Tramas" y elige -por ejemplo- el color amarillo de la paleta.
Definido el segundo caso, vamos al tercero.
De la lectura de tu pregunta infiero que si un valor se encuentra una vez en cada columna esto estaría OK. También sería válido que esté varias veces, en cuanto se repitiera la misma cantidad en ambas columnas. (?)
Para evaluar estas dos situaciones correctas, deberás aplicar la siguiente fórmula:
=CONTAR.SI($F$1:$F$11;F1)=CONTAR.SI($G$1:$G$11;F1)
Es decir si el conteo del valor en la celda es igual en ambas columnas (una o más veces) esto está bien. De la paleta de colores de trama selecciona el verde.
Presiona "Aceptar" dos veces y saldrás de este cuadro de diálogo aceptando estos tres formatos para la primera celda.
Tratándose -precisamente- de un formato, podrás copiarlo a otras celdas como cualquier otro formato de celda.. Copia esta celda y con Pegado Especial...[Formato] aplícaselo a la celda a la derecha de esta. Con Formato | "Formato Condicional" accederás a la misma pantalla que estuviste trabajando antes, pero esta vez para la celda G1. Aquí deberemos modificar la segunda condición para que lea la columna a su izquierda: el rango $F$1:$F$11
Es decir que deberás reemplazar la fórmula que halla allí por la siguiente:
=CONTAR.SI($F$1:$F$11;G1)=0
Las otras dos fórmulas seguirán siendo válidas, por lo cual no es necesario que las modifiques, pero no está de más que las controlen para que lean los rangos correctos.
Bien, finalmente basa que copies este par de celdas y pegues sus formatos al resto de las que tengas debajo de ellas.
Por lo tanto, tendrás un semáforo que
Si la celda tiene un dato (si está vacía, quedará blanca)...
Si el valor se encuentra en ambas columnas (sino mostrará amarillo)...
Si la cantidad de veces en cada columna fuese diferente (caso contrario, se pondrá en verde)...
... quedará con el color rojo que le habías dado originalmente, indicando que tienen un problema.
Cierto es largo de explicar es realmente simple armarlo. También podría hacerlo una macro, pero no sería instantáneo como esto y, además, deberías recordar ejecutarla cada vez que haya un cambio.
Espero no haberte aburrido y que esto sirva para tu propósito.
Un abrazo!
Fernando
Ha sido una muy buena idea, ha faltado resolver las repeticiones asimétricas que es el quiz de la pregunta, pero creo que ya sé como hacerlo. Gracias.