Como filtrar las celdas que contienen cadenas de texto duplicadas (palabras)

Uno de mis amigos trabaja en una empresa de análisis sanitaria. A menudo conecta conmigo en algunos de los problemas de la vida real a los que se enfrenta mientras trabaja con datos a Excel.

Muchas veces convierto sus consultas en tutoriales de Excel en este lugar, ya que también podría ser útil para mis otros lectores.

Este también es uno de esos tutorial.

Mi amigo me llamó la semana pasada con el siguiente número:

Hay datos de dirección en una columna de Excel y quiero identificar / filtrar las celdas donde la dirección provee texto duplicadas (palabras).

A continuación el conjunto de datos similar en que quería filtrar las celdas que contienen una cadena de texto duplicada (las que tienen flechas rojas):

Identifique cadenas de texto duplicadas a Excel: dirección del conjunto de datos

Ahora lo que dificulta esto es que no hay coherencia en estos datos. Como se trata de una recopilación de conjuntos de datos que han sido creados manualmente por los representantes de ventas, puede haber variaciones en el conjunto de datos.

Considere esto:

  • Cualquier cadena de texto se podría repetir en este conjunto de datos. Por ejemplo, podría ser el nombre de la zona o el nombre de la ciudad o ambos.
  • Las palabras están separadas por un carácter espacial y no hay coherencia en sí el nombre de la ciudad sería después de seis u ocho palabras.
  • Hay miles de registros como este, y la necesidad es filtrar aquellos registros donde haya cadenas de texto duplicadas.

Tras considerar muchas opciones (como texto en columnas y fórmulas), finalmente decidí utilizar VBA para hacerlo.

Por lo tanto, he creado una función VBA personalizada ( “IdDuplicate”) para analizar estas celdas y darme CERT si hay una palabra duplicada en la cadena de texto y FALSO en caso de que no haya repeticiones (como se muestra en continuación):

Identifique cadenas de texto duplicadas a Excel: demostración de dirección del conjunto de datos

Esta función personalizada analiza cada palabra de la cadena de texto y comprueba cuántas veces aparece en el texto. Si el recuento es superior a 1, regresa TRUE; de lo contrario, vuelve FALSO.

Además, se ha creado para contar sólo palabras de más de tres caracteres.

Una vez tengo los datos CERT / FALSO, puedo filtrar fácilmente todos los registros que son CERT.

Ahora dejadme mostraros cómo hacerlo en Excel.

Código VBA para la función personalizada

Esto se hace creando una función personalizada a VBA. Esta función se puede utilizar como cualquier otra función de hoja de cálculo en Excel.

Aquí tiene el código VBA:

Function IdDuplicates(rng As Range) As String
Dim StringtoAnalyze As Variant
Dim i As Integer
Dim j As Integer
Const minWordLen As Integer = 4
StringtoAnalyze = Split(UCase(rng.Value), " ")
For i = UBound(StringtoAnalyze) To 0 Step -1
If Len(StringtoAnalyze(i)) < minWordLen Then GoTo SkipA
For j = 0 To i - 1
If StringtoAnalyze(j) = StringtoAnalyze(i) Then
IdDuplicates = "TRUE"
GoTo SkipB
End If
Next j
SkipA:
Next i
IdDuplicates = "FALSE"
SkipB:
End Function

Gracias Walter por sugerir un mejor enfoque de este código.

Como se utiliza este código VBA

Ahora que tiene el código VBA, debe colocarlo en el dorsal de Excel, para que funcione como una función de hoja de cálculo normal.

A continuación se muestran los pasos para poner el código VBA en el dorsal:

  1. Ve a la pestaña Desarrollador.identifique cadenas de texto duplicadas: pestaña del desarrollador en la cinta
  2. Haga clic en Visual Basic (también puede utilizar el atajo de teclado ALT + F11)Seleccione Visual basic en la cinta
  3. Al fondo posterior del editor VB que se abre, haga clic con el botón derecho sobre cualquiera de los objetos del libro.
  4. Vaya a “Insertar” y haga clic en “Módulo”. Esto insertará el objeto del módulo para el libro.Inserte un módulo para el código VAB personalizado
  5. En la ventana del código del módulo, copie y pegue el código VBA mencionado anteriormente.Código VBA al fondo: para identificar cadenas de texto duplicadas

Una vez que tenga el código VBA en la parte posterior, puede utilizar la función – “IdDuplicates” como cualquier otra función de hoja de trabajo normal.

Esta función adopta un argumento único, que es la referencia de celda de la celda donde está el texto.

El resultado de la función es CERT (si hay palabras duplicadas) o FALSO (si no hay duplicados). Una vez que tenga esta lista de VERDADES / FALSOS, puede filtrarlos con VERDADES para obtener todas las celdas que tengan cadenas de texto duplicadas.

Nota: He creado el código sólo para tener en cuenta aquellas palabras de más de tres caracteres. Esto garantiza que si hay palabras de 1, 2 o 3 caracteres (como 12 A, KGM o LDA) en la cadena de texto, estas se ignoran mientras se cuentan los duplicados. Si lo desea, puede cambiarlo fácilmente al código.

Esta función sólo estará disponible en el libro de trabajo donde haya copiado el código en el módulo. En caso de que desee que esto esté disponible también en otros libros de trabajo, debe copiar y pegar este código en estos libros de trabajo. Como alternativa, también puede crear un complemento (habilitación que hará que esta función esté disponible en todos los libros de su sistema).

Además, recuerde guardar este libro a la extensión .xlsm (ya que tiene un código macro).

También te pueden gustar los siguientes tutoriales de Excel:

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *