Vijay 2

[vijaydsk1970]

I have 2 excell sheets once contains the payments made by my bank basing on
the
cheque no and amount another contains (general ledger / gl)documnet no and
amount.
Both the sheets are having more than 20 columns and 20000 rows. Here i want
a formula
which should match contents of the bank statement (from top to bottom) with
general ledger on doc no and amount, if it matches it should put remark as
"Matched" in both the sheets, if it not matches as "Unmatched". If one entry
in bank statement is already matched with general ledger and if it repeats
again, it should show "already matched". Like wise it should run
through out the sheet.

Here what i am asking is, the macro/formula should compare both doc no + amt
(at a
time) with check no + amt in the GL sheet.

Thanks in advance for you guys who are ready to help me

PS: May the following will help you as the following is resolving only one
condition
(checking the chq. no in bank sheet with doc no in gl sheet)
Sub matching()
Dim wsbank As Worksheet
Dim wsgl As Worksheet
Dim rngsrc As Range
Dim rngtarget As Range
Dim rngmatch as Range
Dim c As Range
Dim lrow As Long

Set wsbank = Sheets("Bank")
lrow = wsbank.Cells(Rows.Count, "D").End(xlUp).Row
Set rngsrc = wsbank.Range("D2" & lrow)

Set wsgl = Sheets("GL")
lrow = wsgl.Cells(Rows.Count, "E").End(xlUp).Row
Set rngtarget = wsgl.Range("E2:E" & lrow)

On Error Resume Next
For Each c In rngsrc
Set rngmatch = Nothing
Set rngmatch = rngtarget.Find(c)
If rngmatch Is Nothing Then
wsbank.Cells(c.Row, "A") = "Not matched"
Else
wsbank.Cells(c.Row, "A") = "Matched"
wsgl.Cells(rngmatch.Row, "A") = "Matched"
End If
Next c

On Error GoTo 0
End Sub

 

[KC]

You can concatenate the two and use this new cell to compare.

Doing this appears contradicting the original requirement that "If one entry
in bank statement is already matched with general ledger and if it repeats
again, it should show "already matched". If the same doc nr + different
amount (wrong? which one is correct now?), they are unique and so "already
matched" does not apply anymore.

Cheers!

 

[vijaydsk1970]

Thanks once again kc