Cómo evitar duplicados al consolidar archivos con VBA

Introducción

Consolidar varios archivos de Excel mediante VBA puede ahorrar muchas horas de trabajo, pero también puede introducir un problema serio: la aparición de registros duplicados. Un mismo cliente, pedido, factura, producto o movimiento puede estar presente en varios archivos, repetirse dentro de una misma hoja o aparecer con pequeñas diferencias de escritura que dificultan su detección.

Evitar duplicados no consiste únicamente en borrar filas repetidas. Antes de programar la macro es necesario definir qué identifica de forma única cada registro, cómo deben compararse los datos y qué tratamiento debe aplicarse cuando se encuentra una coincidencia. En algunos procesos será correcto conservar solo una fila; en otros habrá que sumar cantidades, actualizar datos, guardar ambas versiones o generar un informe independiente de incidencias.

Este artículo explica cómo diseñar una consolidación con VBA capaz de detectar y gestionar duplicados de forma controlada, trazable y adaptada a las necesidades reales de una pequeña empresa.

Índice

Qué se considera un duplicado al consolidar archivos

Dos filas no tienen por qué ser completamente idénticas para representar el mismo registro. En una consolidación empresarial, el concepto de duplicado depende de la lógica del proceso y no solo del contenido visible de las celdas.

Por ejemplo, dos filas pueden corresponder a la misma factura aunque una tenga el nombre del cliente escrito en mayúsculas y otra en minúsculas. También pueden representar el mismo pedido aunque una incluya espacios adicionales, una fecha con formato distinto o un código almacenado como número en un archivo y como texto en otro.

Antes de desarrollar la macro conviene clasificar los posibles duplicados:

• Duplicado exacto: todos los campos relevantes contienen exactamente los mismos valores.
• Duplicado por clave: coincide el identificador principal, aunque otros campos sean diferentes.
• Duplicado lógico: los datos parecen distintos, pero representan la misma entidad después de normalizarlos.
• Duplicado parcial: coinciden varios campos importantes, aunque no existe un identificador único fiable.
• Duplicado acumulable: las filas corresponden al mismo concepto y deben agruparse sumando importes, cantidades u horas.

Esta clasificación es importante porque cada tipo requiere un tratamiento diferente. Una macro que elimina cualquier fila con la misma clave puede borrar información válida si no se ha definido correctamente la regla de negocio.

Cómo elegir el campo clave de la tabla

El campo clave es el dato utilizado para identificar de manera única cada registro. Puede ser un número de factura, un código de pedido, un identificador de cliente, una matrícula, una referencia de producto o cualquier otro valor estable que no debería repetirse.

Una buena clave debe cumplir, en la medida de lo posible, estas condiciones:

• Estar presente en todos los archivos que se van a consolidar.
• No cambiar con el tiempo.
• No depender de la forma en que una persona escriba un nombre o una descripción.
• No contener espacios o caracteres añadidos de manera accidental.
• No reutilizarse para registros distintos.
• Ser suficientemente específica para evitar coincidencias falsas.

Los nombres de clientes, empresas o productos suelen ser malas claves porque pueden escribirse de varias formas. Por ejemplo, «Talleres Norte», «TALLERES NORTE» y «Talleres Norte, S.L.» podrían referirse a la misma empresa, pero una comparación directa los consideraría distintos.

Siempre que sea posible, es preferible utilizar códigos internos, números de documento o identificadores generados por el sistema de origen.

Qué ocurre si la clave está vacía

Una fila sin clave no debería incorporarse automáticamente como si fuera válida. La macro puede enviarla a una hoja de incidencias, marcarla como incompleta o asignarle un identificador provisional, pero esta decisión debe estar definida de antemano.

Utilizar una cadena vacía como clave en un diccionario provocaría que todas las filas sin identificador se considerasen duplicadas entre sí, aunque pertenezcan a operaciones completamente distintas.

Cuándo utilizar una clave compuesta

