Блог

Я хочу создать инструмент, который будет согласовывать значения в 3 электронных таблицах. Например, Лист1: ID = 123A, страна = ИРЛАНДИЯ; Лист2: ID = 123A, СТРАНА = ВЕЛИКОБРИТАНИЯ; Лист3: ID = 123A, СТРАНА = ВЕЛИКОБРИТАНИЯ. Если он не совпадает, мы получим значение FALSE.

В настоящее время у меня есть возможность согласовать 2 файла в столбцах B и C в shtRecon, и я хочу добавить 3-е значение в столбец D, которое будет согласовывать 3 файла вместо 2.

Другими словами, когда столбец B = столбец C, возникает 0 ошибок, когда столбец B <> столбец C, возникает 1 ошибка. Я бы хотел, чтобы вместо этого это был столбец B = столбец C = столбец D. Я включил свой рабочий код для согласования 2 столбцов ниже. Есть идеи?

 Sub ReconcileVenueRef()
                                           
    Dim sht1 As Worksheet
    Dim sht2 As Worksheet
    Dim shtRecon As Worksheet
    Dim shtSummary As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range
    Dim cel As Range
    Dim oDict As Object
    Dim reconRow As Long
    Dim noOfErrors As Long
    Dim ETL As Variant
    Dim MiFID As Variant
    Dim Match As Boolean
    
    Set sht1 = Worksheets("MiFID Export")
    Set sht2 = Worksheets("ETL")
    Set shtRecon = Worksheets("Reconciliation")
    Set shtSummary = Worksheets("Summary")

    'Define the columns that you need, and find the last row
    Set rng1 = sht1.Range("A2:B" amp; sht1.Range("A" amp; sht1.Rows.Count).End(xlUp).Row)
    Set rng2 = sht2.Range("A2:B" amp; sht2.Range("A" amp; sht2.Rows.Count).End(xlUp).Row)
    
    'Get a dictionary object holding unique values from sheet 2
    'In my case, column A holds the Id, and column B holds the value
    Set oDict = GetDictionary(rng2, 1, 4)
    
    reconRow = 0
    
    'Loop over each cel in sheet 1, column 1
    'Look for the value in column 1 in the sheet 2 dictionary
    'object, and then reconcile
    For Each cel In Intersect(rng1, sht1.Columns(1))
        'Get the next avail row in reconciliation sheet
        reconRow = reconRow   1
        shtRecon.Range("A" amp; reconRow).Value = cel.Value
        
        'Recon column B holds value from sheet 1, column B
        shtRecon.Range("B" amp; reconRow).Value = cel.Offset(, 3).Value
        
        
        'If Id is found in Sheet B dictionary, then take the value
        'otherwise, write "blank" in column C
        'ETL spreadsheet
        If oDict.exists(cel.Value) Then
            shtRecon.Range("C" amp; reconRow).Value = oDict(cel.Value)
        Else
            shtRecon.Range("C" amp; reconRow).Value = "BLANK"
        End If
    Next cel
    

   'If ETL cell = MiFID cell then noOfErrors is 0, otherwise it's 1
    noOfErrors = 0
    
    ETL = shtRecon.Cells(1, 3).Value
    MiFID = shtRecon.Cells(1, 2).Value
    
    If ETL = MiFID Then
    Match = True
    shtRecon.Cells(1, 4).Value = Match
    shtSummary.Cells(4, 3).Value = noOfErrors

    ElseIf ETL <> MiFID Then
    Match = False
    shtRecon.Cells(1, 4).Value = Match
    noOfErrors = noOfErrors   1
    shtSummary.Cells(4, 3).Value = noOfErrors

    End If
    
    End Sub





    'Function stores a range into a dictionary
    'Param inputRange takes a range to be stored
    'Param idColumn takes the column of the range to be used as the ID
    'e.g. if idColumn = 2, and inputRange("C1:F10"), then column D is used for ID
    'Param valueColumn points to the column in range used for the value
    Function GetDictionary(inputRange As Range, idColumn As Long, valueColumn As Long) As Object
        Dim oDict As Object
        Dim sht As Worksheet
        Dim cel As Range
        
        Set oDict = CreateObject("Scripting.Dictionary")
        Set sht = inputRange.Parent
        
        For Each cel In Intersect(inputRange, inputRange.Columns(idColumn))
            If Not oDict.exists(cel.Value) Then
                oDict.Add cel.Value, sht.Cells(cel.Row, valueColumn).Value
            End If
        Next cel
        
        Set GetDictionary = oDict
    End Function