☰

Como mesclar dados de dois arquivos Excel estruturados de forma diferente?

tag-icon • tag-icon microsoft-excel vba
Como mesclar dados de dois arquivos Excel estruturados de forma diferente?

Tenho dois arquivos Excel muito grandes com dados financeiros. Preciso combinar os dados de um arquivo com os dados do outro. Todas as linhas do primeiro arquivo possuem um código de categoria atribuído. Algumas linhas do segundo arquivo podem ter o mesmo código.
Preciso combinar todas as linhas do primeiro arquivo com todas as linhas correspondentes com o mesmo código do segundo arquivo. Os arquivos possuem um número diferente de colunas.

Como devo lidar com isso?

Responder1

Primeiro, adicione algumas colunas ao arquivo que precisa para alinhar os dados, depois recorte e cole os dados do menor para o maior arquivo e, em seguida, classifique pelo código da categoria.

Aqui está uma maneira de fazer isso no VBA. Este código só será copiado se a célula que contém o valor NACE for a mesma, mas você pode modificá-lo de acordo com suas necessidades. No momento, ele apenas copia a linha inteira para a primeira pasta de trabalho.

Private Sub CopyRows()

Dim FirstSheet As Range
Dim SecondSheet As Range
Dim s1col As Integer, s2col As Integer
Dim nextrow As Integer, secondendrow As Integer
Dim copyrow As Range, col As Range
Dim firstsheetrow As Range, secondsheetrow As Range
Dim NACE() As String, Limit As Integer, Index As Integer
Dim testrange As Range

Set FirstSheet = ActiveSheet.UsedRange
Set SecondSheet = Workbooks("Book2").Sheets("Sheet1").UsedRange

For Each col In FirstSheet.Columns
    If Not col.Cells(1).Find("NACE") Is Nothing Then
        s1col = col.Column
        Exit For
    End If
Next col

For Each col In SecondSheet.Columns
    If Not col.Cells(1).Find("NACE") Is Nothing Then
        s2col = col.Column
        Exit For
    End If
Next col


''//Fill NACE array with distinct entries from first sheet
nextrow = FirstSheet.Rows.Count + 1

ReDim Preserve NACE(1 To 1)
NACE(1) = FirstSheet.Rows(2).Cells(1, s1col).Value

For Each firstsheetrow In FirstSheet.Range("3:" & nextrow - 1).Rows
    Limit = UBound(NACE)
    If instrArray(NACE, firstsheetrow.Cells(1, s1col).Value) = 0 Then
        ReDim Preserve NACE(1 To Limit + 1)
        NACE(Limit + 1) = firstsheetrow.Cells(1, s1col).Value
    End If
Next firstsheetrow

''//Copy lines from second sheet that match a NACE value on the first sheet
secondendrow = SecondSheet.Rows.Count

For Each secondsheetrow In SecondSheet.Range("2:" & secondendrow).Rows
    Index = instrArray(NACE, secondsheetrow.Cells(1, s2col).Value)
    If Index > 0 Then
        secondsheetrow.Copy
        ActiveSheet.Rows(nextrow).PasteSpecial (xlPasteValues)
    End If
Next secondsheetrow

End Sub

Este código precisa entrar em um módulo para suportar a rotina principal:

Public Declare Sub CopyMemory Lib "kernel32" _
   Alias "RtlMoveMemory" _
  (pDest As Any, _
   pSrc As Any, _
   ByVal ByteLen As Long)

Public Function GetArrayDimensions(ByVal arrPtr As Long) As Integer

   Dim address As Long
  'get the address of the SafeArray structure in memory

   CopyMemory address, ByVal arrPtr, ByVal 4

  'if there is a dimension, then
  'address will point to the memory
  'address of the array, otherwise
  'the array isn't dimensioned
   If address <> 0 Then

     'fill the local variable with the first 2
     'bytes of the safearray structure. These
     'first 2 bytes contain an integer describing
     'the number of dimensions
      CopyMemory GetArrayDimensions, ByVal address, 2

   End If

End Function

Public Function VarPtrArray(arr As Variant) As Long

  'Function to get pointer to the array
   CopyMemory VarPtrArray, ByVal VarPtr(arr) + 8, ByVal 4

End Function

