jueves, 13 de diciembre de 2012

Evitar Duplicados en Varias Columnas


"Tengo una hoja en la que cada 4 columnas tengo que introducir los códigos de recibos (en total en tres columnas). Estos códigos no pueden repetirse y, en consecuencia, necesito que no permita introducir los códigos que ya se hayan introducido previamente".

Este problema es una derivada un poco más complicada del que ya traté en su día en el artículo "Evitar Datos Repetidos con Validación de Datos Personalizada". En dicho post veíamos como evitar introducir dos veces el mismo código pero dentro de una misma columna. En este nuevo caso, además de comprobar cada columna individualmente, debemos cotejar las distintas columnas entre si para cerciorarnos de que no estamos introduciendo datos repetidos.

 Partimos del siguiente ejemplo:



Y lo que queremos conseguir es que cuando introduzcamos un código que ya se haya utilizado, como se ve en la siguiente imagen, nos aparezca un mensaje de error que no nos permita utilizar dicho código:


Vamos a necesitar generar una matriz de comprobaciones para resolver este problema, matriz que situaré en el rango M2:R10. Antes de empezar a formular vamos a crear tres nombres. Para ello seleccionamos el rango discontinuo A1:A10 E1:E10 y I1:I10 y en la ficha Fórmulas pinchamos en Crear desde la selección. En la ventana que se abre seleccionamos Crear nombres a partir de los valores de la: Fila superior. De esta manera hemos creado los nombres Codigo1, Codigo2 y Codigo3 que se corresponden a cada una de las columnas de la entrada de datos.

Vamos a la celda M2 y escribimos la fórmula =CONTAR.SI(codigo1;A2)  y copiamos la fórmula hasta M10. De esta manera estamos contando cuántas veces está cada código introducido en la primera columna.

Hacemos lo mismo para el resto. Nos situamos en N2 y escribimos: =CONTAR.SI(codigo2;E2)  y copiamos hasta N10. Finalmente nos situamos en O2 y escribimos: =CONTAR.SI(codigo3;I2)  y copiamos hasta O10.

Procedemos ahora a comprobar si hay duplicados de unas columnas en otras. Para ello nos situamos en la celda P2 y escribimos la fórmula: =SI(ESERROR(BUSCARV(A2;codigo2;1;));0;1)   y la copiamos hasta la celda P10. Esta fórmula verifica si algún elemento de la primera columna se encuentra en la segunda columna. Si no se encuentra generará un error tipo N/A que lo convertimos en valor cero con el uso del condicional de la fórmula. En caso contrario, es decir, si está repetido, entonces el condicional le otorga un valor 1, como se puede ver al final de la fórmula.
Hacemos ahora la misma comprobación pero en la tercera columna. Nos situamos en Q2 y escribimos: =SI(ESERROR(BUSCARV(A2;codigo3;1;));0;1)   Finalmente comprobamos si hay valores repetidos de la columna 2 en la columna 3 con la siguiente fórmula en la celda R2: =SI(ESERROR(BUSCARV(E2;codigo3;1;));0;1)

El resultado obtenido es la siguiente matriz de comprobación:


Sólo nos queda analizar que resultados podemos permitir en esta matriz que garanticen que no se ha introducido el mismo código 2 veces:
En el caso de las columnas M, N, O los resultados permitidos serán cero o uno (cero en el caso de que todavía no hayamos introducido un código y uno en el caso de que lo hayamos introducido y no esté repetido (en cuyo caso sumaría 2).
En el caso de las columnas P, Q, R  el resultado permitido será cero, ya que si aparece un valor uno, por ejemplo, significará que hay una repetición de código de una columna en otra.

Seleccionamos el rango discontinuo A1:A10 E1:E10 y I1:I10 y vamos a la ficha Datos y pinchamos en Validación e datos. Dentro de la ventana que se abre elegimos Permitir Personalizada. En el recuadro Fórmula escribimos la fórmula que cumple con las condiciones analizadas, a saber:

=SUMAR.SI(M2:M10;">1")+SUMAR.SI(N2:N10;">1")+SUMAR.SI(O2:O10;">1")+
SUMA(P2:P10;Q2:Q10;R2:R10)=0

Y problema resuelto!

6 comentarios:

  1. =SI(Y(CONTAR.SI(B:B;B1)=1;CONTAR.SI(C:C;B1)=0;CONTAR.SI(H:H;B1)=0);VERDADERO) -- Con esta fórmula en validación datos se consigue lo mismo, adaptándola a cada rango.

    ResponderEliminar
  2. tengo un problema muy parecido al que tratan de resolver en este ejemplo, solo que no se como resolverlo
    tengo diversos datos en diversas columnas, son diferentes todos entre si, pero quiero que cuando se dupliquen me impida continuar

    un ejemplo sería:
    refresco cocacola regular retornable 1lt
    refresco cocacola light retornable 1lt
    agua ciel purificada desechable 1.5lt

    quiero que cuando por error meta nuevamente coca regular en lugar de coca light
    quiero que cumpla las condiciones de repetir los datos de varias columnas

    ResponderEliminar
    Respuestas
    1. Buenos días Edd. Lo siento pero no consigo ver la diferencia del problema que planteas con el que se resuelve en este post.

      Eliminar
  3. Hola Buenos días yo tengo un caso similar sin poder resolverlo aún,

    Necesito validar todos los campos de una fila, y que me marque un error si ingreso un registro duplicado, Ejemplo.

    Nombre Apellido Direccion Telefono
    JUAN PEREZ I. ZARAGOZA 55555555
    NOE MARTINEZ PROGRESO 555555
    JUAN PEREZ I. ZARAGOZA 55555555

    El registro #3 es un valor duplicado con el 1, y quisiera ver la manera de que Excel envíe un mensaje cuando ingrese un valor duplicado "Usted esta intentando ingresar un valor duplicado", si algun campo de la fila es diferente, el registro ya no seria duplicado y no es necesario mandar el mensaje de error.

    Nota: No necesito que me valide solo una columna con valores repetidos, si no filas completas entre varias filas. Ej. Si yo copio y pego una fila completa me mande el error, Ej2. Si yo transcribo la fila completa de igual manera me marque un eror. Y si yo cambio el valor de un campo de la fila no envíe nada.

    Espero alguien pueda ayudarme creo que es algo complejo, pero creo que puede tener una solución.

    Gracias de antemano.

    Saludos.

    ResponderEliminar
    Respuestas
    1. Perdón pero esto es lo que se resuelve precisamente en este post. A lo mejor es que no he entendido bien la diferencia...

      Eliminar