Excel: Concatenar filas consecutivas en función del valor de otra columna (VBA)

En ocasiones, al pasar documentos pdf a excel resultan tablas en las que el contenido de una columna, para una fila del PDF, se distribuye en distintas filas en la tabla Excel exportada ( o bien otros casos).

Ejemplo 1:

El ejemplo siguiente lo muestra (al final de este ejemplo, mostramos otro caso de uso):

Task NumberCampo 1Description
task_1Valor 1Parte 1
Valor 1_1Subparte 1_1
Subparte 1_2
task_2Valor 2Parte 2
Subparte 2_1
task_3Valor 3Parte 3
Subparte 3_1
Subparte 3_2
Subparte 3_3
Subparte 3_4
task_4Valor 4Parte 4
Valor 4_1Subparte 4_1
Subparte 4_2
Subparte 4_3
Subparte 4_4
Tabla ejemplo al pasar de PDF a Excel

Con la tabla anterior no se puede trabajar bien para hacer comparaciones o tratar los datos correctamente

Lo que pretendemos es poder concatenar los valores del campo que se ha dividido en varias filas, que se puede conseguir muy fácilmente programando una función definida por el usuario (UFD) teniendo en cuenta el patrón que se repite en la tabla anterior, que es sencillamente que el inicio y final de las filas que se han multiplicado, es el valor de la columna Task Number, que será nuestra columna de referencia (REF_Col en el código VBA).

El proceso para crear una UFD es simplemente ir al menú herramientas, insertar un módulo VBA , copiar el código que se muestra a continuación y guardar como archivo XLS con macros (Extensión XLSM)

Proceso para insertar un módulo VBA en una tabla excel

Ahora, al hacer click en Módulo, se nos crea un espacio donde podemos escribir código, y escribimos lo siguiente, que es muy sencillo y simple. Tan sólo hace un bucle por las filas, teniendo en cuenta la columna de referencia que hemos indicado y validando si las filas siguientes corresponden a valores de esa misma columna de referencia o bien a otras (la validación puede hacerse de muchas maneras, en este caso, con la longitud del valor del campo, admitiendo la posibilidad de que existan saltos de línea), pero puede adaptarse a cada caso, como veremos en otro ejemplo posterior.

Función «recomponer», el parámetro de entrada es la celda cuyos valores queremos «recomponer» o concatenar.

Public Function recomponer(celda As Range) As String
Dim i As Integer
Dim REF As String
Dim valor As String
Dim fila As Integer
Dim columna As Integer
Dim REF_Col As Integer
 
' Definir la columna donde están las tareas (columna de referencia que establece los límites
' para la concatenación de las filas que le siguen)
REF_Col = 1 ' A=1, B=2, C=3...etc
 
' para automatizar mejor (poder hacer varias a la vez arrastrando a la derecha
' uso como columna la del argumento, que es lo que vamos a recomponer
fila = celda.Row
columna = celda.Column
REF = Cells(fila, REF_Col).Value
valor = Trim(Cells(fila, columna).Value)
 

' siguientes filas
i = 1
recomponer = ""
REF = Cells(fila + i, REF_Col).Value
Do While Len(REF) < 3 ' por si hay LF/CR
       recomponer = recomponer & " " & Cells(fila + i, columna).Value
    i = i + 1
    REF = Cells(fila + i, REF_Col).Value ' siguiente tarea
Loop
recomponer = valor & " " & Trim(recomponer) ' quitar espacios inic/final
 
End Function

' No olvidar añadir una fila última con "FIN" o similar

Muy importante: para que la última fila no produzca un error de fórmula de Excel, es necesario añadir un valor al final de la tabla, en la columna de referencia y que cumpla con los criterios o el patrón que estamos usando, en este ejemplo, basta con añadir cualquier palabra:

Añadir valor adicional a la Columna de Referencia al Final de la tabla para evitar error de función Excel

Hay que tener en cuenta que al guardar el archivo excel hay que hacerlo con macros, siguiendo el diálogo que no s muestra Excel

Guardar Excel con Macros

Una vez guardado todo, se puede probar como cualquier función excel, puede arrastrarse en distintas columnas y en distintas filas. Lo único que hay que tocar en el código es la columna de referencia.

Se selecciona una celda, se escribe la función de excel «recomponer», se selecciona la celda donde están los valores a concatenar y se obiente el resultado (se puede arrastrar a través de filas y columnas):

La función definida ya es mosrada por Excel

Seleccionamos como argumento el valor B2,

