Tengo dos archivos excel muy grandes con datos financieros. Necesito combinar los datos de un archivo con los datos del otro. Todas las líneas del primer archivo tienen asignado un código de categoría. Algunas líneas del segundo archivo pueden tener el mismo código.
Necesito combinar todas las líneas del primer archivo con todas las líneas coincidentes con el mismo código del segundo archivo. Los archivos tienen un número diferente de columnas.
¿Cómo debería abordar esto?
Respuesta1
Primero, agregue algunas columnas al archivo que las necesita para alinear los datos, luego corte y pegue los datos del archivo más pequeño al más grande, luego ordénelos por su código de categoría.
Aquí hay una forma de hacerlo en VBA. Este código solo se copiará si la celda que contiene el valor NACE es la misma, pero puede modificarlo según sus necesidades. En este momento simplemente copia la fila completa en el primer libro.
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 debe ir a un módulo para admitir la rutina 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
Respuesta2
Este tipo de tarea es para lo que está diseñado Microsoft Access y se denomina "Unión izquierda". Pero aún puedes hacer esto en Excel usando una búsqueda virtual o usando la función de coincidencia e índice. Personalmente prefiero coincidir/índice.
Supongamos que la Hoja 1 A:F es el primer archivo y coloca el segundo archivo en la Hoja 2 A1:Q500. Digamos que sus códigos están en la columna A de ambos. Luego, en la hoja 1 en G2, escriba esto:
=MATCH(A2,Sheet2!A$1:A$500,0)
Luego en H2 escriba:
=INDEX(Sheet2!B$1:B$500,$G2)
Luego arrastre esto y arrastre todos hacia abajo.
Respuesta3
Dependiendo del tamaño de los 2 archivos, también puedes intentar usar Consulta desde archivos de Excel:
- Definir nombre para la primera tabla de Excel (pestaña Fórmulas -> Definir nombre)
- Definir nombre para la segunda tabla de Excel
- Vaya a la pestaña Datos, seleccione "De otras fuentes" y, en el menú desplegable, seleccione "De Microsoft Query".
- Seleccione el archivo de su libro de trabajo y confirme que desea fusionar las columnas manualmente
- En la siguiente ventana "Consulta desde archivos de Excel", arrastre y suelte la primera columna de la primera tabla en la primera columna de la segunda tabla; se creará un vínculo entre estas columnas.
- Vaya al menú Archivo, haga clic en "Devolver datos a MS Office Excel", aparecerá un cuadro de diálogo Importar datos
- Seleccione la hoja a la que desea importar los datos coincidentes.
- Haga clic en Aceptar -> debería ver datos coincidentes con columnas de ambas tablas