sábado, 11 de abril de 2009

Evitar datos repetidos con Validación Datos Personalizado

Supongamos que tenemos una tabla en la que vamos introduciendo los datos relativos a las ventas producidas en cada delegación de nuestra compañía semanalmente. La primera columna hace referencia al código de la operación. Dicho código es único, es decir, no se puede repetir, y representa la delegación donde se ha producido la venta (dos primeros dígitos) y el número de factura (tres últimos dígitos). Queremos que si por error intentamos introducir un código repetido la hoja no nos lo permita y nos avise con un mensaje. También queremos que en la tercera columna de la tabla nos indique, tras introducir el código, a qué delegación pertenece la venta en cuestión. Para todas estas labores utilizaremos Formato Condicional, Validación de Datos y las funciones CONTAR.SI, SI, O, BUSCARV y IZQUIERDA.
Lo primero que buscamos es lo que puede ver en la siguiente imagen: que no nos permita introducir datos repetidos en el campo Código.

1. Seleccionamos el rango donde vayamos a introducir los datos, en nuestro caso A4:A20 y vamos al menú Datos/Validación de datos.

2. Seleccionamos Permitir/Personalizada y en el recuadro Fórmula escribimos:
=CONTAR.SI($A$4:$A$20;A4)=1
3. Antes de aceptar pulsamos la pestaña de mensaje de error y personalizamos el mensaje que queramos que aparezca si intentamos duplicar un registro. Una vez hecho pulsamos Aceptar.
Gracias a esta sencilla fórmula cada registro (A4, A5, A6...) es comparado con el rango $A$4:$A$20. Como establecemos la condición de que al contar cada registro debe ser igual a 1 si intentamos volver a introducir el mismo registro nos saldrá el mensaje de error que le hayamos indicado.

Por otro lado nos interesa que en C4 y siguientes filas nos indique automáticamente en qué delegación se ha realizado la venta (utilizaremos esta información más adelante para generar un informe resumen por delegación). Para ello elaboramos la tabla que se muestra en la figura en el rango E3:F9.

4. Seleccionamos el rango E4:F9 y le damos el nombre de ciudades.

5. Para resolver este problema además de la función BUSCARV necesitaremos otra función que nos permita extraer del código los dos primeros dígitos. Dicha función será IZQUIERDA. Si escribiéramos =IZQUIERDA(A4;2) obtendríamos los dos primeros caracteres de la celda A4, que es precisamente lo que necesitamos para realizar después la búsqueda en nuestra tabla de ciudades. Así pues, nos situamos en la celda C4 y escribimos la siguiente fórmula:

=BUSCARV(IZQUIERDA(A4;2);Ciudades;2;Falso) De esta manera obtendremos la ciudad a la que hace referencia el código. Si queremos mejorar nuestra fórmula para que sólo aparezca la ciudad en caso de que introduzcamos un código en la columna A y un importe en la columna B deberemos añadir dos funciones lógicas, a saber, SI y O:

=SI(O(A4="";B4="");"";BUSCARV(IZQUIERDA(A4;2);Ciudades;2;Falso))

