Evitando duplicados con Validación de Datos en Excel

jueves, febrero 02, 2006

La novedad de hoy es que finalmente he instalado el paquete castellano de Office. Así que inauguro la "nueva era" del blog con una nota sobre como evitar los duplicados en una lista.
Este tema está relacionado con los que traté en mis notas "Cómo construir una lista de valores únicos en Excel" y "Cómo comparar dos listas en Excel".
Tanto aquí como en mi blog en inglés he visto muchas entradas relacionadas con el tema de comparación de listas. Muchas veces lo que buscamos es descubrir si hay valores duplicados. Un enfoque distinto es preguntarse cómo evitar la duplicación de valores.
Supongamos que tenemos una hoja de Excel en la cual registramos los números de catálogo de los productos en un recuento de inventario. Nuestro objetivo es evitar que el mismo producto sea anotado en dos líneas. O sea, que en el momento de anotar un número de catálogo, Excel nos advierta en caso que ya exista una línea con este número.
Para lograr esto usamos la funcionalidad de Datos ---> Validación. Supongamos que tenemos esta hoja dónde anotaremos el recuento de inventario

Para descargar el archivo del ejemplo pulsar aqui.



Marcamos el rango de celdas en la columna A dónde anotaremos los número de catálogo y abrimos el menú Datos ---> Validación



Allí elegimos la opción "personalizada" y en la ventanilla escribimos la siguiente fórmula: =CONTAR.SI($A$4:$A$30,A4)=1




El rango $A$4:$A$30 incluye todas las celdas dónde anotaremos los números de catálogo. Es recomendable utilizar Nombres para señalar el rango. Cada vez que registremos un número de catálogo en la columna A, Excel contará cuántas veces aparece en el rango señalado. Si aparece más de una vez, el resultado de la función sera 2 o más y por lo tanto no se cumplirá la condición de la fórmula =CONTAR.SI($A$4:$A$30,A4)=1
Si intentamos registrar un valor ya existente recibiremos una advertencia



Este método tiene un inconveniente. Si copiamos el número de catálogo, por ejemplo de otra hoja, en lugar de escribirlo manualmente, Excel no reconocerá la duplicación y aceptará el registro.



Si te gustó esta nota anotala en del.icio.us


Technorati Tags: , , ,



Categorías: Funciones&Formulas_, Manejo de Datos_

56 comments:

Anónimo,  16 marzo, 2006 01:58  

intente con la formula pero me parece que al separar en el ejemplo
A4 CON UNA , ME ARROJA ERROR DE ESCRITURA.

Jorge L. Dunkelman 16 marzo, 2006 10:42  

He revisado el ejemplo y veo que funciona. Acabo de agregar un link en la nota para descargar el archivo del ejemplo. Si no logras encontrar la solucion puedes mandarme el archivo y vere donde esta el problema.

Anónimo,  27 marzo, 2006 01:52  

Hola Jorge:

Junto con saludarte y agradecerte por contestar mi inquietud te quiero señaler que después de varios intentos fallidos se me ocurrió hacer un cambio en la fórmula y me resultó.
tú fórmula dice ( la del ejemplo):
=contar.si($A$4:$A$30,A4)=1

COMO TE CONTÉ ME ARROJABA ERROR DE ESCRITURA Y DESPUÉS DE INTENTAR VARIOS CAMBIOS ESTE ES EL QUE ME RESULTÓ :
=contar.si($A$4:$A$30;A4)=1
TENGO EXCEL 98 NO SE SI SERÁ POR ESE MOTIVO.
Bueno Jorge estoy muy contento de que veas los comentarios ya que habla muy bien de tu seriedad y además esta fórmula en "cuestión" no la sabía y me ha ayudado muchísimo en mis funciones diarias.
Se despide afectuosamente

Jorge Cárdenas

Madrynense 18 octubre, 2006 03:23  

Hay un error en los enlaces del principio en donde dice "Cómo construir una lista de valores únicos en Excel" en realidad te direcciona a la entrada de "Como seleccionar celdas en blanco solamente", espero que puedas solucionarlo

Jorge L. Dunkelman 18 octubre, 2006 18:02  

Gracias por la observación. He arreglado el enlace.

Anónimo,  27 marzo, 2007 21:23  

