Vijay
 

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

Vijay
 

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

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

Vijay
 

It is meaningless to check amounts as there may be multiple same amount,
same as date.
Anyway, you can adjust the code to suit.

Regards

"vijaydsk1970" wrote in message
...
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

Vijay
 

Dear KC,
This is vijay once again.
Many thanks for your macro.
Here what i am asking is, the macro should compare both doc no + amt (at a
time) with check no + amt in the GL sheet.

As i am a newbie i don't know how to adjust the macro.
A detailed discription is may be greatly appreciated.

Thanks once again


"KC" wrote:

It is meaningless to check amounts as there may be multiple same amount,
same as date.
Anyway, you can adjust the code to suit.

Regards

"vijaydsk1970" wrote in message
...
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

Vijay
 

Dear KC,
This is vijay once again.
Many thanks for your macro.
Here what i am asking is, the macro should compare both doc no + amt (at a
time) with check no + amt in the GL sheet.

As i am a newbie i don't know how to adjust the macro.
A detailed discription is may be greatly appreciated.

Thanks once again


"vijaydsk1970" wrote:

Dear KC,
This is vijay once again.
Many thanks for your macro.
Here what i am asking is, the macro should compare both doc no + amt (at a
time) with check no + amt in the GL sheet.

As i am a newbie i don't know how to adjust the macro.
A detailed discription is may be greatly appreciated.

Thanks once again


"KC" wrote:

It is meaningless to check amounts as there may be multiple same amount,
same as date.
Anyway, you can adjust the code to suit.

Regards

"vijaydsk1970" wrote in message
...
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