Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vijay 2
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: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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vijay 2
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" 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. 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: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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vijay 2
Thanks once again kc
"vijaydsk1970" wrote: 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: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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vijay | Excel Worksheet Functions | |||
Vijay 1 | Excel Worksheet Functions | |||
Check Box Not working? | New Users to Excel |