hola, tu ejemplo me ha servido mucho, pero quisiera saber si se puede hacer lo siguiente: tengo una hoja con bastantes columnas (proveedor, factura, importe, fecha, etc) y unas 2000 filas, y a veces se duplica la factura, pero no me sirve la validacion para el número de factura porque hay proveedores que tienen el mismo numero de factura, por lo que me serviria mucho si se puede validar dos columnas (preveedor y nº factura)

Jorge L. Dunkelman 27 marzo, 2007 22:22  

Hola, una de las formas de validar en esos casos es creando una columna auxiliar, que contiene una combinación de los dos campos. En tu caso, si en columna A aparece el nombre del proveedor y en la B el numero de factura, en la columna C, celda C2, ponemos la fórmula =A2&B2
El resultado de la fórmula sería algo como Perez1234, que sería un valor único para efectuar el control de duplicados.
Espero que la explicación sea clara.

Anónimo,  28 marzo, 2007 07:27  

gracias por la rapidez, pero no me funciona porque realmente la celda c2 no es Perez1234 sino a2&b2, por lo que solo existiria coincidencia siexistiese otra celda a2&b2 no Perez1234

Jorge L. Dunkelman 28 marzo, 2007 18:16  

Estimado Anónimo, no logro entender tu última observación. Con "Perez1234" me refiero al resultado de la fórmula, no a la fórmula en si misma.
Te sugiero que me envíes el archivo por e-mail y te lo devolveré con la solución

Anónimo,  28 marzo, 2007 20:48  

¿a que e-mail?

Anónimo,  10 agosto, 2007 06:29  

YA PUDE INGRESAR LA FORMULA PARA QUE ME MUESTRE LA ADVERTENCIA DE UN ARCHIVO DUPLICADO, LA PREGUNTA AHORA ES, COMO PUEDO COMPARAR REGISTROS DUPLICADOS DE HOJAS DISTINTAS DE UN MISMO LIBRO, GRACIAS

Anónimo,  10 agosto, 2007 07:03  

HOLA JORGE, ME SIRVIO DE MUCHO LA INSTRUCCION PARA EVITAR REGISTROS DUPLICADOS EN UNA LISTA, SOLO QUE QUISIERA SABER COMO EVITAR REGISTROS DUPLICADOS EN HOJAS DISTINTAS DENTRO DE UN MISMO LIBRO ES DECIR QUE SI TENGO UNA LISTA EN UNAHOJA Y AL ABRIR OTRA HOJA CON DEL MISMO LIBRO SI INGRESO UN REGISTRO IGUAL AL DE LA HOJA ANTERIOR, COMO PUEDO DETECTARLO. TE AGRADESCO TU APOYO MI EMAIL ES digitalfrec@yahoo.com.mx GRACIAS NUEVAMENTE

Jorge L. Dunkelman 11 agosto, 2007 10:32  

Hay en el blog varias notas sobre comparación de registros y búsqueda de duplicados. Puede ver las botas paretando el enlace "Comparar Listas" en la Etiquetas (en la parte superior de la columna izquierda del blog).
Las técnicas se aplican a listas que en una misma o distintas hojas.

Jorge L. Dunkelman 11 agosto, 2007 10:46  
Este comentario ha sido eliminado por el autor.
Anónimo,  11 agosto, 2007 20:57  

QUE TAL JORGE.

PRIMERO QUE NADA TE AGRADESCO TU ATENCION A MI COMENTARIO ANTERIOR, SOLO QUE EL PROBLEMA ES QUE LA FORMULA =(CONTAR.SI($A$4:$A$30;A4)+CONTAR.SI(Hoja2!$A$4:$A$30;Hoja1!A4))=1 QUE ME SUGIERES NO ME FUNCIONA, YA QUE ME MANDA UN MENSAJE DE ERROR "Errror en la Formula", OJALA ME PUEDAS AYUDAR GRACIAS. digitalfrec@yahoo.com.mx

Jorge L. Dunkelman 12 agosto, 2007 20:49  

Hola, en mi apuro por contestarte pasé por alto que en validación de datos para referirse a rangos en otras hojas hay que usar nombres.
En breve irá una explicación detallada.

Anónimo,  13 agosto, 2007 23:06  

