Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have two very large excel files with financial data. I need to combine the data from one file with the data of the other. All lines in the first file have a category code assigned. Some lines in the second file might have the same code.
I need to combine all lines from the first file with all matching lines with the same code from the second file. The files have a different number of columns.

How should I tackle this?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

First, add some columns to the file that needs it to line the data up, then cut and paste the data from the smallest to the largest file, then sort by your category code.

Here's one way to do it in VBA. This code will only copy if the cell holding the NACE value is the same, but you can modify for your needs. Right now it just copys the entire row to the first workbook.

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

This code needs to go into a module to support the main routine:

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

The Utility code was found here and here.

share|improve this answer
    
Clever. This works, but it gets unwieldy if this has to be done a lot of times. I have to do this same trick a lot of times, so I'm looking for a method to semi-automate this. Do you have any ideas for that? –  JackStoneS Oct 9 '10 at 21:59
    
@JackStoneS, I think it could be done easy in VBA, give me some details on the layout. –  Lance Roberts Oct 10 '10 at 0:17
    
Thanks for your reply! Both files contain financial data. The smaller file has a list of Initial Public Offerings, with some data of the companies doing this. For me, the most important part of the file is the cell with NACE codes. This is a code for a particular industry, and multiple codes are listed per cell. I want to find all kinds of industry data for all companies with the same code in the second file. This could be: sales from years 2000-2010 or assets from those years. I want to do this for every company in the first file. Does this clarify the situation? –  JackStoneS Oct 11 '10 at 14:34
    
@Jack, Are you having to extract your code from the NACE cell, or can you just compare against the whole cell? So you're saying that at different times you'll want to get different columns from the second file? –  Lance Roberts Oct 11 '10 at 15:03
    
@Lance, I have to extract the codes, but that's not the hard part. All codes (if more than one) are separated by ' / ', so a combination of FIND and LEFT (or MID) can extract the appropriate code. Right now I'm working with a second file of 28 columns, but the exact amount may differ. I'd need all non-empty columns every time. The file is a dump from a database I don't have access to. I don't have to extract codes from the second file. –  JackStoneS Oct 11 '10 at 15:39

This kind of task is what microsoft access is meant for and is called a "Left Join". But you can still do this in excel using a vlookup or using the match and the index function. Personally I prefer match/index.

Suppose Sheet1 A:F is the first file and you put the second file onto Sheet2 A1:Q500. Lets say your codes are in column A of both. Then on sheet1 in G2 type this:

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

Then in H2 type:

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

Then drag this across, and drag all of these down.

share|improve this answer
    
This is really useful, thanks, but this only matches one line. For every line in the first sheet, there might be more than one match in the second file. Is it possible to do this, with this method, or should I resort to access after all? –  JackStoneS Oct 9 '10 at 21:54
    
@JackStoneS: In order to do a multi-match you're going to have to do something more similar to Lance's response... after which manually shifting the rows to match or using a macro to do it. Using access is probably your best solution though. –  Jarvin Oct 11 '10 at 5:32
    
+1 for suggesting using access. any database you can link these excel files into would give you the benefit lots of querying options –  datatoo Oct 12 '10 at 17:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.