Ya he tratado el tema en diversas oportunidades, por ejemplo como comparar dos listas usando formato condicional o como evitar duplicados usando validación de datos. También he mostrado como usar consolidación de datos para comparar listas
En esta nota haremos una revisión ordenada del tema. Empecemos por plantearnos un ejemplo. En una misma hoja tenemos dos listas de productos
Para facilitar nuestro trabajo hemos definido dos nombres
lista1 =Hoja1!$A$2:$A$11
lista2 =Hoja1!$D$2:$D$11
1 - Encontrar duplicados usando la función COINCIDIR
En el rango B2:B11 ponemos la fórmula =COINCIDIR(A2,lista2,0); en el rango E2:E11 ponemos la fórmula =COINCIDIR(D2,lista1,0). El resultado es
Los resultados N/A identifican los valores de la lista 1 que no se encuentran en la lista 2, y viceversa. Podemos lograr un resultado más "elegante" combinando COINCIDIR con SI y ESERROR en esta fórmula
=SI(ESERROR(COINCIDIR(A2,lista2,0)),"","duplicado")
El resultado es
Podemos mejorar aún más la presentación usando formato condicional para dar un fondo de color a los duplicados. Por ejemplo, para la lista2 definimos esta condición
La dirección de la celda de referencia debe ser relativa (sin los signos $)
2 – Señalar duplicados usando formato condicional.
Como explicamos en la nota sobre el tema, usamos la función CONTAR.SI en el menú de formato condicional para comprobar si valores de la lista 1 también figuran en la lista 2. Usamos una fórmula distinta para cada lista.
En la lista 1 usamos la fórmula =CONTAR.SI(lista2,A2)>0
En la lista 2 usamos la fórmula =CONTAR.SI(lista1,A2)>0
El resultado es idéntico al método anterior
3 – Generar una lista de valores comunes a ambas listas
Digamos que ahora queremos generar una lista de valores que aparecen en ambas listas.
En nuestro ejemplo, escribimos esta fórmula en la columna F:
=SI(CONTAR.SI(lista2,A2)>0,A2,"")
Alternativamente podemos usar la fórmula =SI(CONTAR.SI(lista1,D2)>0,D2,""), que dará el mismo resultado aunque en distinto orden.
Como podemos ver en la lista de valores comunes aparecen celdas en blanco. Una alternativa para eliminar las celdas en blanco es copiar los valores de la lista a otro rango y usar la función Ir A--Especial--Celdas en Blanco, como explicamos en esta nota.
También podemos hacerlo usando fórmulas. Para esto necesitaremos crear dos rangos de fórmulas auxiliares.
Siguiendo con nuestro ejemplo, escribimos esta fórmula en la celda H2 y la copiamos a lo largo del rango
=SI(CELDA("contenido",F2)="",0,FILA())
Esta fórmula nos permite dar un número único si en la columna F aparece un producto común a ambas listas o 0 (cero) si el resultado en F es blanco. Usamos la función CELDA y no ESBLANCO, ya ninguna de las celdas en la columna F está en blanco (todas contienen fórmulas).
El próximo paso es usar la función JERARQUIA en la columna I
=JERARQUIA(H2,$H$2:$H$11)
En esta fórmula hemos omitido el argumento opcional "orden", por lo tanto el resultado aparece en forma descendiente.
Podemos ver que para las celda con resultado 0 en la columna H, obtenemos el mismo número de orden.
El resultado en la columna I nos sirve de variable en la fórmula que escribimos en la columna J,
=INDICE($F$2:$F$11,COINCIDIR(FILA()-1,$I$2:$I$11,0))
que nos da esta lista
El problema con esta fórmula es que aparecen resultados N/A. Para evitar esto agregamos una condición a nuestra fórmula, resultando
=SI(ESERROR(INDICE($F$2:$F$11,COINCIDIR(FILA()-1,$I$2:$I$11,0))),"",INDICE($F$2:$F$11,COINCIDIR(FILA()-1,$I$2:$I$11,0)))
es decir, primero evaluamos la fórmula y si el resultado da error, la función SI da como resultado "blanco", si no se aplica la función INDICE.
4 – Generar una lista de valores únicos
Para generar una lista de valores únicos, usamos el mismo método con la diferencia que la fórmula en la columna F pasa a ser
=SI(CONTAR.SI(lista2,A2)=0,A2,SI(CONTAR.SI(lista1,D2)=0,D2,""))
donde combinamos dos condiciones para obtener los productos que sólo aparecen en una de las listas.
Por supuesto, existen otros métodos para comparar listas, incluyendo el uso de macros. Esto será tema de una futura nota.
Categorías: Funciones&Formulas_, Manejo de Datos_
Technorati Tags: Excel, Duplicados en Excel
Oye tengo una duda como puede comparar un vector con otro y que me devuelva que que parte se asemejan, ejemplo vectorA 1 2 3 4 5 6 7 y vectorB 4 3 2 1 5 6 7, quiero que me diga que posicion del vectorsB ocupan las semejanzas del 1, me entendes?
ResponderBorrarbye
Para comparar vectores, y calcular la posicion de un miembro de un vector en otro vector, utilizas la funcion CONCIDIR, como indico en esta entrada.
ResponderBorrarPuedes descargar un archivo con tu ejemplo aqui.
En este ejemplo uso Validacion de Datos para generar la lista desplegable en la celda D2.
La formula en la celda E2 es =COINCIDIR(D2,B2:B8,0)
Espero haber entendido
Chau
Mil gracias por tan valiosa informacion!!! no sabes como necesitaba esta informacion, sos un grande.!!
ResponderBorrarHola Jorge, fuera del tema de esta entrada... Estoy tratando de que me muestre los datos que estén duplicados dentro de una misma columna, sin borrarlos, pero destacándolos de la misma forma que lo haces tu acá... con un formato condicional. Por favor, me puedes ayudar.
ResponderBorrarHola Pablo,
ResponderBorrarno tan fuera del tema. Suponiendo que el rango de datos a comparar empieza en A1, escribís en la ventanilla Fórmula de Formato Condicional
=CONTAR.SI($A$1:A1;A1)>1
Fijate que el el primer A1 es una dirección absoluta ($A$1, con el signo $).
(Alexander Beltran Colombia)
ResponderBorrarhola jorge tengo una duda en
ejemplo q expones de comparar listas, al principio muestras como nombras a cada una de las lista con un rango especifico "
lista1 =Hoja1!$A$2:$A$11
lista2 =Hoja1!$D$2:$D$11
cuando ingreso la formula de
=COINCIDIR(A2,lista2,0); el sistema no me reconoce el nombre del campo q en este caso es lista 1 o lista 2, yo manejo el excel 2003 del paquete de office en español. mil gracias jorege espero me puedas colaborar con esta consulta estoy atento a tu respuesta gracias
Lo mejor sería que me mandes el archivo así para revisarlo (jorgedun@gmail.com).
ResponderBorrarSi estás copiando las fórmulas de la página, ten en cuenta que dependiendo del sistema el separador de los argumentos puede ser ";" y no ",".
Otro problema podría ser algún error en el nombre que pones en la fórmula. Para pegar los nombres conviene usar la tecla F3.
En el punto 3 cuando dices: "Una alternativa para eliminar las celdas en blanco es copiar los valores de la lista a otro rango y usar la función Ir A--Especial--Celdas en Blanco, "
ResponderBorrarNo acaba de salir, una vez copiados solos los valores y ejecutando esta función el sistema responde que no hay celdas en blanco. ¿es posible que al colocar los "" la celda ya no este en blanco ?
gracias
hola,
ResponderBorrarsi usamos esta formula para generar una lista de valores unicos:
=SI(CONTAR.SI(lista2,A2)=0,A2,SI(CONTAR.SI(lista1,D2)=0,D2,""))
que sucede si por coincidencia los valores unicos estan en la misma fila? No es cierto que el valor unico de la segunda lista no seria tenido en cuenta.
Ciertamente se cumpliria la primera parte de la condición y este otro valor se pasaria por alto.
Como se podria solucionar?
Hola Tecno,
ResponderBorrarse trata de copiar los valores de las celdas a otro rango. De esta manera, cuando el resultado de la fórmula en el rango original es "", al copiar se obtiene una celda en blanco.
La celda original que contiene la formula, aunque no muestra ningún valor, no es una celda en blanco.
La fórmula =SI(CONTAR.SI(lista2,A2)=0,A2,SI(CONTAR.SI(lista1,D2)=0,D2,"")) prioriza los valores que se encuentran en la columna A. Sólo si no son unicos busca en la D.
ResponderBorrarHola Jorge:
ResponderBorrarMe encantá tu blog... Tengo una pregunta:
Habrá una manera de comparar textos parecidos: Ejemplo; Si tengo una compañía que se llama
yashve C.A. y la otra Yahve CA.. el me podra decir que porcentaje de coincidencia hay entre esos dos nombres??
Gracias
Hola
ResponderBorraren principio se puede hacer pero hay que definir ciertas condiciones. Por ejemplo, podemos calcular que "Yashve" es muy similar a "Yashve CA". Pero que pasa con "Yashve" y "Veyash"? O con
"Yashve" y "Yavesh" cuando Yavesh es realmente un texto distinto aunque parecido?
Hola Jorge, tengo el problema casi resuelto, mediante la siguiente formula "=BUSCARV(Alcaudete!B2;España!B2:B2051;1;FALSO)", pero necesito que en la busqueda me de el registro completo, o sea, la linea donde coincide el dato ¿me explico?.
ResponderBorrarTe agradezco la ayuda de antemano.
Eugenio
ResponderBorrarlas fórmulas dan resultados en una celda, no en una fila. Para producir un registro completo a partir del valor de una celda, tienes que usar una macro.
Buenas tardes, Jorge. llevo bastante tiempo pensando como sacar adelante una fórmula ayudandome de la página, pero no doy con la tecla:Tengo una hoja con datos de una cuenta corriente donde se abonan y cargan importes en distintas divisas y por distintos conceptos.Cuando creo subtotales por conceptos se suman todos los importes del mismo concepto: p.e. Ingreso en efectivo, recibos...pero la suma no distingue entre divisas y me lo suma todas las cantidades en euros.He solucionado la obtención de las cotizaciones diarias de divisas mediante actualización por internet y he añadido una columna "tipo de cambio". Mi interés está en que cuando exista un movimiento de cuenta en divisas, p.e: Dolar americano (en siglas sería USD), Libras esterlinas (GBP)poder pasarlo a euros para que al sumar con subtotales la cantidad sea real en euros. Para ello intento obtener una fórmula que cuando encuentre siglas distintas al euro (EUR) como GBP o USD me introduzca en la columna de tipo de cambio el valor obtenido por internet (que esta en una tabla de otra hoja), de modo que simplemente divida la cantidad en divisas por el tipo de cambio, me de el valor en euros y, por lo tanto, el subtotal en euros. GRACIAS.
ResponderBorrarEstimado
ResponderBorrarlos comentarios tienen que estar relacionados con el tema de la nota. Te sugiero que consultes en alguno de los muchos, y buenos, foros de ayuda de Excel que hay en la WEB.
De todas maneras, y a partir de tu descripción: creas una tabla con las tasas de conversión de las divisas; en una columna auxiliar traduces todo a euros con la función BUSCARV.
Hola Jorge:
ResponderBorrarAunque esta entrada sea antigua, creo que conviene comentar una cosa para que no haya lugar a error.
En el punto 4-Generar una lista de valores únicos, pienso que esa fórmula no responde al título del punto (lista de valores únicos) porque podría haber valores únicos en la misma fila, tal como decía el usuario Tecno.
Si queremos la lista completa de valores únicos de las 2 columnas habría que añadir algo más.
Una solución posible, aunque no del todo satisfactoria, sería:
=SI(Y(CONTAR.SI(lista2;A2)=0;CONTAR.SI(lista1;D2)=0);A2&" - "&D2;SI(CONTAR.SI(lista2;A2)=0;A2;SI(CONTAR.SI(lista1;D2)=0;D2;"")))
De esta forma, cuando hubiera coincidencia en la misma fila de 2 valores únicos (en tu ejemplo en la fila 7), aparecería algo así como Producto 7 - Producto 6.
Supongo que lo ideal sería desarrollar una matriz donde cada valor encajara en una celda separada, pero esto no sé cómo se haría.
Por lo demás, esta nota me parece muy buena.
Un saludo,
SL
buen dia, quisiera saber como realizar en excel 2003 el conteo de celdas por el color.
ResponderBorrarFijate en esta nota.
ResponderBorrarEn esta otra nota muestro funciones definidas por el usuario para contar o sumar por colores.
Cordial Saludo
ResponderBorrarDesearia conocer si es posible comparar una fila de una matriz de 5000 datos con una fila que se encuentra en la matriz de datos de 60000 datos y me señale la coincidencia.
Muchas Gracias por tu colaboracion
Hola Joge,
ResponderBorrar¿la matriz está en Excel? Si los datos están en filas supongo que hay un dato por cada celda de la fila. En Excel 2007 disponemos de 16384 columnas, por lo que no podríamos poner 60000 datos en una fila.
Estimado Jorge, mi pregunta es la siguiente:
ResponderBorrarTengo dos bases de datos, con descripciones de productos cada una de ellas, relacionadas a un código. Una descripción textual por celda por cada producto. Las descripciones de una base y la otra son muy parecidas pero no iguales(respecto del mismo producto).Esto por un cambio de sistemas. Existe alguna forma de poder relacionarlas de acuerdo a la mayor similitud que puedan presentar, una referencia de una lista, vs la otra lista.
Saludos. Gary.
Gary,
ResponderBorrarse puede hacer pero los resultados pueden ser erróneos. Los productos, ¿no tienen el mismo número de catálogo en ambas tablas?
Estimado Jorge, lastimosamente NO tienen el mismo número de catálogo o código, por lo que resulta complicado justamente relacionar un producto con su similar de la otra base de datos. Lo único muy parecido que tuviesen una tabla con la otra sería la descripción, que por los formatos del sistema, tampoco es exactamente igual, pero si presenta mucha similitud en la mayoría de los casos.
ResponderBorrarGracias x la respuesta. Gary.
Jorge como comparo dos columnas pero con diferente cantidad de columnas, es decir en una columna tengo 450 y en la otra 2034, en excel 2003
ResponderBorrarLa cantidad de columnas es la misma, una, lo que difiere es la cantidad de filas.
ResponderBorrarEnel blog hay varias notas sobre comparación de listas. Fijate en la etiqueta "comparar listas".
hola jorge!
ResponderBorrarretomando las preguntas de Anónimo y Yashvé, y es que tengo una hoja (PRODUCTOS!)con una lista de productos (p.e. clavos de 2"; clavo para contreto 3"; tornillo de 3/4...)que tengo que asociar con una categoría, el criterio para asignar las categorías está en la hoja CATALOGO!, donde la columna A tiene una lista genérica de productos (p.e. clavo; tornillo) y la columna B la categoría que quiero relacionar.
quisiera saber entonces como puedo poner en PRODUCTOS!B, la categoría de CATALOGO!B.
la coincidencia no es exacta, pero el catálogo de productos es bastante general y enlista además los errores de dedo más comunes, como podría ser (viático, viatico, viáticos, viaticos).
cualquier sugerencia es bien recibida, porque hablamos de listados semanales de 5000 productos, así que con que me resuelva "algunos" productos me daría por bien servida...
(o bien alguna sugerencia para cumplir mi objetivo)
Hola Sandra,
ResponderBorrarpodrías crear una tabla de "coincidencias" donde a cada posibilidad se le asigne el valor único correcto (por ejemplo, en la columna A de la tabla aparecería "viaticos", "viatico", "viático", etc. y al en la columna B el valor correcto, "viáticos"). Basándose en esta tabla podrías crear una columna axuiliar con el valor correcto para registro. Luego podrías usar SUMAPRODUCTO con resultados exactos y sin mayores complicaciones.
hola Jorge,
ResponderBorrarMuiy parecida mi duda a ellos, si tengo un listado de proveedores con nombres similares por ejemplo:
Jorge Asociados
Jorge Asociados SRL
Como puedo hacer para qeu me busque esos parecidos? porque son más de 3000 líneas...
Saludos y gracias
Como ya puse en el pasado, no hay una forma exacta de hacerlo ya que nombres similares pueden pertenecer a proveedores distintos.
ResponderBorrar=(,
ResponderBorrarpero alguna forma que encuentre esos similares y evitarme revisar uno por uno las 3000 líneas, así nomas revisar 150 o las que aparezcan...
Se puede hacer usando un método recursivo. Por ejemplo, se podría programar una macro que revise todo los nombres que coinciden en los primeros 10 caracteres, agruparlos y revisarlos. Luego indentificarlos como "corregidos" y volver al proceso con coincidencias de 8 caracteres, y así sucesivamente.
ResponderBorrarHola me gustaría ver si me pueden ayudar.
ResponderBorrarEstor utilizando esta formula:
=INDICE($F$2:$F$11,COINCIDIR(FILA()-1,$I$2:$I$11,0))
Como en el ejemplo de arriba, sol que la lista ordenada solo me da los datos que están seguidos, cuando se brinca un espacio en blanco ya no me da los siguientes datos.
que estoy haciendo mal??
Tendrías que decir que hay en los rangos F2:F11 e I2:11
ResponderBorrarHola me gustaría ver si me pueden ayudar.
ResponderBorrarEstoy utilizando esta formula:
=CONTAR.SI(K2:AA2,">0")
pero el tema es que solo necesito que lo haga en las columnas intermedias
K2, M2, O2, Q2 etc...
te estaria muy agradecida
Si bien se puede hacer (la explicación excede el marco de un comentario), la pregunta es si la forma más eficiente de hacerlo es con CONTAR.SI.
ResponderBorrar