OK AMIGO JORGE ESPERO DICHA INFORMACION, SOLO COMO REPFEENCIA TE COMENTO QUE EN EL LIBRO QUE INGRESARE LOS DATOS SE TIENEN QUE COMPROVAR 3 HOJAS DIFERENTES EN LIBRO, EVITANDO REGISTROS DUPLICADOS DE LA LISTA ENTRE LAS TRES HOJAS. MUCHAS GRACIAS
digitalfrec@yahoo.com.mx

Anónimo,  29 agosto, 2007 07:39  

Gracias amigo, tu ejemplo de validacion de datos en una columna de excel, es excelente, en otra buscare tu ayuda

Riki 19 noviembre, 2007 21:43  

Hola Jorge, ante todo felicitarte por la página.
Llevo algunos dias quemandome las pestañas haciendo un planning de trabajadores y he usado de referencia tu página, pero hay algo que no consigo hacer... Necesito que en la validación de datos vayan desapareciendo los codigos de los trabajadores ya puestos en una fila para evitar duplicados en una misma fila. Es eso posible? Lo he intentado pero no lo consigo... Gracias por tu ayuda y enhorabuena por la page.

Jorge L. Dunkelman 19 noviembre, 2007 23:46  

Hola Riki

en esta nota, muestro como hacer eso. La explicación comienza en la segunda mitad de la nota.

Riki 20 noviembre, 2007 12:06  

me pierdo en la parte de rango dinámico, que no se como hacer, podrias pasarme el archivo de ejemplo que no me funciona el link? muchas gracias
Rikibeldaarrobahotmail.com

Anónimo,  11 febrero, 2009 20:40  

Jorge, tengo la siguiente duda, no se si se peude realizar: ¿Como validar una celda con una lista de otro archivo?

Que practico que es validar una lista con el "NOMBRE" de un rango de otra "hoja de calculo" que se encuentra en el mismo archivo.

Pero yo necesito utilizar un rango de una "hoja de calculo" que se encuentra en otro archivo Excel.

de antemano gracias.

Jorge L. Dunkelman 11 febrero, 2009 22:31  

Se puede hacer, pero usando un pequeño truco. En primer lugar el cuaderno que contiene la lista debe estar abierto.
Supongamos que el cuaderno con la lista se llama lista.xls y que hemos definido en él el rango con los valores bajo el nombre "nombres".
En el segundo cuaderno, donde queremos usar la validación, definimos un nombre de esta manera

=lista.xls!nombres

Como explico más arriba, el cuaderno lista.xls tiene que estar abierto para que la validación funcione.

mjabadia,  15 mayo, 2009 13:32  

Hola Jorge,
Tengo una problemilla con las listas de la Validación de datos y las fórmulas.
Tengo una lista de nombres de clientes y otra de codigos de clientes, hasta ahora, si se elige el nombre del cliente, hago que aparezca el codigo de cliente utilizando la función BUSCARV en la columna de codigo de cliente, lo que yo quiero conseguir es que eligiendo un codigo de cliente, me aparezca el nombre del cliente, cuando intento poner la fórmula en la columna de nombre de cliente, me sale un error de referencia circular, quiero tener las dos funcionalidades, ¿es posible? ¿como lo hago? Muchas gracias por tu ayuda anticipida.

Jorge L. Dunkelman 19 mayo, 2009 21:26  

Una referencia circular es eso, una referencia circular. Estás haciendo depende una fórmula de la otra. Tienes que tener dos listas desplegables independientes, de manera que una fórmula no se refiera a la otra.

El Duro 19 junio, 2009 01:17  

Jorge, estoy desde hace horas intentando armar una formula del tipo: =+SI(Y(1<=AD4;AD4<=19);"1 - uno";SI(Y(20<=AD4;AD4<=39);"2 - dos";SI(Y(40<=AD4;AD4<=59);"3 - tres";SI(Y(60<=AD4;AD4<=69);"4 - cuatro"))))

El tema es que necesito anidar 9 if, y el excel solo me permite 4. ¿Tenes idea como hacer?

Jorge L. Dunkelman 19 junio, 2009 14:07  

La función SI se puede anidar sólo 7 veces. Es el límite. Hay varias alternativas para lo que querés hacer. Tratá de usar ELEGIR como muestro en esta nota.
Otra alternativa para tu caso es crear una tabla auxiliar con los rangos de valores y luego usar BUSCARV para obtener el valor deseado.

fofi 10 julio, 2009 21:19  

