Uno de mis amigos trabaja en una empresa de análisis de la salud. A menudo se conecta conmigo sobre algunos de los problemas de la vida real que enfrenta mientras trabaja con datos en Excel.
Muchas veces, convierto sus consultas en tutoriales de Excel en este sitio, ya que también podría ser útil para mis otros lectores.
Este es también uno de esos tutoriales.
Mi amigo me llamó la semana pasada con el siguiente problema:
Hay datos de direcciones en una columna en Excel, y quiero identificar / filtrar celdas donde la dirección tiene cadenas de texto duplicadas (palabras).
Aquí está el conjunto de datos similar en el que quería filtrar las celdas que tienen una cadena de texto duplicada (las que tienen flechas rojas):
Ahora bien, lo que dificulta esto es que no hay coherencia en estos datos. Dado que se trata de una compilación de un conjunto de datos que ha sido creado manualmente por los representantes de ventas, puede haber variaciones en el conjunto de datos.
Considera esto:
- Cualquier cadena de texto podría repetirse en este conjunto de datos. Por ejemplo, podría ser el nombre del área o el nombre de la ciudad o ambos.
- Las palabras están separadas por un carácter de espacio y no hay coherencia en si el nombre de la ciudad estaría allí después de seis u ocho palabras.
- Hay miles de registros como este, y la necesidad es filtrar aquellos registros donde hay cadenas de texto duplicadas.
Después de considerar muchas opciones (como texto en columnas y fórmulas), finalmente decidí usar VBA para hacer esto.
Así que creé una función de VBA personalizada ("IdDuplicate") para analizar estas celdas y darme VERDADERO si hay una palabra duplicada en la cadena de texto, y FALSO en caso de que no haya repeticiones (como se muestra a continuación):
Esta función personalizada analiza cada palabra en la cadena de texto y verifica cuántas veces aparece en el texto. Si la cuenta es más de 1, devuelve VERDADERO; de lo contrario, devuelve FALSE.
Además, ha sido creado para contar solo palabras de más de tres caracteres.
Una vez que tengo los datos VERDADEROS / FALSOS, puedo filtrar fácilmente todos los registros que son VERDADEROS.
Ahora déjame mostrarte cómo hacer esto en Excel.
Código VBA para la función personalizada
Esto se hace creando una función personalizada en VBA. Esta función se puede utilizar como cualquier otra función de hoja de cálculo en Excel.
Aquí está el código VBA para ello:
Función 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 Paso -1 Si 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: Finalizar función
Gracias Walter por sugerir un mejor enfoque para este código.
Cómo usar este código VBA
Ahora que tiene el código VBA, debe colocarlo en el backend de Excel para que pueda funcionar como una función normal de la hoja de trabajo.
A continuación se muestran los pasos para poner el código VBA en el backend:
- Vaya a la pestaña Desarrollador.
- Haga clic en Visual Basic (también puede usar el atajo de teclado ALT + F11)
- En el back-end de VB Editor que se abre, haga clic con el botón derecho en cualquiera de los objetos del libro de trabajo.
- Vaya a "Insertar" y haga clic en "Módulo". Esto insertará el objeto de módulo para el libro de trabajo.
- En la ventana Código del módulo, copie y pegue el código VBA mencionado anteriormente.
Una vez que tenga el código VBA en el back-end, puede usar la función "IdDuplicates" como cualquier otra función de hoja de trabajo normal.
Esta función toma un solo argumento, que es la referencia de celda de la celda donde tiene el texto.
El resultado de la función es VERDADERO (si hay palabras duplicadas) o FALSO (si no hay duplicados). Una vez que tenga esta lista de VERDADERO / FALSO, puede filtrar las que tengan VERDADERO para obtener todas las celdas que tienen cadenas de texto duplicadas.
Nota: He creado el código solo para considerar aquellas palabras que tienen más de tres caracteres. Esto asegura que si hay 1, 2 o 3 palabras de largo carácter (como 12 A, K G M o L D A) en la cadena de texto, estas se ignoran mientras se cuentan los duplicados. Si lo desea, puede cambiarlo fácilmente en el código.
Esta función solo estará disponible en el libro de trabajo donde haya copiado el código en el módulo. En caso de que desee que esto también esté disponible en otros libros de trabajo, debe copiar y pegar este código en esos libros de trabajo. Alternativamente, también puede crear un complemento (habilitar lo que haría que esta función esté disponible en todos los libros de trabajo de su sistema).
Además, recuerde guardar este libro en la extensión .xlsm (ya que tiene un código de macro).
