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: CONTAR.SI, COUNTIF, Evitar Duplicados, Validación de Datos en Excel
Categorías: Funciones&Formulas_, Manejo de Datos_
intente con la formula pero me parece que al separar en el ejemplo
ResponderBorrarA4 CON UNA , ME ARROJA ERROR DE ESCRITURA.
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.
ResponderBorrarHola Jorge:
ResponderBorrarJunto 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
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
ResponderBorrarGracias por la observación. He arreglado el enlace.
ResponderBorrarhola, 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)
ResponderBorrarHola, 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
ResponderBorrarEl 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.
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
ResponderBorrarEstimado 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.
ResponderBorrarTe sugiero que me envíes el archivo por e-mail y te lo devolveré con la solución
¿a que e-mail?
ResponderBorrardunk@maaganm.co.il
ResponderBorrarYA 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
ResponderBorrarHOLA 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
ResponderBorrarHay 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).
ResponderBorrarLas técnicas se aplican a listas que en una misma o distintas hojas.
Este comentario ha sido eliminado por el autor.
ResponderBorrarQUE TAL JORGE.
ResponderBorrarPRIMERO 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
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.
ResponderBorrarEn breve irá una explicación detallada.
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
ResponderBorrardigitalfrec@yahoo.com.mx
Gracias amigo, tu ejemplo de validacion de datos en una columna de excel, es excelente, en otra buscare tu ayuda
ResponderBorrarHola Jorge, ante todo felicitarte por la página.
ResponderBorrarLlevo 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.
Hola Riki
ResponderBorraren esta nota, muestro como hacer eso. La explicación comienza en la segunda mitad de la nota.
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
ResponderBorrarRikibeldaarrobahotmail.com
Jorge, tengo la siguiente duda, no se si se peude realizar: ¿Como validar una celda con una lista de otro archivo?
ResponderBorrarQue 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.
Se puede hacer, pero usando un pequeño truco. En primer lugar el cuaderno que contiene la lista debe estar abierto.
ResponderBorrarSupongamos 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.
Hola Jorge,
ResponderBorrarTengo 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.
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.
ResponderBorrarJorge, 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"))))
ResponderBorrarEl tema es que necesito anidar 9 if, y el excel solo me permite 4. ¿Tenes idea como hacer?
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.
ResponderBorrarOtra alternativa para tu caso es crear una tabla auxiliar con los rangos de valores y luego usar BUSCARV para obtener el valor deseado.
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.-
ResponderBorrarHéctor, tendrías que haber dejado el comentario en la nota correspondiente.
ResponderBorrarEn 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 :)
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
ResponderBorrarHola
ResponderBorrarbusca 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).
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.
ResponderBorrarJuan Pablo
¿Qué es lo que querés comparar (hojas, tablas, todo el libro...)?
ResponderBorrarJorge ,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.
ResponderBorrarTrabajo 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............
Anarve,
ResponderBorrarponte en contacto conmigo por mail.
Jorge, antes que nada muchas, muchas gracias por tantas soluciones que nos brindas con nuestro amigo Excel.
ResponderBorrarTe 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
No, no se pueden poner dos validaciones en el mismo rango, pero se puede usar la técnica que muestro en esta nota.
ResponderBorrarOk, muchas gracias Jorge
ResponderBorrarSaludos 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.
ResponderBorrarHernán,
ResponderBorraro 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.
Hola Jorge.
ResponderBorrarAcabo 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 :)
El archivo estaba en un sitio que se cerró, lo lamento.
ResponderBorrarEstimados, 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
ResponderBorrarBien, es lo que explica la nota. ¿Cuál es el problema?
ResponderBorrarHola 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
ResponderBorrarOctavio, 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):
ResponderBorrar=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.
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:
ResponderBorrarCrear 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,
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.
ResponderBorrarGracias, esperaré la nota.
ResponderBorrarSaludos. :)
necesito ayuda:
ResponderBorrarMi 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
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.
ResponderBorrarEvitar que se pueda ingresar una fecha en la cual ya se llevara a cabo un evento. con validacion de datos
ResponderBorrarEn 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).
ResponderBorrarPuedes ingresar el valor de la validación como constante o mejor aún, crear un referencia a la celda que contieneel valor.
hola buenas tardes, quisiera saber si esto mismo se puede hacer pero evitando que se duplique en un libro completo???
ResponderBorrarSe 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.
ResponderBorrar