En muchas tablas no existe una sola columna capaz de identificar un registro. En ese caso puede construirse una clave compuesta combinando varios campos.

Por ejemplo, un movimiento podría identificarse mediante la combinación de:

• Código de cliente.
• Fecha de operación.
• Número de documento.
• Referencia del producto.

En VBA, una clave compuesta puede construirse concatenando los valores normalizados con un separador poco probable:

clave = codigoCliente & "|" & fechaNormalizada & "|" & numeroDocumento

El separador evita ambigüedades. Sin él, las combinaciones «12» y «345» y «123» y «45» producirían en ambos casos la cadena «12345».

La clave compuesta debe contener únicamente los campos necesarios para identificar el registro. Si se incluyen columnas que pueden cambiar, como una descripción o un importe corregido, dos versiones de la misma operación podrían dejar de ser detectadas como duplicadas.

Normalizar los datos antes de compararlos

La normalización consiste en transformar los valores a un formato común antes de compararlos. Este paso reduce los falsos negativos, es decir, los casos en los que dos registros equivalentes no son detectados como duplicados.

Entre las operaciones habituales de normalización se encuentran:

• Eliminar espacios al principio y al final.
• Convertir el texto a mayúsculas o minúsculas.
• Reemplazar varios espacios consecutivos por uno solo.
• Eliminar saltos de línea y caracteres no imprimibles.
• Unificar guiones, barras y signos de puntuación.
• Convertir fechas a un formato interno estable.
• Convertir números almacenados como texto en valores numéricos.
• Eliminar ceros iniciales solo cuando no formen parte real del identificador.

No todas las transformaciones son válidas para cualquier dato. Por ejemplo, eliminar ceros iniciales puede ser correcto para un número de pedido, pero incorrecto para un código como «00125», donde los ceros forman parte de la referencia oficial.

Función básica de normalización de texto

Private Function NormalizarTexto(ByVal valor As Variant) As String
    Dim texto As String

```
If IsError(valor) Or IsNull(valor) Or IsEmpty(valor) Then
    NormalizarTexto = vbNullString
    Exit Function
End If

texto = CStr(valor)
texto = Application.WorksheetFunction.Clean(texto)
texto = Replace(texto, Chr(160), " ")
texto = Application.WorksheetFunction.Trim(texto)
texto = UCase$(texto)

NormalizarTexto = texto
```

End Function

Esta función elimina caracteres no imprimibles, sustituye espacios especiales, reduce espacios repetidos y convierte el texto a mayúsculas.

Cómo comparar cadenas de texto correctamente

VBA permite comparar cadenas de varias formas. El resultado puede depender de si la comparación distingue entre mayúsculas y minúsculas, de la configuración regional y de si se han normalizado previamente los datos.

Comparación binaria

La comparación binaria distingue entre mayúsculas y minúsculas. Por tanto, «CLIENTE A» y «Cliente A» se consideran valores diferentes.

If StrComp(texto1, texto2, vbBinaryCompare) = 0 Then
    ' Los textos son exactamente iguales
End If

Comparación textual

La comparación textual ignora normalmente las diferencias entre mayúsculas y minúsculas.

If StrComp(texto1, texto2, vbTextCompare) = 0 Then
    ' Los textos son equivalentes
End If

Aun así, la comparación textual no corrige espacios sobrantes, signos distintos, abreviaturas o errores ortográficos. Por ello, resulta recomendable normalizar las cadenas antes de compararlas.

Comparar nombres y descripciones

Cuando la clave se basa en nombres o descripciones, la detección se vuelve menos fiable. «Construcciones Pérez» y «Construcciones Pérez S.L.» pueden referirse a la misma empresa, pero una comparación exacta no lo sabrá.

En estos casos pueden aplicarse reglas adicionales, como eliminar determinadas formas societarias, sustituir abreviaturas conocidas o mantener una tabla maestra de equivalencias. Sin embargo, cuanto más flexible sea la comparación, mayor será el riesgo de considerar duplicados dos registros diferentes.

