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):

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)

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:

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

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):

Seleccionamos como argumento el valor B2,

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

Ahora arrastramos hacia la derecha (para incluir el campo Description) primero, y una vez calculado éste, hacia abajo para incluir todas 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)

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:

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:

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

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

Y llegamos al resultado final, con la tabla final: