Usando fórmulas similares podemos también extraer elementos repetidos o únicos de dos listas.
En este caso supondremos dos listas de 9 nombres
Hemos creado dos nombres para contener los rango de las listas
lista1 = A2:A10
lista2 = B2:B10
Para extraer los nombres que repetidos, aquellos que aparecen en ambas listas usamos esta fórmula matricial “multicelular”
={SI(CONTAR.SI(lista1,lista2)>0,lista2,"")}
Recordemos que para usar esta fórmulas seleccionamos previamente el rango (en nuestro caso C2:C10) y luego introducimos la fórmula apretando simultáneamente Ctrl+Mayúsculas+Enter.
Por supuesto queremos que la lista de nombres repetidos aparezca ordenado, para lo cual usamos la función INDICE en forma matricial de la siguiente manera
=INDICE(lista1,K.ESIMO.MAYOR(SI(CONTAR.SI(lista2,lista1)>0,FILA()-1,""),FILA()-1))
Al rango D2:D10 aplicamos formato condicional para ocultar los resultados #NUM! que aparecen.
La fórmula funciona de la siguiente manera:
+ la expresión SI(CONTAR.SI(lista2,lista1)>0,FILA()-1,"") genera un vector de números o blancos. Cuando un nombre de la lista 1 aparece en la lista 2, la fórmula produce un número equivalente al número de fila menos 1; en caso contrario produce un valor en blanco.
+ Este vector los ordenamos con la función K.ESIMO.MAYOR
K.ESIMO.MAYOR(SI(CONTAR.SI(lista2,lista1)>0,FILA()-1,""),FILA()-1)
+ Este vector ordenado nos sirve como argumento en la función INDICE para extraer los elementos de las filas correspondientes en la lista 1.
Para extraer elementos únicos, debemos definir previamente el orden de comparación. Es decir, nombres de la lista 1 que no aparecen en la lista 2 o nombres de la lista 2 que no aparecen en la lista 1.
Para extraer los nombres de la lista 1 que no aparecen en la lista 2 usamos la fórmula matricial
=INDICE(lista1,K.ESIMO.MAYOR(SI(CONTAR.SI(lista2,lista1)=0,FILA()-1,""),FILA()-1))
En las fórmulas que hemos mostrado podemos usar también K.ESIMO.MENOR para cambiar el orden de aparición de los nombres:
=INDICE(lista1,K.ESIMO.MENOR(SI(CONTAR.SI(lista2,lista1)=0,FILA()-1,""),FILA()-1))
Technorati Tags: MS Excel
Hola Jorge,
ResponderBorrarPrimeramente felicitarte por tu blog, es de gran ayuda! Enhorabuena!
Necesito sacar elementos únicos de diferentes listas que están en diferentes hojas, de un mismo documento, a una hoja nueva en el mismo documento. Y no se como puedo hacerlo.
A ver si tengo suerte y me puedes echar una mano.
Gracias y un saludo!
Verónica
Con la misma técnica que muestro aquí. Solamente tenés que definir el nombre de cada lista en la hoja correspondiente. En la entrada ambas listas están en la misma hoja, pero también pueden estar en hojas distintas.
ResponderBorrarHola Jorge,
ResponderBorrarEstuve leyendo el ejemplo último, lo copie a un Excel y aplique las formulas pero no me da la columna "Únicos Lista 1", es decir, no me aparecen los nombres (Carlos,Juana, Rosario, Sara, Luis) sino que me aparece #¡VALOR! #¡NUM!
#¡NUM! #¡NUM! #¡VALOR! #¡VALOR!#¡NUM!#¡VALOR!#¡NUM!
¿Qué estoy haciendo mal?
Gracias!!
Muy bueno el blog!
Juan T
Parece ser que hay un problema con alguna de las referencias en la fórmula. Tenés que revisar las fórmulas. También podés mandarme el archivo para que lo revise.
ResponderBorrarHOLA, esta formula tambien funciona con valores numericos??
ResponderBorrarBuenos días, saludos desde Colombia, tengo una consulta: Tengo un libro en excel 2010, el libro tiene varias hojas de cálculo, en cada hoja de cálculo hay por lo menos dos columnas, una con un nombre, otra con el número de cédula de esa persona (cédula es como el dni de ustedes), a su vez, cada hoja tiene un nombre, la idea es encontrar las cédulas duplicadas, escribirlas en una columna y que al lado escriba el nombre de las hojas donde están, cual es el objetivo de esto, cada hoja representa a un candidato a un cargo público, y los nombres en las hojas son las personas que dicen que votarán por ellos, a veces un ciudadano lo promete a varios candidatos que votarán por el y quiero saber como encontrar esas personas. Gracias por la atención prestada
ResponderBorrarPodrías ahcerlo con unpoco de trabajo manual:
ResponderBorrar1 - copias todas las listas a una única hoja agregando un campo que identifique la hoja (el candidato)
2 - agregas una segunda columna auxiliar para identificar si una cédula aparece más de una vez. Digamos que las cédulas están en la columna A y la columna auxiliar en D, sería =CONTAR.SI($A$1:$A$1000,A1). Si queremos ver sólo los duplicados, no la primer ocurrencia, usamos CONTAR.SI($A$1:A1,A1)
3 - ahora podés filtrar usando como criterio al columna D>1.
Me pica la curiosidad, ¿es legal usar el número de cédula en este tipo de estudios?