Alerta por dato duplicado

[Mohamad Marrawi Marrawi]

Buenos días,

Para que el sistema me lance una ventana con un mensaje de error informando que ya existe el mismo valor en la columna B, selecciono primero un rango desde la celda 2 hasta la celda 200 y luego me voy a la pestaña Datos, y en Validación de datos, pongo lo siguiente: 

=CONTAR.SI($B$2:$B$200;B2)=1

¿Habría alguna manera de no tener que indicar un rango, para que no tenga porque ser hasta la celda 200? 

Luego, querría repetir lo mismo para las columnas E y H.

 

 

[DiegoPC]

Hola

Lo que yo haría es crear una tabla

Luego crear un nombre para la columna (o columnas) que me interesa(n)

Y en la validación uso ese nombre

 

La tabla crece automáticamente y el nombre se adecúa de manera dinámica.

[Mohamad Marrawi Marrawi]

Hola DiegoPC,

Gracias por tu aporte.

¿Se puede crear una sola tabla para todas las columnas que interesan, o tiene que ser una tabla para cada columna?

Luego, en la fórmula de la validación, dentro de los paréntesis, ¿qué habría que poner en el segundo argumento?

[Sergio]

Mohamad, veo que en tu exposición usas validación de datos, por tanto puedes incluir la siguiente fórmula en esa herramienta:

=CONTAR.SI.CONJUNTO(B:B; B2) > 1

Importante: la fórmula debe escribirse en la validación de la celda B2. Después ya podrás llevarte la validación a las celdas que desees copiándola y pegándola en cualquier ubicación. Esta misma fórmula te servirá para las columnas E y H.

 

[DiegoPC]

hace 3 horas, Mohamad Marrawi Marrawi dijo:

¿Se puede crear una sola tabla para todas las columnas que interesan, o tiene que ser una tabla para cada columna?

Convertirías el rango de todo tu cuadro en una tabla, luego crearías un nombre por cada columna que te interese y en la fórmula de validación usas el nombre creado como primer argumento y como segundo argumento usas la primera celda de cada columna (en tu ejemplo sería B2, E2 y H2 respectivamente)

[DiegoPC]

Hola Sergio, esa forma es buena porque te ahorras crear la tabla y los nombres pero soy enemigo número uno de usar referencias de columna completa. Me queda la tarea de testear rendimientos cuando la tabla sea grande, espero poder darme un tiempito para testearlo, por lo pronto sigo siendo enemigo de ese tipo de referencias. ?

[FGV]

Hola @Mohamad Marrawi Marrawi(DiegoPC y Sergio),

Otra opción es utilizar la función DESREF para  incluir en la validación todo el rango de la columna B que contenga datos, de forma que al escribir un dato en cualquier fila de esa columna, el rango dinámico lo asume en la validación:

En el ejemplo la validación personalizada con esa fórmula llega hasta la fila 1000, puedes reducirla o aumentarla como quieras. Un saludo, Fernando

Advertir duplicados.xlsx

[Sergio]

hace 7 horas, DiegoPC dijo:

soy enemigo número uno de usar referencias de columna completa.

A mí tampoco me gusta usar referencias de este tipo, pero era una de las condiciones de Mohamad...

Creo (no estoy totalmente seguro) de que se han hecho mejoras en Excel que permiten calcular solamente las celdas con contenido aunque se haga referencia a la columna completa.

[Mohamad Marrawi Marrawi]

hace 7 horas, Sergio dijo:

A mí tampoco me gusta usar referencias de este tipo, pero era una de las condiciones de Mohamad...

La verdad es que no hay ninguna condición. Lo único importante es que el sistema lance una alerta cada vez que entre un valor duplicado en las columnas B, E y H. 

Veo que se puede hacer referencia a columna completa o bien, usando una tabla. Cualquier método mientras funcione, ya serviría. Gracias a todos vosotros por vuestros geniales aportes.

Me quedo con cualquier método, pero si hay alguno en particular que sea lo ideal, sean bienvenidos en mencionar.

Gracias 

[Mohamad Marrawi Marrawi]

Hola @DiegoPC,

disculpa mi torpeza pero no consigo seguir el procedimiento que me has indicado. Me estoy liando con los nombres de las columnas y las referencias a la tabla. ¿Te importaría realizar el procedimiento en el archivo adjunto, por favor?

DHL EXPRESS.xlsm

[Gerson Pineda]

En 11/12/2022 at 3:48 , Sergio dijo:

Creo (no estoy totalmente seguro) de que se han hecho mejoras en Excel que permiten calcular solamente las celdas con contenido aunque se haga referencia a la columna completa.

En el caso, de las funciones como contar.si, sumar.si o sus funciones derivadas más actuales, son increíblemente eficientes, aun desde versiones antiguas

 Por lo que no es lo mismo hacer uso de esta fórmula [solo como caso comparativo]

=SUMAPRODUCTO(--(A:A=A2))>1

Con solo el hecho de probar con el evaluador de fórmulas, rápidamente nos damos cuenta de la ralentización, comparándolas con las antes mencionadas

Por lo tanto, en estos casos excepcionales, no hay problema alguno utilizar las columnas completas, que habitualmente es una mala practicar utilizarlas como rango  

 

Saludos a todos 

[Mohamad Marrawi Marrawi]

Hola Sergi,