Argumento d ela función

el resultado es el deseado, concatena los valores de las filas siguientes, dentro de nuestro campo de Referencia task_1

Resultado de la función

Ahora arrastramos hacia la derecha (para incluir el campo Description) primero, y una vez calculado éste, hacia abajo para incluir todas las filas:

Arrastrar la función para abarcar más campos (columnas)
Arrastrar hacia abajo para abarcar las filas

Finalmente, ocultamos o borramos las filas en donde la Columna de Referencia está vacía (en este caso) y obtenemos la tabla final, que podemos usar ya (nota: para esto, es mejor copiar la tabla en otra pestaña como «valores» para evitar errores en las fórmulas)

Resultado tras filtrar

Ejemplo 2

El siguiente caso muestra cómo se puede transformar a tabla un PDF que aparentemente resulta complicado. Nos interesan las tareas, sus descripciones y sus intervalos:

Ejemplo de pdf con listado de tareas no fácilmente exportable a Excel

Pasar a Excel con Acrobat da un resultado no manejable.

Se pueden usar otras técnicas, por ejemplo, copiando las páginas pdf como texto y pegándolas en una tabla excel obtenemos el siguiente resultado, donde ya se observa un patrón para las tareas, y por tanto, susceptible de aplicar nuestra función recomponer de Excel:

Patrón en la copia como texto a Excel

El patrón es el siguiente:

  • Cada tarea comienza con un guión » – «
  • Le sigue algo de texto
  • La última parte es el intervalo, que comienza por un número.

Una vez filtradas (eliminadas) las filas que no interesan (aquellas que contienen títulos, en el ejemplo Section y descripciones de componentes), tenemos ya una tabla objetivo para aplicar la función recomponer, que antes tenemos que retocar para adaptarla al patrón que hemos encontrado. Adicionalemtene, preparamos los valores que se van a concatenar para poder separarlos luego en distintas columnas, con el caracter «*» como separador, por ejemplo. Este separador lo añadiremos al final de la tarea y al principio del intervalo, que detectamos si la fila empieza por un valor numérico. Todo se ve mucho más fácil en el código de la función:

Public Function recomponer(celda As Range) As String
Dim i As Integer
Dim REF As String
Dim VALOR As String
Dim fila As Integer
Dim columna As Integer
Dim REF_Col As Integer
 
' Definir la columna donde están las tareas (columna de referencia que establece los límites
' para la concatenación de las filas que le siguen)
REF_Col = 1 ' A=1, B=2, C=3...etc
 
' para automatizar mejor (poder hacer varias a la vez arrastrando a la derecha
' uso como columna la del argumento, que es lo que vamos a recomponer
fila = celda.Row
columna = celda.Column
REF = Cells(fila, REF_Col).Value
VALOR = Trim(Cells(fila, columna).Value)
 
Dim separador As String
' siguientes filas
i = 1
recomponer = ""
REF = Cells(fila + i, REF_Col).Value
'Do While Len(REF) < 3 ' por si hay LF/CR
Do While Left(REF, 1) <> "-" ' la tarea empieza por "-"
       separador = " "
       If IsNumeric(Left(Cells(fila + i, columna).Value, 1)) Then
            separador = " * " ' los intervalos van al final y siempre empiezan por un número
        End If
       recomponer = recomponer & separador & Cells(fila + i, columna).Value ' añado un separador para separar luego por columnas, pero solo para los VALORalos
       
    i = i + 1
    REF = Cells(fila + i, REF_Col).Value ' siguiente tarea
Loop
recomponer = VALOR & " * " & Trim(recomponer) ' quitar espacios inic/final y añado separador
 
End Function
' No olvidar añadir una fila última con "FIN" o similar
' en este caso, siguiendo el patrón ha de llevar un guión delante:  - FIN

Ahora, ejecutando la función, puede apreciarse el resultado y cómo podemos ya pasar la fila a columnas. Antes, hay que ocultar las filas donde no tenemos tareas (aquellas que no empiezan por «-«), y copiar a otra pestaña como valores, para evitar errores con la función

Resultado de la función

Filtrado por las filas que no contienen tareas, y pasado a columnas, con el menú datos en columnas, con el separador «*»

Pasar una columna a varias, con un delimitador (en este caso ‘*’)

Y llegamos al resultado final, con la tabla final:

Tabla separada, mediante la función recomponer y herramientas Excel

Leave a Reply

Tu dirección de correo electrónico no será publicada.

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.