Detectar duplicados con un objeto Dictionary

Una de las formas más eficientes de detectar claves repetidas en VBA es utilizar un objeto Scripting.Dictionary. Este objeto permite almacenar cada clave encontrada y comprobar rápidamente si ya existe.

El proceso general es el siguiente:

1. Crear el diccionario.
2. Recorrer las filas de los archivos de origen.
3. Construir y normalizar la clave de cada fila.
4. Comprobar si la clave ya existe.
5. Agregar el registro si es nuevo.
6. Aplicar la regla definida si está duplicado.

Dim diccionario As Object
Set diccionario = CreateObject("Scripting.Dictionary")

diccionario.CompareMode = vbTextCompare

If Not diccionario.Exists(clave) Then
diccionario.Add clave, filaDestino
Else
' La clave ya existe
End If

El valor asociado a cada clave puede ser el número de fila de destino, un array con datos acumulados, una colección de registros o un objeto personalizado. La elección depende de lo que deba hacerse con los duplicados.

Ventajas frente a buscar en la hoja

Buscar cada clave mediante Find, Match o un bucle por todas las filas de destino puede funcionar con pocos registros, pero se vuelve lento cuando se consolidan miles de filas. El diccionario mantiene las claves en memoria y permite consultas mucho más rápidas.

Qué hacer con los registros duplicados

Detectar un duplicado es solo una parte del problema. La macro debe saber qué acción realizar. Las alternativas más habituales son:

• Ignorar el nuevo registro.
• Sustituir el registro anterior.
• Conservar el registro más reciente.
• Conservar el registro más completo.
• Sumar campos numéricos.
• Agrupar varias filas en una sola.
• Guardar todas las versiones y marcar la duplicidad.
• Enviar el caso a una hoja de revisión.
• Detener el proceso y solicitar una decisión manual.

La elección no debe dejarse a criterio del programador. Es una regla funcional que debe acordarse con la persona responsable del proceso.

Ejemplo de reglas posibles

Eliminar duplicados conservando un registro

Cuando los duplicados son copias exactas, puede conservarse la primera aparición e ignorar las siguientes. Esta estrategia es sencilla, pero debe documentarse claramente.

If Not diccionario.Exists(clave) Then
    diccionario.Add clave, True
    ' Copiar la fila al resultado
Else
    ' No copiar la fila repetida
End If

También es posible conservar la última aparición. En ese caso, cuando la clave ya exista, la macro deberá sobrescribir en la hoja consolidada los datos almacenados anteriormente.

La decisión entre conservar el primero o el último registro puede cambiar el resultado. Si los archivos contienen actualizaciones sucesivas, normalmente interesa conservar la versión más reciente, pero solo si existe un campo de fecha fiable que permita determinar el orden.

Sumarizar o agrupar registros duplicados

En ciertos procesos, una clave repetida no representa un error. Puede indicar que existen varios movimientos que deben agruparse. Por ejemplo, varias líneas del mismo producto podrían consolidarse sumando sus cantidades e importes.

Cuando se detecta una clave ya existente, la macro puede recuperar la fila de destino y actualizar los campos acumulables:

filaExistente = diccionario(clave)

wsDestino.Cells(filaExistente, columnaCantidad).Value = _
wsDestino.Cells(filaExistente, columnaCantidad).Value + cantidadNueva

wsDestino.Cells(filaExistente, columnaImporte).Value = _
wsDestino.Cells(filaExistente, columnaImporte).Value + importeNuevo

No todos los campos deben sumarse. Los códigos, fechas, textos y categorías requieren otras reglas. Una consolidación bien diseñada define qué columnas:

• Se suman.
• Se conservan del primer registro.
• Se sustituyen por el último valor.
• Se concatenan.
• Se validan para comprobar que coinciden.

Controlar diferencias inesperadas

Si dos filas tienen la misma clave, pero pertenecen a clientes distintos o contienen descripciones incompatibles, la macro no debería sumarlas automáticamente. Puede tratarse de una clave mal construida o de un error en los datos de origen.

