Cruzar dos bases de datos en excel es una tarea muy requerida, en Aplicaciones Contables suele ser muy necesaria para depurar registros. En algunos casos se desea saber cuales datos se repiten, cuantas veces se encuentra un dato en la otra base de datos. Para otras situaciones lo que se desea es eliminar los duplicados ( los que también están en la otra lista ). En fin, son muchas las razones y los resultados que se desean obtener al cruzar dos bases de datos en excel.
Veremos un caso sobre el cruce de bases de datos en excel, buscamos en este ejemplo, es cruzar dos listados con nombre y correo electrónico. Los registros de una lista pueden estar repetidos en la otra, en ese caso la idea es identificarlos de algún modo.
1.- Macro para cruzar bases de datos y marcar cada registro encontrado.
Qeremos simplemente poner un fondo verde a cada registro que también este en la otra lista de datos, asi identificamos cada registro duplicado.
Esto se puede hacer fácilmente mediante el uso de una macro.
A continuación les dejo un vídeo donde se explican algunas opciones para cruzar bases de datos en excel con el uso de macros y formulas.
Link de descarga del ejemplo al final del post
El proceso es realmente simple, solo se debe buscar el dato de una tabla en la otra, si lo encuentra, entonces se decide que hacer. Aqui solo poner un color de fondo a la celda, pero igual puede ser cualquier otro procedimiento, como poner un texto en la celda de otra columna, eliminar la fila, etc.
Ya en esa parte depende de tus necesidades al hacer el cruce de datos, la parte fundamental el el ciclo que recorre los datos y la función que hace la búsqueda.
Código VBA para cruzar bases de datos
Sub CruzaBDColorFondo()
Application.ScreenUpdating = False
Sheets("depura").Activate
Range("B2").Activate
Do While ActiveCell.Value <> ""
If Not IsError(Application.VLookup(ActiveCell, Range("base"), 1, False)) Then
ActiveCell.Interior.ColorIndex = 50
ActiveCell.Offset(1).Activate
Else
ActiveCell.Offset(1).Activate
End If
Loop
Application.ScreenUpdating = True
End Sub
Lo que hace este código es recorrer la columna de correo electrónico de la hoja “depura” y busca cada uno de ellos en la base de datos de la hoja “bd”. Si el correo es encontrado deberá poner un fondo verde a la celda activa. Si no se pasa al siguiente, la macro se detiene cuando encuentra una celda vacía.
En el código de la macro encontramos Range(“base”) , “base” corresponde al nombre que le hemos dado al rango de datos donde esta el correo electrónico de la base de datos en la hoja “bd” ( que es donde vamos a buscar los registros de la otra hoja ). Para poner ese nombre de rango, solo debemos seleccionar el rango de celdas donde están los correos, en este caso es la columna B de de la hoja “bd” y tecleamos el nombre “base” en el cuadro de nombres ( justo arriba de la celda A1 )
En la hoja donde tenemos los registros que deseamos cruzar, hay un botón para ejecutar la macro, al presionar ese botón, la macro deberá ejecutar el código anterior .
2.- Macro para cruzar bases de datos y poner un texto cuando el dato sea encontrado.
En este caso se repite el mismo ciclo para hacer la búsqueda, pero cada vez que un dato sea encontrado, en la columna de a lado deberá poner algún texto que nos indique que ese dato también esta en la otra lista de datos.
Así que se usa el mismo código anterior y solo cambiamos esta linea
ActiveCell.Interior.ColorIndex = 50
Por esta
ActiveCell.Offset(0, 1) = "Repetido"
3.- Macro para cruzar bases de datos y eliminar los duplicados.
En este cruce de datos, lo que se busca es eliminar de una lista los registros que sean encontrados en la otra, para esto seguiremos usando el mismo código, solo con un pequeño cambio.
Cambiar esta parte del código
ActiveCell.Interior.ColorIndex = 50
ActiveCell.Offset(1).Activate
Por esta otra.
ActiveCell.EntireRow.Delete
4.- Cruzar bases de datos con formulas.
También se pueden cruzar dos bases de dato en excel usando formulas, en este caso es para saber cuales registros se encuentran en ambas bases de datos, para esto usamos una formula que deberá poner un “SI” cuando el registro se encuentre.
Para este caso usamos esta formula: =SI(CONTAR.SI(base,B2)>=1,”SI”,””)
Si el correo se encuentra por lo menos una vez en la otra hoja, pondrá un SI, si el conteo es cero no pone nada.
Otra opción es poner solo la cantidad de veces que se encuentra el correo en la otra hoja, si es uno o mas, sabremos que el registro también esta en la otra hoja.
Esta es la formula: =CONTAR.SI(base,B2)
Con estas dos ultimas opciones, se puede usar filtro avanzado para mostrar solo los registros que tienen un SI o los que tienen por lo menos 1 en conteo.
Constanza dice
Hola Nolberto, tengo una duda con el código, cómo lo puedo hacer para que elimine los registros repetidos en ambas pestañas, es decir que sólo queden aquellos registros únicos en ambas hojas. Saludos,
Hache dice
Hola Nolberto, quiero ofrecer un servicio nuevo en mi empresa, en mi web para ser exacto donde hago una encuesta y los resultados de la encuesta me saltan automáticamente en gooogle docs, en una tabla excell. Cada cliente es una línea nueva y las Columnas son preguntas que hacen otras personas, pero esas personas tb han respondido a las mismas preguntas y quiero cruzar las respuestas para ver quien ha respondido lo mismo. Como se puede hacer o a quien debo buscar para que me lo haga? Un analista de datos o alguien experto en excell me valdría? Muchas gracias de antemano
Nolberto Lopez dice
Saludos.
Favor de enviar un ejemplo de los datos y el resultado que le gustaria obtener, esto es con el fin de poder hacer pruebas y en su caso una propuesta al respecto.
Ricardo Angeles Escobar dice
Hola que tal. Al aplicar las fórmulas en un archivo las ejecuta, pero cuando lo hago en otro diferente con la misma base de datos, no ejecuta las fórmulas de conteo.
Nolberto Lopez dice
Saludos Ricardo.
No ha indicado si obtiene algún mensaje de error durante la ejecución, así que las posibilidades de que se le brinde una solución acertada son pocas.
Aun asi puedo decir que lo mas probable es que el fallo este en los nombres de hojas y nombres de rango que se manejan en el ejemplo, quizá usted puso en su archivo la macro tal como en el ejemplo, pero en su hoja no esta usando los mismos nombres de hoja y rangos de dicha macro.
Ricardo Angeles Escobar dice
No envía mensaje de error. No estoy utilizando la macro, sólo las fórmulas de conteo. Simplemente se queda en la celda la sintaxis de la fórmula, sin ejecutarse. =SI(CONTAR.SI(BDGENERAL!X1:X1338,TANIA!C2)>=1,”SI”,””)
Gracias por tan pronta respuesta. saludos
Ricardo Angeles Escobar dice
¡Muchas gracias! Ya lo resolví. Tenía que ver con el formato de las celdas. Estaba en texto y lo convertí a general. Saludos
Nolberto Lopez dice
Bien Ricardo.
Ademas del formato de texto, otra cosa que deja a la vista las formulas como tal es la opción “Mostrar Formulas” en la sección “Auditoria de formulas del menú “Formulas”
Antonio dice
Excelente. Sin embargo, tengo una inquietud: ¿como sería la linea de código si a alguien se le ocurriera eliminar o insertar filas antes de la fila 2? Gracias por compartir.
Rodolfo Eduardo Cervantes dice
gracias por tu enseñanza, muy bueno todo, te pido un favor necesito hacer credenciales de identificación, se puede combinar excel y photoshop, de ser así podrías indicarlo gracias
Felix dice
Si eres el mismo Norberto de lawebdelprogramador, tu me has ayudado enormemente, para lo cual sigo muy agradecido. Efectivamamente eres el mejor experto que he conocido en la web. Muchas felicidades para tí . continua con altruismo y profesionalismo muchos estamos agradecidos por tus aportes.
danilo efren dice
De los mejores ejemplos.
gracias por compartirlos.
un SALUDO!!!
Danilo
danilo efren dice
Gracias una ves mas excelente ejemplo
y como siempre no dejas nada para las dudas.
Danilo
EDGAR dice
TENGO DOS BASES DE DATOS CADA UNA CON 4 COLUMNAS. CODIGO A, CODIGO AA, DESCRIPCION Y UNIDAD, EN AMBAS COMPARTES UN SOLO CODIGO EN COMUN, DESCRIPCION Y UNIDAD, MI PREGUNTA ES SI PUEDO HACER EL CRUCE PARA QUE ME APARESCA LOS TRES CODIGOS, LA DESCRIPCION Y LA UNIDAD EN UNA SOLA BASE??
TE AGRADESCO TU AYUDA
Hugo Hdez dice
Este macro se podría modificar para obtener los valores únicos de una tabla en otra tabla??
Nolberto Lopez dice
La macro es totalmente modificable…
Nolberto Lopez dice
Esta justo debajo del video, debe compartir en las redes sociales para verlo.
nancy dice
Por favor ,disculpe mi ignorancia ,pero estoy aprendiendo excel ,y me sorprendo las herramientas valiosas que tiene,pero me pierdo,que formula debo usar si: en una columna tengo los ,dias de la semene( de miercoles a jueves)con lista desplegable,nombres de los trabajadores con lista desplegable,en otra el menú que adquirieron en el almuerzo ,para pagar posteriormente,(quiero que me salga el precio según menú) (ya aqui estoy estancada,para luego me diese un reporte o facilitara la busqueda por trabajadador,cuanto consumio en la semana y debe pagar..gracias ,espero que me ayude que pasos seguir
Nolberto Lopez dice
Saludos Nancy.
En este caso le sirve la funcion buscarv y tambien el ejemplo de suma condicional
gonzalo dice
Este ejemplo está muy bueno, muy práctico. se puede utilizar en muchas aplicaciones.
Julio Garcìa dice
Este ejemplo está muy bueno, muy práctico. se puede utilizar en muchas aplicaciones.