Scripting Dictionary and matching values in offset cell

[Elliottj2121]

Hello

I am looking for some help with a scripting dictionary issue. What is going on is in Column F are posted check amounts to a database and Column H are the actual check values. I am trying to automate finding errors in posting and/or errors in the check writing. The image is how I would like the end result to look. For example, the posted amount in cell(5, 6) is three dollars short to its corresponding check in cell(11, 8) the column offset value is "No Match". The same goes for the posted amount in cell(12, 6), this check was posted to the database but no check was collected. Column H are values that are manually entered, Column F is the last column in a data set copied over from another spreadsheet. Any help is greatly appreciated! Thank you!!!

Sub VerifyChecks(wkb As Workbook)
    Dim Ws As Worksheet
    Dim CkValues As Scripting.Dictionary
    Dim R As Long, i As Long
    Dim vlr As Variant
    vlr = LastRow(Ws)
    Set Ws = wkb.Worksheets(1)
    If vlr > 0 Then
        Set CkValues = New Dictionary
            For R = 2 To vlr
        If Not CkValues.Exists(Ws.Cells(R, 8).Value) Then
        CkValues.Add CStr(Ws.Cells(R, 8).Value), R
        End If
       Next R
      End If
    If vlr > 0 Then
        For R = 2 To vlr
            If CkValues.Exists(CStr(Ws.Cells(R, 6).Value)) Then
            i = CkValues(CStr(Ws.Cells(R, 6).Value))
            Ws.Range("H" & R).Copy Ws.Cells(i, 7)
        Else
        Ws.Cells(i, 7).Offset(0, 1).Value = ""
        End If
    Next R
    End If
End Sub

Function LastRow(sh As Worksheet) As Variant
  On Error Resume Next
  LastRow = sh.Cells.Find(What:="*", _
                          After:=sh.Range("A1"), _
                          lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
  On Error GoTo 0
End Function

 

[DanteAmor]

For example, the posted amount in cell(5, 6) is three dollars short to its corresponding check in cell(11, 8) the column offset value is "No Match".

I see that the values are the same.

And if you just use the Vlookup formula:

Dante Amor
	F	G	H
1	Posted	Verification	Enter
2	26,614.59	26,614.59	22,640.82
3	25,276.19	25,276.19	18,405.55
4	23,909.01	23,909.01	17,953.68
5	23,806.36	23,806.36	15,756.63
6	22,640.82	22,640.82	11,057.86
7	18,405.55	18,405.55	15,756.63
8	17,953.68	17,953.68	26,614.59
9	15,756.63	15,756.63	25,276.19
10	15,756.63	15,756.63	23,909.01
11	11,057.86	11,057.86	23,806.36
12	10,696.72	No Match
Hoja6

Cell Formulas
Range	Formula
G2:G12	G2	=IFERROR(VLOOKUP(F2,$H$2:$H$11,1,0),"No Match")

 

[DanteAmor]

I can observe that you have duplicate values, so vlookup would not work.
I show you how it would be with Dictionary, to even search for duplicates, if you have 2 equal values in column F, it means that you should have 2 equal values in column H. If you have 2 in F and only 1 in H. Then one of the F values must be "No Match".
Try this:

Sub check_values()
  Dim a As Variant
  Dim i As Long, lr As Long
  Dim dic As Object
  
  Set dic = CreateObject("Scripting.Dictionary")
  lr = Sheets(1).Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
  a = Range("F2:H" & lr).Value
  
  For i = 1 To UBound(a)
    If a(i, 3) <> "" Then dic(a(i, 3)) = dic(a(i, 3)) + 1
  Next
  
  For i = 1 To UBound(a)
    If dic.exists(a(i, 1)) And dic(a(i, 1)) > 0 Then
      a(i, 2) = a(i, 1)
      dic(a(i, 1)) = dic(a(i, 1)) - 1
    Else
      a(i, 2) = "No Match"
    End If
  Next
  Range("G2").Resize(UBound(a, 1)).Value = Application.Index(a, , 2)
End Sub

 

[DanteAmor]

The following formula works.
I put some duplicates.

Dante Amor
	F	G	H
1	Posted	Verification	Enter
2	26,614.59	26614.59	22,640.82
3	25,276.19	25276.19	18,405.55
4	23,909.01	23909.01	17,953.68
5	23,806.36	No Match	15,756.63
6	22,640.82	22640.82	11,057.86
7	18,405.55	18405.55	15,756.63
8	17,953.68	17953.68	26,614.59
9	15,756.63	15756.63	25,276.19
10	15,756.63	15756.63	23,909.01
11	15,756.63	No Match	23,803.36
12	10,696.72	No Match
Hoja6

Cell Formulas
Range	Formula
G2:G12	G2	=IF(COUNTIFS($F$2:F2,F2)<=COUNTIF($H$2:$H$11,F2),F2,"No Match")

 

[Elliottj2121]

View attachment 43822

I can observe that you have duplicate values, so vlookup would not work.
I show you how it would be with Dictionary, to even search for duplicates, if you have 2 equal values in column F, it means that you should have 2 equal values in column H. If you have 2 in F and only 1 in H. Then one of the F values must be "No Match".
Try this:

Sub check_values()
  Dim a As Variant
  Dim i As Long, lr As Long
  Dim dic As Object
 
  Set dic = CreateObject("Scripting.Dictionary")
  lr = Sheets(1).Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
  a = Range("F2:H" & lr).Value
 
  For i = 1 To UBound(a)
    If a(i, 3) <> "" Then dic(a(i, 3)) = dic(a(i, 3)) + 1
  Next
 
  For i = 1 To UBound(a)
    If dic.exists(a(i, 1)) And dic(a(i, 1)) > 0 Then
      a(i, 2) = a(i, 1)
      dic(a(i, 1)) = dic(a(i, 1)) - 1
    Else
      a(i, 2) = "No Match"
    End If
  Next
  Range("G2").Resize(UBound(a, 1)).Value = Application.Index(a, , 2)
End Sub

Thank you so much! this worked perfectly!!!!!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Peter_SSs]

Perhaps you have a particular reason for using the dictionary object for this task but you could avoid any of that looping by utilising Dante's formula concept, just applying it via vba.

Sub CkeckValues()
  With Sheets(1)
    With .Range("G2:G" & .Range("F" & Rows.Count).End(xlUp).Row)
      .Formula = "=IF(COUNTIFS($F$2:F2,F2)<=COUNTIF($H$2:$H$" & .Parent.Range("H" & Rows.Count).End(xlUp).Row & ",F2),F2,""No Match"")"
      .Value = .Value
    End With
  End With
End Sub

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.