Crear un registro independiente de duplicados

Una solución prudente consiste en crear una hoja específica, por ejemplo «Duplicados», donde se copien los casos encontrados. Esto permite revisar qué registros fueron descartados, agrupados o sustituidos.

El registro de incidencias puede incluir:

• Clave detectada.
• Archivo de origen.
• Nombre de la hoja.
• Número de fila original.
• Tipo de duplicidad.
• Acción aplicada.
• Fecha y hora de ejecución.
• Datos del registro anterior.
• Datos del registro nuevo.

Esta trazabilidad es especialmente útil cuando la consolidación afecta a facturación, compras, inventario, partes de trabajo o información que posteriormente será utilizada para tomar decisiones.

Ejemplo de registro

Private Sub RegistrarDuplicado( _
    ByVal wsLog As Worksheet, _
    ByVal clave As String, _
    ByVal archivoOrigen As String, _
    ByVal hojaOrigen As String, _
    ByVal filaOrigen As Long, _
    ByVal accion As String)

```
Dim siguienteFila As Long

siguienteFila = wsLog.Cells(wsLog.Rows.Count, 1).End(xlUp).Row + 1

wsLog.Cells(siguienteFila, 1).Value = Now
wsLog.Cells(siguienteFila, 2).Value = clave
wsLog.Cells(siguienteFila, 3).Value = archivoOrigen
wsLog.Cells(siguienteFila, 4).Value = hojaOrigen
wsLog.Cells(siguienteFila, 5).Value = filaOrigen
wsLog.Cells(siguienteFila, 6).Value = accion
```

End Sub

Ejemplo completo de código VBA

El siguiente ejemplo recorre todos los archivos de una carpeta, lee una hoja denominada «Datos», utiliza la primera columna como clave y consolida los registros en una hoja llamada «Consolidado». Cuando encuentra una clave repetida, no vuelve a copiarla y crea una entrada en la hoja «Duplicados».

El código es una base adaptable. Antes de utilizarlo en producción deben ajustarse las rutas, nombres de hojas, columnas, encabezados y reglas de tratamiento.

Option Explicit

