compare two columns and return matches and differences in another columns

[jsolanki]

can somebody please help with this?

I need a VBA code that will compare check #s in Col A with #s in E. If they match, I need the values to return in col I,J,K. If they don't, I need the values to return in col M,N,O.

Checks Issued Bank Statement Cleared Checks Outstanding checks

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

Check #

Date

Amount

Check #

Date

Amount

Check #

Date

Amount

Check #

Date

Amount

<tbody>
</tbody>

 

[duggie33]

I think more information is needed. Do you only need to compare Check #'s or could the Check #'s be the same with different Dates or Amounts? Do you expect matches to be in the same row or could they be in different rows?

 

[jsolanki]

Dates are going to be different. Check #s and amount will be same. Lets say that a check # in col A matches with check # in col E. I want the see the info from col E,F,G to return in I,J,K. If they don't match, I want the info from col A,B,C return in com M,N,O.

Here is an example.


Checks Issued Bank Statement Cleared Checks Outstanding Checks
Check # Date Amount Check # Date Amount Check # Date Amount Check # Date Amount
740991 12/3/2015 60.00 765260 2/1/2019 6,500.00 740991 12/3/2015 60.00
765260 1/18/2019 6,500.00 765260 2/1/2019 6,500.00 765485 2/1/2019 116.95
765485 2/1/2019 116.95 765485 2/1/2019 116.95


Check #s 765260 and 765485 match with so they will appear in cleared checks column and check 740991 will remain outstanding.

 

[jsolanki]

 

[duggie33]

Give this a shot...

Option Explicit


Sub Update()


    Dim wb As Workbook: Set wb = ActiveWorkbook
    Dim ws As Worksheet: Set ws = wb.ActiveSheet
    Dim rng_ChecksIssued As Range
    Dim rng_ChecksCleared As Range
    Dim rng_Found As Range
    Dim lng_LastRowIssued As Long
    Dim lng_LastRowCleared As Long
    Dim i As Long
    Dim k As Long
    Dim l As Long


    k = 0
    l = 0


    lng_LastRowIssued = ws.Range("A10000").End(xlUp).Row
    lng_LastRowCleared = ws.Range("E10000").End(xlUp).Row


    Set rng_ChecksIssued = ws.Range("A1").Offset(1, 0).Resize(lng_LastRowIssued - 1, 3)
    Set rng_ChecksCleared = ws.Range("E1").Offset(1, 0).Resize(lng_LastRowCleared - 1, 3)


    For i = 1 To lng_LastRowIssued - 1
        If Not rng_ChecksCleared.Columns(1).Find(rng_ChecksIssued.Cells(i, 1).Value) Is Nothing Then
            Set rng_Found = rng_ChecksCleared.Columns(1).Find(rng_ChecksIssued.Cells(i, 1).Value)
            If rng_Found.Offset(0, 2).Value = rng_ChecksIssued.Cells(i, 3).Value Then
                ws.Range("I2").Offset(k, 0).Resize(1, 3).Value = rng_Found.Resize(1, 3).Value
                k = k + 1
            Else
                ws.Range("M2").Offset(l, 0).Resize(1, 3).Value = rng_ChecksIssued.Cells(i, 1).Resize(1, 3).Value
                l = l + 1
            End If
        Else
            ws.Range("M2").Offset(l, 0).Resize(1, 3).Value = rng_ChecksIssued.Cells(i, 1).Resize(1, 3).Value
            l = l + 1
        End If
    Next i
End Sub

 

[jsolanki]

Thank you so much. This works great.

 

[duggie33]

You are welcome.