Vijay
Dear PY & Associates,
The suggestion what you gave me is really working well.
One another query, the code what you gave me is searching on one column
(Chq. No and Doc No.), if any thing is not matched, then it should check up
by amount and even after that if any not matched are there, it should check
up by date.
Help me out.
Thanks once again
"PY & Associates" wrote:
We assume you want to match check Nr with document Nr
First sheet is named Bank
Second sheet is named GL
Remarks is in column A on both sheets
Unmatched only appears in sheet Bank
We do not understand why a matched item should repeat in sheet Bank
See if this is close.
Adjust to suit your sheet layout.
Sub m()
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:D" & 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
--
Regards
"vijaydsk1970" wrote in message
...
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.
Thanks in advance for you guys who are ready to help me
|