VBA-大型数据集的对帐 [英] VBA - Reconciliation of Large datasets

问题描述

我目前有两个文件，行数超过50k.它们都包括一个订购代码.在一个将是Right("AQ column"，7)的文件中，另一个将在C列中具有该文件.我想匹配一个订单代码，并比较值是否相同.另外，在第二个文件中，应该在另一列中进行检查，以查看是否存在匹配"SETTLED"的字符串，因为该文件中存在重复的订单代码.

I currently have two files with 50k+ rows. They both include an order code. In one file that would be Right("AQ column", 7) and the other one would have it in column C. I would like to match an order code, and compare if the values are the same. Also, in the second file, there should be a check in a different column to see if there is a string matching "SETTLED" as there are duplicate order codes in that file.

我尝试了几种方法，下面将一种效率不高的方法粘贴到下面，以使我想解决的问题变得清楚.revnW和Wpay是两种不同的工作簿

I have tried a few approaches, Ill paste a not so efficient one below, in order for what I am trying to fix to be clear. revnW and Wpay are the 2 different workbooks

DesLRow = revnW.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
SrcLRow = wPay.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 to DesLrow
 for j = 3 to srcLrow
  If right(revnW.Sheets(1).Cells(i,43).text, 7) = wPay.Sheets(1).Cells(j,3).text And_
 Instr(wPay.Sheets(1).Cells(j,5).text, "SETTLED") And value <> value 2 (pseudo code) Then


Do stuff (get transaction number, and some other things)
      exit for   
      next j
Next i

我知道这段代码效率不高，我曾尝试将列加载到数组中，但是那时我只能比较一个列，而不能比较值等.

I know this code is not efficient, I have tried to load the columns into an array, but then I could only compare one column, and not the values etc..

任何帮助将不胜感激.

推荐答案

未经测试，但应该为您提供一个起点:

Untested but should give you a starting point:

Sub Tester()

    Dim desLRow As Long, srcLRow As Long
    Dim dictDest As Object, dictSrc As Object

    With revnW.Sheets(1)
       Set dictDest = RowMap(.Range(.Cells(2, 43), .Cells(.Rows.Count, 43).End(xlUp)), 7)
    End With
    With wPay.Sheets(1)
       Set dictSrc = RowMap(.Range(.Cells(3, 3), .Cells(.Rows.Count, 3).End(xlUp)))
    End With

    For Each k In dictDest.keys
        If dictSrc.exists(k) Then

            'do the rest of your checks here....

            Debug.Print "Match between Dest " & dictDest(k).Address & " and " & _
                        dictSrc(k).Address
        End If
    Next k

End Sub

这是地图"功能:

'Get a "map" of row keys to the
'    rows where they are located (just maps the first cell in each row)
' "rng" is the range to be mapped
' "numright" -  pass a number if you just want a part of the value to be mapped
Function RowMap(rng As Range, Optional numRight As Long = 0)
    Dim rv, nr As Long, nc As Long, r As Long, c As Long
    Dim k, data

    Set rv = CreateObject("scripting.dictionary")

    data = rng.Value
    For r = 1 To UBound(data, 1)
        k = data(r, 1)
        If numRight > 0 Then k = Right(k, numRight)
        If rv.exists(k) Then
            Set rv(k) = Application.Union(rv(k), rng.Columns(1).Cells(r))
        Else
            rv.Add k, rng.Columns(1).Cells(r)
        End If
    Next r
    Set RowMap = rv
End Function

这篇关于VBA-大型数据集的对帐的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！