Public Sub ConsolidarSinDuplicados()

    Const NOMBRE_HOJA_ORIGEN As String = "Datos"
    Const NOMBRE_HOJA_DESTINO As String = "Consolidado"
    Const NOMBRE_HOJA_LOG As String = "Duplicados"
    Const COLUMNA_CLAVE As Long = 1
    Const PRIMERA_FILA_DATOS As Long = 2

    Dim wbDestino As Workbook
    Dim wbOrigen As Workbook
    Dim wsDestino As Worksheet
    Dim wsOrigen As Worksheet
    Dim wsLog As Worksheet

    Dim diccionario As Object
    Dim carpeta As String
    Dim archivo As String
    Dim rutaCompleta As String

    Dim ultimaFilaOrigen As Long
    Dim ultimaColumnaOrigen As Long
    Dim filaOrigen As Long
    Dim filaDestino As Long
    Dim clave As String

    Dim calculoAnterior As XlCalculation
    Dim pantallaAnterior As Boolean
    Dim eventosAnteriores As Boolean
    Dim alertasAnteriores As Boolean

    On Error GoTo GestionarError

    Set wbDestino = ThisWorkbook
    Set wsDestino = ObtenerOCrearHoja(wbDestino, NOMBRE_HOJA_DESTINO)
    Set wsLog = ObtenerOCrearHoja(wbDestino, NOMBRE_HOJA_LOG)

    PrepararHojaLog wsLog

    carpeta = SeleccionarCarpeta()
    If Len(carpeta) = 0 Then Exit Sub

    If Right$(carpeta, 1) <> Application.PathSeparator Then
        carpeta = carpeta & Application.PathSeparator
    End If

    Set diccionario = CreateObject("Scripting.Dictionary")
    diccionario.CompareMode = vbTextCompare

    CargarClavesExistentes _
        wsDestino:=wsDestino, _
        diccionario:=diccionario, _
        columnaClave:=COLUMNA_CLAVE, _
        primeraFilaDatos:=PRIMERA_FILA_DATOS

    filaDestino = SiguienteFilaDisponible(wsDestino, 1, PRIMERA_FILA_DATOS)

    pantallaAnterior = Application.ScreenUpdating
    eventosAnteriores = Application.EnableEvents
    alertasAnteriores = Application.DisplayAlerts
    calculoAnterior = Application.Calculation

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
    Application.StatusBar = "Iniciando consolidación..."

    archivo = Dir$(carpeta & "*.xls*")

    Do While Len(archivo) > 0

        rutaCompleta = carpeta & archivo

        If StrComp(rutaCompleta, wbDestino.FullName, vbTextCompare) <> 0 Then

            Application.StatusBar = "Procesando: " & archivo

            Set wbOrigen = Workbooks.Open( _
                Filename:=rutaCompleta, _
                UpdateLinks:=False, _
                ReadOnly:=True, _
                AddToMru:=False)

            Set wsOrigen = Nothing

            On Error Resume Next
            Set wsOrigen = wbOrigen.Worksheets(NOMBRE_HOJA_ORIGEN)
            On Error GoTo GestionarError

            If Not wsOrigen Is Nothing Then

                ultimaFilaOrigen = UltimaFilaConDatos(wsOrigen)
                ultimaColumnaOrigen = UltimaColumnaConDatos(wsOrigen)

                If ultimaFilaOrigen >= PRIMERA_FILA_DATOS _
                   And ultimaColumnaOrigen > 0 Then

                    CopiarEncabezadosSiProcede _
                        wsOrigen:=wsOrigen, _
                        wsDestino:=wsDestino, _
                        ultimaColumna:=ultimaColumnaOrigen

                    For filaOrigen = PRIMERA_FILA_DATOS To ultimaFilaOrigen

                        clave = NormalizarTexto( _
                            wsOrigen.Cells(filaOrigen, COLUMNA_CLAVE).Value2)

                        If Len(clave) = 0 Then

                            RegistrarIncidencia _
                                wsLog:=wsLog, _
                                clave:="", _
                                archivoOrigen:=archivo, _
                                hojaOrigen:=wsOrigen.Name, _
                                filaOrigen:=filaOrigen, _
                                accion:="Fila no consolidada: clave vacía"

                        ElseIf Not diccionario.Exists(clave) Then

                            wsDestino.Cells(filaDestino, 1) _
                                .Resize(1, ultimaColumnaOrigen).Value = _
                                wsOrigen.Cells(filaOrigen, 1) _
                                .Resize(1, ultimaColumnaOrigen).Value

                            diccionario.Add clave, filaDestino
                            filaDestino = filaDestino + 1

                        Else

                            RegistrarIncidencia _
                                wsLog:=wsLog, _
                                clave:=clave, _
                                archivoOrigen:=archivo, _
                                hojaOrigen:=wsOrigen.Name, _
                                filaOrigen:=filaOrigen, _
                                accion:="Duplicado ignorado"

                        End If

                    Next filaOrigen

                End If

            Else

                RegistrarIncidencia _
                    wsLog:=wsLog, _
                    clave:="", _
                    archivoOrigen:=archivo, _
                    hojaOrigen:="", _
                    filaOrigen:=0, _
                    accion:="Archivo omitido: no existe la hoja " & _
                            NOMBRE_HOJA_ORIGEN

            End If

            wbOrigen.Close SaveChanges:=False
            Set wbOrigen = Nothing
            Set wsOrigen = Nothing

        End If

        archivo = Dir$

    Loop

