VBA - Match Checks to Bank

[dustindupalo]

Hello,

I am trying to match checks that hit the bank to checks in our accounting system by using the dollar amounts using a macro.
I need it to:
-Copy columns 1-10 in Sheet1 (Bank) and past it into Sheet3 corresponding columns.
-It should do a lookup to the "Debit Amt" from Sheet2 (Accounting system), returning the first 5 columns to Sheet3 columns 13-17. (only if the dollar amounts match)
-It should skip the ones if the dollar amounts don't match.

Bank Columns

[TABLE="width: 1025"]
<tbody>[TR]
[TD="class: xl217, width: 71"]As-Of Date[/TD]
[TD="class: xl217, width: 106"]IBAN[/TD]
[TD="class: xl217, width: 106"]BAI Type Code[/TD]
[TD="class: xl217, width: 106"]Tran Desc[/TD]
[TD="class: xl217, width: 106"]Debit Amt[/TD]
[TD="class: xl217, width: 106"]Credit Amt[/TD]
[TD="class: xl217, width: 106"]Customer Ref No[/TD]
[TD="class: xl217, width: 106"]Location[/TD]
[TD="class: xl217, width: 106"]Bank Reference[/TD]
[TD="class: xl217, width: 106"]Descriptive Text 1[/TD]
[/TR]
</tbody>[/TABLE]

Accounting System Columns

[TABLE="width: 522"]
<tbody>[TR]
[TD="class: xl216, width: 64"]Invoice #[/TD]
[TD="class: xl216, width: 64"]Cust #[/TD]
[TD="class: xl216, width: 255"]Customer[/TD]
[TD="class: xl216, width: 75"]Date[/TD]
[TD="class: xl216, width: 64"]Amt[/TD]
[/TR]
</tbody>[/TABLE]


If this is not do-able, any suggestions would be appreciated! I have seen other possibilities, but this seems like the best solution for me.

 

[sarfaraz78615]

Hello,

I am trying to match checks that hit the bank to checks in our accounting system by using the dollar amounts using a macro.
I need it to:
-Copy columns 1-10 in Sheet1 (Bank) and past it into Sheet3 corresponding columns.
-It should do a lookup to the "Debit Amt" from Sheet2 (Accounting system), returning the first 5 columns to Sheet3 columns 13-17. (only if the dollar amounts match)
-It should skip the ones if the dollar amounts don't match.

Bank Columns

[TABLE="width: 1025"]
<tbody>[TR]
[TD="class: xl217, width: 71"]As-Of Date[/TD]
[TD="class: xl217, width: 106"]IBAN[/TD]
[TD="class: xl217, width: 106"]BAI Type Code[/TD]
[TD="class: xl217, width: 106"]Tran Desc[/TD]
[TD="class: xl217, width: 106"]Debit Amt[/TD]
[TD="class: xl217, width: 106"]Credit Amt[/TD]
[TD="class: xl217, width: 106"]Customer Ref No[/TD]
[TD="class: xl217, width: 106"]Location[/TD]
[TD="class: xl217, width: 106"]Bank Reference[/TD]
[TD="class: xl217, width: 106"]Descriptive Text 1[/TD]
[/TR]
</tbody>[/TABLE]

Accounting System Columns

[TABLE="width: 522"]
<tbody>[TR]
[TD="class: xl216, width: 64"]Invoice #[/TD]
[TD="class: xl216, width: 64"]Cust #[/TD]
[TD="class: xl216, width: 255"]Customer[/TD]
[TD="class: xl216, width: 75"]Date[/TD]
[TD="class: xl216, width: 64"]Amt[/TD]
[/TR]
</tbody>[/TABLE]


If this is not do-able, any suggestions would be appreciated! I have seen other possibilities, but this seems like the best solution for me.

Actually i am not expert in VBA but i would like use the formula to get this done ..

As i can see u wanted to merge two sheets column in Sheet3.

so her is the formula.
1st add column to create the Unique ID in both the sheet and then is us the vlookup in sheet 3 by finding lookup_value with Unique ID.


If you still getting the problem then u can call me on 9967006510- Sarfaraz