27 comentarios:

  1. Excelente Blog. EL problema surge aquí cuando se quiere pegar el dato ctrl+v, entonces la validación no funciona

    ResponderEliminar
  2. Apreciable Kiko
    Espero me puedas ayudar: en una columna tengo que insertar un dato de una lista que esta en otra hoja, ejemplo en A tengo que introducir "001/001/2010", "001/004/2010",003/004/2010", etc, esto lo hice con validación por lista (=NCONTRATO que es mi lista de datos), pero al mismo tiempo quiero impedir que se pueda capturar un dato dos veces, ¿como puedo lograrlo?

    ResponderEliminar
  3. Alexo, lo que entiendo que necesitas es lo que se explica en este post por lo que me me temo que no entiendo bien la pregunta.

    ResponderEliminar
  4. Hola Alexo.

    He vuelto a leer tu pregunta (esto me pasa por contestar recién levantado...) y creo que ya he entendido lo que necesitas. La solución a lo que buscas la encontrarás en el siguiente artículo del blog de Jorge Dunkelman:

    http://jldexcelsp.blogspot.com/2009/10/listas-desplegables-en-excel-con-ajuste.html

    ResponderEliminar
  5. Jorge Dunkelman otra persona tan maravillosa como tú, gracias Kiko, lo reviso y comento.

    ResponderEliminar
  6. HOLA HOLA TENGO UNA PREGUNTA. TRABAJO EN LA INDUSTRIA DEL CALZADO, PARA AHORRAR TIEMPO HE DISEÑADO UNA PAGINA EN EXCEL DONDE COLOCO HACIA ABAJO QUIEN CORTA EL MATERIAL, OTRA COLUMNA CON CUANTOS PARES HA CORTADO, Y AHI SE ME DA EL CASO QUE EXISTEN 5 CORTADORES A LOS QUE SE LE DAN LAS TAREAS EN EL DIA. MI PREGUNTA ES COMO HAGO QUE EXCEL ME BUSQUE EL NOMBRE DEL CORTADOR Y ME SUME TODAS LAS COLUMNAS DONDE EL APARECE Y ME DE EL TOTAL DIARIO DE CORTES????? ESTARE MUY AGRADECIDA.

    ResponderEliminar
  7. Necesito ver el ejemplo para ver como tienes dispuesta la información. Por favor mándame el archivo a kiko.rial@gmail.com

    ResponderEliminar
  8. me funciono muy bien pero como hago para que al pegar una lista de datos me funcione la validacion y no pegue datos duplicados?

    ResponderEliminar
  9. Hola!

    Al intentar aplicar tu formula en mi archivo me marca como error de formula, ya la copie y pegue directamente solo cambiando valores, ya recree tu ejemplo en una hoja de excel y aun así no puede aplicar la formula, no se si se deba a que tengo el excel 2010, espero me puedas ayudar gracias.

    ResponderEliminar
  10. hola que tal? queria hacerte una pregunta sobre este tema. ¿y si no disponemos en el registro de ningun campo clave unico y lo que necesitamos es que no admita el registro si todos sus campos coinciden con el de otro registro?
    Te agradeceria mucho si me pudieses dar la solucion. Muchas gracias.

    ResponderEliminar
  11. Hola Jose. Me temo que no entiendo bien lo que necesitas ¿me podrías explicar con un ejemplo lo que quieres resolver?

    ResponderEliminar
  12. AL utilizar la formula en validación, no puedo insertar ningún dato. No entiendo contra qué compara entonces.
    Solamente me sale el mensage de error que configuré y no puedo colocar dato alguno.... qué hacer?


    Gracias!

    ResponderEliminar
    Respuestas
    1. Hola Elías. En principio, el único motivo que se me ocurre es que en la fórmula de validación de datos hayas puesto =CONTAR.SI($A$4:$A$20;A4)=0
      en vez de
      =CONTAR.SI($A$4:$A$20;A4)=1
      Compruébalo y en caso contrario dime qué fórmula exacta estás utilizando. Gracias

      Eliminar
  13. holaaaa me gusta y me fusiona muy bien solamente que cuando quiero utilizarla para as de 10 números que me los valide y no acepte números duplicados no pasa nada como que no fusionara que sera lo que sucede con 10 mil valores me funciona muy bien la vdd

    ResponderEliminar
    Respuestas
    1. Gracias Jery. La verdad que no entiendo muy bien cuál es el problema que te sucede porque la solución debe funcionar igualmente para 10 o para 10.000 valores. Si me das más información te intento ayudar. Un saludo

      Eliminar
  14. Gracias por la informacion, ami si me funciono... saludos

    ResponderEliminar
  15. USO EL COMANDO COPIAR Y LUGO, INSERTAR CELDAS COPIADAS, COMO LE HAGO PARA EVITAR DATOS CUPLICADOS EN LA BASE DE DATOS,
    YA QUE USANDO EL COMANDO INSERTAR CELDAS COPIADAS ME INSERTA HACIA ABAJO LOS DATOS,

    ResponderEliminar
    Respuestas
    1. Necesito que me especifiques más la pregunta porque no entiendo bien el problema. lo siento.

      Eliminar
  16. BUENAS TARDES, NO SE COMO UTILIZAR LA FORMULA, LA NECESITO PARA INVENTARIO PERO LAS REFERENCIAS SE ENCUENTRAN SOBRE LA COLUMNA B, NO SE COMO HACER QUE FUNCIONE

    ResponderEliminar
    Respuestas
    1. Pues en principio bastaría con cambiar las referencias a la columna B, es decir, y siguiendo con el ejemplo el post: =CONTAR.SI($B$4:$B$20;B4)=1

      Eliminar
  17. Buenas tardes Kiko Rial, la cuestión es la siguiente: Si tengo la Columna A con valores y quiero Rellenar la columna B pero que no permita introducir valores que ya están en la columna A, como podría hacerle. Soy nuevo en Excel y apreciaría su ayuda. Saludos y gracias.

    ResponderEliminar
  18. Te contesto en el post de mañana. Un saludo

    ResponderEliminar
  19. Buenas tardes Kiko Rial, tengo un problema con excel y aver si puedes ayudarme. Te cuento, tengo una empresa y me he creado una hoja excel para tener controladas las facturas que voy pagando y no duplicar su pago. Las celdas que tengo son (Nº factura, nombre empresa, fecha, base imponible e iva) lo que quiero hacer es validar para que excel me avise si ya existe un numero de factura para una empresa en concreto. nose si me explico. Muchas gracias. Un saludo

    ResponderEliminar
    Respuestas
    1. Hola. Tienes una solución muy similar (cambiando los nombres de los campos) en este post:
      http://lareboticadeexcel.blogspot.com.es/2014/04/resaltar-duplicados-concatenados.html
      Espero te aclare el problema. Un saludo

      Eliminar
  20. como solucionar no repetir datos si solo pego el código

    ResponderEliminar
    Respuestas
    1. Si pegas los códigos entonces no funciona la validación de datos (ya que valida entrada de datos pero no copiado y pegado). En tal caso te sugiero que añadas en una columna adicional una fórmula de aviso del tipo:
      =SI(CONTAR.SI($A$4:A4;A4)>1;"Código Repetido";"")

      Eliminar