Estimado Jorge: Agradezco la prontitud de tu respuesta. He realizado el ejemplo tal como lo sugieres en el blog, y de hecho funciona. No obstante no se ajusta completamente a mi necesidad, dado que mi intención, quizás muy rebuscada, es que al introducir el numero de factura en la celda ese valor se descuente (se borre) automáticamente de la lista sin necesidad de escribir “pagada” en la lista Estado (según tu ejemplo) dado que esto ultimo significa un trabajo adicional, que es lo que trato de evitar... Te saluda cordialmente, Hector.-

Jorge L. Dunkelman 11 julio, 2009 14:03  

Héctor, tendrías que haber dejado el comentario en la nota correspondiente.
En cuanto a tu consulta, cómo le indacamos a Excel que la factura ha sido pagada. Siempre tenemos que hacer algo para que la aplicación sepa que una factura a pasado de un estado al otro. Las capacidades telepáticas de Excel son muy limitadas :)

efda007 10 noviembre, 2009 04:03  

hola amigo, quería hacerte una pregunta, como puedo hacer que en una cela escriba algo y en un cuadro me salga la imagen de la palabra escrita por ejemplo q escriba en la celda A2 "gato" y en un cuadro aparezca la imagen de un gato; me podrías dar los pasos por que no encuentro como, por favor indicame donde estan los botones y todo, paso por paso por favor por que es la primera vez que uso esto en excel, gracias de antemano, efda007@hotmail.com

Jorge L. Dunkelman 10 noviembre, 2009 20:45  

Hola
busca en mi blog las notas sobre catálogos de imágenes en Excel. Ahí encontrarás la pista.
Y me permito una observación: si quieres indicaciones paso por paso tienes que ir a un curso o pagar a un instructor. No puedes esperar recibir instrucción gratuita a cuenta del tiempo libre del autor de un blog (o del cualquier otra persona).

Anónimo,  04 diciembre, 2009 07:57  

Hola Quisiera hacerte una pregunta, tengo dos libros en excel de oficce 2007 y necesito saber si es posible compararlos y que solo arroje los que no se repiten todo eso en una hoja nueva, y que me des alguna pista de como comenzar alguna formula para hacerlo, tengo el problema en le trabajo, de antemano muchas gracias.

Juan Pablo

Jorge L. Dunkelman 04 diciembre, 2009 11:16  

¿Qué es lo que querés comparar (hojas, tablas, todo el libro...)?

anarve 15 febrero, 2010 23:30  

Jorge ,aunque parezca tópico he de felicitarte por tu blog es muy bueno ,he leído muchas veces tus fórmulas y me han resuelto varios problemas ,pero no doy con la solución al siguiente.
Trabajo en un almacén donde disponemos de entradas y salidas de B.bags (sacos de 300kg),la cuestión es que al hacer las entradas de forma manual a veces repetimos el número de Big-bag y nos crea problemas a la hora de buscarlos en su sitio teorico,estoy intentando buscar la fórmula para que no se repitan en ninguna fila o columna el número y no doy con la fórmula ,podrías ayudarme?.
Y ya seria rizar el rizo si pudiera hacerlo con dos hojas diferentes............

Jorge L. Dunkelman 16 febrero, 2010 06:39  

Anarve,

ponte en contacto conmigo por mail.

Anónimo,  20 abril, 2012 23:42  

Jorge, antes que nada muchas, muchas gracias por tantas soluciones que nos brindas con nuestro amigo Excel.
Te quiero hacer la siguiente consulta, tengo una columna donde he puesto una lista desplegable para que se puedan introducir solo los datos de la lista, pero también quiero que los datos no se puedan repetir, hay alguna manera de poner las 2 validaciones en la misma columna?
Saludos

Jorge L. Dunkelman 22 abril, 2012 06:41  

No, no se pueden poner dos validaciones en el mismo rango, pero se puede usar la técnica que muestro en esta nota.

Anónimo,  23 abril, 2012 16:51  

Ok, muchas gracias Jorge

Anónimo,  06 julio, 2012 21:07  

Saludos amigos y espero me puedan ayudar,tengo una base de datos en excel 2010,contiene numero de cedula,nombres y cargos yo introduci la formula para que me diga cuando una cedula ya exista,el caso es que voy a ingresar un numero nuevo,y me dice ya existe y es la formula del ejemplo,gracias si pueden darme una mano,mi nombre es hernan.

Jorge L. Dunkelman 06 julio, 2012 21:21  