SalidaSegura:

    On Error Resume Next

    If Not wbOrigen Is Nothing Then
        wbOrigen.Close SaveChanges:=False
    End If

    Application.ScreenUpdating = pantallaAnterior
    Application.EnableEvents = eventosAnteriores
    Application.DisplayAlerts = alertasAnteriores
    Application.Calculation = calculoAnterior
    Application.StatusBar = False

    On Error GoTo 0

    If Err.Number = 0 Then
        MsgBox _
            "La consolidación ha finalizado." & vbCrLf & _
            "Revise la hoja '" & NOMBRE_HOJA_LOG & _
            "' para consultar las incidencias.", _
            vbInformation
    End If

    Exit Sub

GestionarError:

    Dim numeroError As Long
    Dim descripcionError As String

    numeroError = Err.Number
    descripcionError = Err.Description

    On Error Resume Next

    RegistrarIncidencia _
        wsLog:=wsLog, _
        clave:="", _
        archivoOrigen:=archivo, _
        hojaOrigen:="", _
        filaOrigen:=filaOrigen, _
        accion:="Error " & numeroError & ": " & descripcionError

    On Error GoTo 0

    MsgBox _
        "La consolidación no pudo completarse." & vbCrLf & _
        "Error " & numeroError & ": " & descripcionError, _
        vbExclamation

    Resume SalidaSegura

End Sub

Private Function NormalizarTexto(ByVal valor As Variant) As String

    Dim texto As String

    If IsError(valor) Or IsNull(valor) Or IsEmpty(valor) Then
        NormalizarTexto = vbNullString
        Exit Function
    End If

    texto = CStr(valor)
    texto = Application.WorksheetFunction.Clean(texto)
    texto = Replace(texto, Chr(160), " ")
    texto = Application.WorksheetFunction.Trim(texto)
    texto = UCase$(texto)

    NormalizarTexto = texto

End Function

Private Sub CargarClavesExistentes( _
    ByVal wsDestino As Worksheet, _
    ByVal diccionario As Object, _
    ByVal columnaClave As Long, _
    ByVal primeraFilaDatos As Long)

    Dim ultimaFila As Long
    Dim fila As Long
    Dim clave As String

    ultimaFila = wsDestino.Cells( _
        wsDestino.Rows.Count, columnaClave).End(xlUp).Row

    If ultimaFila < primeraFilaDatos Then Exit Sub

    For fila = primeraFilaDatos To ultimaFila

        clave = NormalizarTexto( _
            wsDestino.Cells(fila, columnaClave).Value2)

        If Len(clave) > 0 Then
            If Not diccionario.Exists(clave) Then
                diccionario.Add clave, fila
            End If
        End If

    Next fila

End Sub

Private Sub CopiarEncabezadosSiProcede( _
    ByVal wsOrigen As Worksheet, _
    ByVal wsDestino As Worksheet, _
    ByVal ultimaColumna As Long)

    If Application.WorksheetFunction.CountA(wsDestino.Rows(1)) = 0 Then

        wsDestino.Cells(1, 1) _
            .Resize(1, ultimaColumna).Value = _
            wsOrigen.Cells(1, 1) _
            .Resize(1, ultimaColumna).Value

    End If

End Sub

Private Sub PrepararHojaLog(ByVal wsLog As Worksheet)

    If Application.WorksheetFunction.CountA(wsLog.Rows(1)) = 0 Then
        wsLog.Cells(1, 1).Value = "Fecha y hora"
        wsLog.Cells(1, 2).Value = "Clave"
        wsLog.Cells(1, 3).Value = "Archivo de origen"
        wsLog.Cells(1, 4).Value = "Hoja de origen"
        wsLog.Cells(1, 5).Value = "Fila de origen"
        wsLog.Cells(1, 6).Value = "Acción aplicada"
    End If

End Sub

Private Sub RegistrarIncidencia( _
    ByVal wsLog As Worksheet, _
    ByVal clave As String, _
    ByVal archivoOrigen As String, _
    ByVal hojaOrigen As String, _
    ByVal filaOrigen As Long, _
    ByVal accion As String)

    Dim siguienteFila As Long

    If wsLog Is Nothing Then Exit Sub

    siguienteFila = wsLog.Cells( _
        wsLog.Rows.Count, 1).End(xlUp).Row + 1

    If siguienteFila < 2 Then siguienteFila = 2

    wsLog.Cells(siguienteFila, 1).Value = Now
    wsLog.Cells(siguienteFila, 2).Value = clave
    wsLog.Cells(siguienteFila, 3).Value = archivoOrigen
    wsLog.Cells(siguienteFila, 4).Value = hojaOrigen
    wsLog.Cells(siguienteFila, 5).Value = filaOrigen
    wsLog.Cells(siguienteFila, 6).Value = accion

End Sub

Private Function ObtenerOCrearHoja( _
    ByVal libro As Workbook, _
    ByVal nombreHoja As String) As Worksheet

    On Error Resume Next
    Set ObtenerOCrearHoja = libro.Worksheets(nombreHoja)
    On Error GoTo 0

    If ObtenerOCrearHoja Is Nothing Then

        Set ObtenerOCrearHoja = libro.Worksheets.Add( _
            After:=libro.Worksheets(libro.Worksheets.Count))

        ObtenerOCrearHoja.Name = nombreHoja

    End If

End Function

Private Function SeleccionarCarpeta() As String

    Dim selector As FileDialog

    Set selector = Application.FileDialog(msoFileDialogFolderPicker)

    With selector
        .Title = "Seleccione la carpeta con los archivos a consolidar"
        .AllowMultiSelect = False

        If .Show = -1 Then
            SeleccionarCarpeta = .SelectedItems(1)
        Else
            SeleccionarCarpeta = vbNullString
        End If
    End With

End Function

Private Function UltimaFilaConDatos( _
    ByVal ws As Worksheet) As Long

    Dim celda As Range

    Set celda = ws.Cells.Find( _
        What:="*", _
        After:=ws.Cells(1, 1), _
        LookAt:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False)

    If celda Is Nothing Then
        UltimaFilaConDatos = 0
    Else
        UltimaFilaConDatos = celda.Row
    End If

End Function

Private Function UltimaColumnaConDatos( _
    ByVal ws As Worksheet) As Long

    Dim celda As Range

    Set celda = ws.Cells.Find( _
        What:="*", _
        After:=ws.Cells(1, 1), _
        LookAt:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False)

    If celda Is Nothing Then
        UltimaColumnaConDatos = 0
    Else
        UltimaColumnaConDatos = celda.Column
    End If

End Function

Private Function SiguienteFilaDisponible( _
    ByVal ws As Worksheet, _
    ByVal columnaControl As Long, _
    ByVal primeraFilaDatos As Long) As Long

    Dim ultimaFila As Long

    ultimaFila = ws.Cells( _
        ws.Rows.Count, columnaControl).End(xlUp).Row

    If ultimaFila < primeraFilaDatos Then
        SiguienteFilaDisponible = primeraFilaDatos
    Else
        SiguienteFilaDisponible = ultimaFila + 1
    End If

End Function

Qué debe adaptarse en este ejemplo

• La columna utilizada como clave.
• La estructura y posición de los encabezados.
• El nombre de la hoja de origen.
• Las extensiones de archivo permitidas.
• La forma de construir claves compuestas.
• La acción que debe aplicarse a cada duplicado.
• Las columnas que deben copiarse o acumularse.
• El tratamiento de errores y archivos protegidos.

Errores frecuentes al programar la consolidación

Comparar los valores sin normalizarlos

Dos claves visualmente iguales pueden contener espacios, caracteres invisibles o tipos de dato distintos. La macro puede no detectar la coincidencia aunque el usuario vea los mismos datos.

Utilizar una clave que no es realmente única

El nombre del cliente, la fecha o el importe no suelen ser suficientes por separado. Una clave demasiado genérica provoca falsos duplicados y puede mezclar operaciones diferentes.

Eliminar duplicados sin conservar un registro