He aplicado la fórmula que me has comentado en B2, E2 y H2 pero no me lo está haciendo bien. Adjunto el archivo. ¿Sabrías decirme qué es lo que faltaría?

En 11/12/2022 at 1:26 , Sergio dijo:

Después ya podrás llevarte la validación a las celdas que desees copiándola y pegándola en cualquier ubicación.

Esto ¿cómo se realizaría?

DHL EXPRESS.xlsm

[DiegoPC]

En 12/12/2022 at 15:14 , Mohamad Marrawi Marrawi dijo:

Hola @DiegoPC,

disculpa mi torpeza pero no consigo seguir el procedimiento que me has indicado. Me estoy liando con los nombres de las columnas y las referencias a la tabla. ¿Te importaría realizar el procedimiento en el archivo adjunto, por favor?

DHL EXPRESS.xlsm 17.53 kB · 1 descarga

Pues esta vez Excel me ha hecho quedar mal a mi, jajajaja.

Realicé pruebas y mi forma propuesta solamente funciona si todas las celdas de la columna contienen valores, si alguna se queda en blanco pues no funciona, creo que mejor optas por alguna de las otras soluciones indicadas por los maestros.

[Sergio]

hace 4 horas, Mohamad Marrawi Marrawi dijo:

¿Sabrías decirme qué es lo que faltaría?

La fórmula no debe tener espacios. No obstante la fórmula que te recomiendo usar es esta:

=SI(CONTAR.SI.CONJUNTO(B:B;B3)=1;1;0)

 

[Oscar_MRF]

Que tal a todos, verificando la consulta me permito aportar una alternativa que no requiere referenciar toda la columna:

Misma que se puede replicar en otras columnas, cambiando la referencia de columna.

Espero les sea útil.

[Sergio]

@Oscar_MRF, ¿tu función sería válida en caso de que ya exista un listado y se hagan modificaciones en un dato anterior? ¿o solo funcionaría con los nuevos valores introducidos?

[Oscar_MRF]

hace 1 hora, Sergio dijo:

@Oscar_MRF, ¿tu función sería válida en caso de que ya exista un listado y se hagan modificaciones en un dato anterior? ¿o solo funcionaría con los nuevos valores introducidos?

Estimado Sergio, evidentemente esta regla aplica solo para ingreso de nuevos datos, y no preve la modificación de un dato anterior, en cuyo caso aplicaría una regla que haga referencia a todo el rango o columna.

[Mohamad Marrawi Marrawi]

Hola Oscar_MRF,

He aplicado tu fórmula en B2 y sólo detecta la referencia duplicada en B3, pero no en las posteriores:

hace 12 horas, Oscar_MRF dijo:

¿Cómo puedo extender el rango al resto de la columna?

[Mohamad Marrawi Marrawi]

Hola Sergio,

He aplicado tu fórmula tanto en B2 como en B3 y no detecta referencias duplicadas:

hace 14 horas, Sergio dijo:

=SI(CONTAR.SI.CONJUNTO(B:B;B3)=1;1;0)

¿Hay algo que esté haciendo mal?

[Sergio]

La fórmula de mi mensaje debes introducirla en la validación de datos de la celda B3. A continuación,, arrastrala celda hacia arriba y hacia abajo para que se copien las validaciones también en esas celdas.

 

[Mohamad Marrawi Marrawi]

He  introducido la fórmula en B3 y luego arrastrado esa celda hacia arriba (B2) y hacia abajo (B4). Al parecer lo hace bien, pero más abajo de B4 no detecta referencias duplicadas. ¿Cómo puedo extender el rango más allá de B4? ¿Tengo que arrastrar hasta la fila que yo quiera, por ejemplo la 200?

[Sergio]

Hace 1 hora, Mohamad Marrawi Marrawi dijo:

¿Cómo puedo extender el rango más allá de B4?

Esa fórmula vale para cualquier celda de la columna B.

 

[Mohamad Marrawi Marrawi]

Yo apliqué la validación de la fórmula:

=SI(CONTAR.SI.CONJUNTO(B:B;B3)=1;1;0)

en B2, y luego  arrastré esa celda hacia arriba (B2) y hacia abajo (B4). Sí que detecta los duplicados entre B2 y B4 pero no las inferiores. Desde la B5 hasta abajo no detecta duplicados porque al parecer no hay validación:

[Gerson Pineda]

Hola @Mohamad Marrawi Marrawi

Como veo que tu libro contiene macro de evento, te dejo una solución con VBA

Agrega este código a tu macro Change

Dim vr As Integer
 With Target
    If .Column = 2 Or .Column = 5 Or .Column = 8 Then
        vr = Application.CountIf(Range(.EntireColumn.Address), .Value)
        If vr > 1 Then
            VBA.MsgBox "Entrada duplicada " & .Value
            .Value = Empty
            .Select
        End If
    End If
 End With

 

[Mohamad Marrawi Marrawi]

Hola Gerson Pineda,

El código actual que tengo en el macro es:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Range("B:B,E:E,H:H")) Is Nothing And InStr(1, Target.Address, ":") = 0 Then
  If Target <> Empty Then
   Target.Offset(0, 1).Value = Date
  Else
   Target.Offset(0, 1).Value = ""
  End If
 End If
End Sub

Private Sub Worksheet_Deactivate()

End Sub

--------------------------------------------------------------

¿En qué parte tendría que poner tu código?