Function instrArray(strArray, strWanted, _
    Optional CaseCrit As Boolean = False, _
    Optional FirstOnly As Boolean = True, _
    Optional Location As String = "exact") As Long
     '
     '****************************************************************************************
     '       Title       instrArray
     '       Target Application:  any
     '       Function:   searches string array for some "wanted" text
     '       Limitations:
     '       Passed Values:
     '           strArray    [in, string array]  array to be searched
     '           strWanted   [in, string]  text for which strArray is searched
     '           CaseCrit    [in, Boolean, Optional]
     '               if true, case (upper/lower) of each character is critical and must match
     '               if false, case is not critical {default}
     '           FirstOnly   [in, Boolean, Optional]
     '               if true, proc exits after first instance is found {default}
     '               if false, proc search to end of array and last instance # is returned
     '           Location    [in, string, Optional] text matching constraint:
     '               = "any"     as long as strWanted is found anywhere in strArray(k),i.e.,
     '                               instr(strArray(k),strWanted) > 0, then instrArray = K
     '               = "left"    match is successful only if
     '                               Left(strArray(K),Len(strWanted) = StrWanted
     '               = "right"    match is successful only if
     '                               Right(strArray(K),Len(strWanted) = StrWanted
     '               = "exact"    match is successful only if
     '                               strArray(K) = StrWanted       {default}
     '
     '****************************************************************************************
     '
     '
    Dim I       As Long
    Dim Locn    As String
    Dim strA    As String
    Dim strB    As String

    instrArray = 0
    Locn = LCase(Location)
    Select Case FirstOnly
        Case True
            For I = LBound(strArray) To UBound(strArray)
                Select Case CaseCrit
                Case True
                    strA = strArray(I):     strB = strWanted
                Case False
                    strA = LCase(strArray(I)):  strB = LCase(strWanted)
                End Select
                If instrArray2(Locn, strA, strB) > 0 Then
                    instrArray = I
                    Exit Function
                End If
            Next I
        Case False
            For I = UBound(strArray) To LBound(strArray) Step -1
                Select Case CaseCrit
                Case True
                    strA = strArray(I):     strB = strWanted
                Case False
                    strA = LCase(strArray(I)):  strB = LCase(strWanted)
                End Select
                If instrArray2(Locn, strA, strB) > 0 Then
                    instrArray = I
                    Exit Function
                End If
            Next I
    End Select

End Function

Function instrArray2(Locn, strA, strB)
     '
     '****************************************************************************************
     '       Title       instrArray2
     '       Target Application:  any
     '       Function    called by instrArray to complete test of strB in strA
     '       Limitations:    NONE
     '       Passed Values:
     '           Locn    [input, string] text matching constraint (see instrArray)
     '           strA    [input, string] 1st character string
     '           strB    [input, string] 2nd character string
     '
     '****************************************************************************************
     '
     '

    Select Case Locn
    Case "any"
        instrArray2 = InStr(strA, strB)
    Case "left"
        If Left(strA, Len(strB)) = strB Then instrArray2 = 1
    Case "right"
        If Right(strA, Len(strB)) = strB Then instrArray2 = 1
    Case "exact"
        If strA = strB Then instrArray2 = 1
    Case Else
    End Select

End Function

O código do utilitário foi encontradoaquieaqui.

Responder2

Esse tipo de tarefa é o objetivo do Microsoft Access e é chamado de "Junção à Esquerda". Mas você ainda pode fazer isso no Excel usando um vlookup ou usando a função match e index. Pessoalmente prefiro correspondência/índice.

Suponha que Sheet1 A:F seja o primeiro arquivo e você coloque o segundo arquivo na Sheet2 A1:Q500. Digamos que seus códigos estejam na coluna A de ambos. Então na planilha1 em G2 digite isto:

=MATCH(A2,Sheet2!A$1:A$500,0)

Então em H2 digite:

=INDEX(Sheet2!B$1:B$500,$G2)

Em seguida, arraste-o e arraste tudo para baixo.

Responder3

Dependendo do tamanho dos 2 arquivos, você também pode tentar usar o Query from Excel Files:

  • Defina o nome da primeira tabela do Excel (guia Fórmulas -> Definir nome)
  • Defina o nome da segunda tabela do Excel
  • Vá para a guia Dados, selecione "De outras fontes" e, no menu suspenso, selecione "Do Microsoft Query"
  • Selecione o arquivo da sua pasta de trabalho e confirme que deseja mesclar as colunas manualmente
  • Na janela seguinte "Consulta de arquivos Excel", arraste e solte a primeira coluna da primeira tabela na primeira coluna da segunda tabela - será criado um link entre essas colunas
  • Vá para o menu Arquivo, clique em "Retornar dados para o MS Office Excel", uma caixa de diálogo Importar dados aparecerá
  • Selecione a planilha para a qual você deseja que os dados correspondentes sejam importados
  • Clique em OK -> você deverá ver os dados correspondentes com colunas de ambas as tabelas

informação relacionada