Si la macro borra o ignora filas sin crear un historial, después será difícil comprobar qué información se perdió y por qué.

Sumar datos que no deberían acumularse

Dos filas con la misma clave pueden ser versiones alternativas del mismo registro y no movimientos acumulables. Sumar importes en ese caso duplicaría el resultado económico.

No distinguir entre celdas vacías y valores cero

Una celda vacía puede significar dato desconocido, mientras que cero puede ser un valor válido. Convertir ambos casos al mismo resultado altera la información.

Comparar fechas por su texto visible

Las fechas pueden mostrarse como «01/02/2026», «1-feb-2026» o «2026-02-01» y representar el mismo valor. Para construir una clave conviene utilizar su valor interno o transformarlas con un formato fijo.

claveFecha = Format$(CDate(valorFecha), "yyyymmdd")

Suponer que todos los archivos tienen la misma estructura

Una columna desplazada, un encabezado distinto o una hoja renombrada pueden provocar que la macro lea campos incorrectos. Es recomendable validar la estructura de cada archivo antes de procesarlo.

Cómo mejorar el rendimiento de la macro

Cuando se consolidan muchos archivos, el tiempo de ejecución puede aumentar de forma considerable. Algunas prácticas ayudan a mejorar el rendimiento:

• Desactivar temporalmente la actualización de pantalla.
• Desactivar eventos y cálculo automático durante el proceso.
• Leer rangos completos en arrays en lugar de celda por celda.
• Utilizar un diccionario para consultar claves.
• Escribir los resultados en bloques.
• Abrir los archivos de origen como solo lectura.
• Evitar seleccionar o activar hojas y celdas.
• Restaurar siempre la configuración de Excel, incluso si se produce un error.

Para volúmenes elevados, una solución basada en arrays puede ser mucho más rápida que la copia individual de filas. La macro puede cargar el rango de origen en memoria, procesarlo y volcar el resultado completo en una sola operación.

No sacrificar el control por velocidad

Una macro rápida que consolida mal los datos no aporta una mejora real. La prioridad debe ser mantener la integridad de la información. Después pueden optimizarse los puntos que realmente ralentizan el proceso.

Pruebas necesarias antes de utilizarla

Una consolidación con detección de duplicados debe probarse con situaciones controladas antes de utilizarla sobre archivos reales.

Conviene preparar ejemplos que incluyan:

• Dos filas completamente idénticas.
• La misma clave con mayúsculas y minúsculas distintas.
• Claves con espacios iniciales o finales.
• Registros con la clave vacía.
• Claves repetidas con importes diferentes.
• Fechas almacenadas con formatos distintos.
• Códigos numéricos almacenados como texto.
• Dos registros diferentes que podrían confundirse.
• Archivos sin la hoja esperada.
• Archivos vacíos o protegidos.
• Una ejecución repetida sobre el mismo conjunto de archivos.

La última prueba es especialmente importante. Si la macro se ejecuta dos veces, debe saberse si volverá a insertar los registros, si detectará que ya existen o si limpiará previamente la hoja consolidada.

Conclusión

Evitar duplicados al consolidar archivos con VBA requiere algo más que comparar filas y borrar repeticiones. El punto central es definir una clave fiable, normalizar los datos y establecer qué significa realmente que dos registros sean iguales.

A partir de esa definición, el objeto Dictionary permite detectar coincidencias de forma eficiente. Sin embargo, la macro también debe decidir cómo actuar: conservar una versión, sustituirla, sumar valores, mantener ambas o registrar el caso para revisión.

En una pequeña empresa, una consolidación bien diseñada reduce tareas manuales, evita errores y mejora la trazabilidad. Una solución mal definida puede producir el efecto contrario: eliminar información válida, duplicar importes o mezclar registros que no deberían agruparse.

Por ello, antes de programar conviene documentar los campos clave, las reglas de comparación, el tratamiento de excepciones y el resultado esperado. Esa definición funcional es tan importante como el propio código VBA.

Preguntas frecuentes