Hernán,

o el número ya existe o hay un error en la fórmula de la validación de datos. Si quieres puedes enviarme el archivo.

molecula 08 octubre, 2012 12:21  

Hola Jorge.
Acabo de llegar a tu blog, me está ayudando mucho. Sin embargo, no puedo descargar el archivo de ejemplo, aparentemente ya no existe. ¿Hay alguna manera de que me lo envíes?
Mil gracias :)

Jorge L. Dunkelman 09 octubre, 2012 13:37  

El archivo estaba en un sitio que se cerró, lo lamento.

Anónimo,  27 mayo, 2013 23:31  

Estimados, cuento con una boleta de registros de libros donde el ingreso es el nombre de la obra, ella se encuentra en una lista, luego me trae sus datos como código precio etc, esto solo debe ser permitido una sola vez, como hago para validar que cuando vuelva a ingresar un nuevo código en la boleta me indique su duplicidad

Jorge Dunkelman 28 mayo, 2013 17:05  

Bien, es lo que explica la nota. ¿Cuál es el problema?

Octavio Serrano 09 octubre, 2013 22:37  

Hola Jorge, buenas tardes; Mi problema consiste en que tengo una columna con una serie de datos que se repiten, mi necesidad consiste en al final de la columna hacer una suma y que la suma solo considere una vez los datos duplicados, es decir si tengo 30 veces $100 y otras 40 veces $200 la suma me de como resultado $100+$200=$300 Espero haberme explicado bien P.D. no puedo ordenar y quitar los duplicados porque están ligados a otras columnas que si voy a utilizar la base es de 10,000 filas y 20 columnas

Jorge Dunkelman 13 octubre, 2013 19:53  

Octavio, evidentemente la mejor solución sería revisar el diseño de tu modelo. Mientras tanto lo que puedes hacer es crear una columna auxiliar con una fórmula como ésta (supongamos en la columna B):

=A2/CONTAR.SI($A$2:$A$10000,A2)

donde la columna A tiene los valores repetidos. Luego =SUMA(B2:B10000) te dará el resultado esperado.

Anónimo,  27 noviembre, 2013 00:13  

Hola Jorge, he logrado evitar duplicar valores en cierto rango de celdas, pero lo que no consigo es evitar que ingrese solo ciertos valores, ejemplo:

Crear un rango que acepte solo números del 1 al 10 y que al mismo tiempo evite que se dupliquen estos números.

No he logrado que me valide 2 condiciones a la vez.

Agradezco tu atención.

Saludos,

Jorge Dunkelman 27 noviembre, 2013 07:20  

Tienes que usar la opción Personalizada con una fórmula con la función Y. Como se trata de un tema que interesará a varios de mis lectores, publicaré una nota en breve.

Anónimo,  27 noviembre, 2013 08:05  

Gracias, esperaré la nota.

Saludos. :)

Nixon Eli Hernández Vargas 28 marzo, 2016 19:43  

necesito ayuda:
Mi problema es que necesito hacer una LISTA DE VALIDACIÓN DE DATOS (DESPEGABLE); tengo muchos nombres los cuales se repiten y quiero evitar eso. GRACIAS

Jorge Dunkelman 28 marzo, 2016 22:13  

Hay varias formas de hacerlo pero todas se basan en crear una lista de valores únicos en algún rango y referir la lista de la validación de datos a ese rango. La forma más fácil es con Filtro Avanzado.

Mayquelis Castro 20 junio, 2016 02:34  

Evitar que se pueda ingresar una fecha en la cual ya se llevara a cabo un evento. con validacion de datos

Jorge Dunkelman 20 junio, 2016 07:41  

En validación de datos puedes usar la opción Fechas (no es igual, entre fechas, etc.)| también la opción Número Entero (las fechas son números enteros, como ya hemos explicadoo en este blog).
Puedes ingresar el valor de la validación como constante o mejor aún, crear un referencia a la celda que contieneel valor.

Criztian Rivera 27 julio, 2016 23:27  

hola buenas tardes, quisiera saber si esto mismo se puede hacer pero evitando que se duplique en un libro completo???

Jorge Dunkelman 30 julio, 2016 18:11  

Se puede hacer, pero significa aplicar la validación de datos a toda las hojas y, supongo, todas las celdas del cuaderno. Esti puede ser muy costoso en términos de funcionamiento del cuaderno.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP