matching Check # for Bank Reconciliation through Excel

[Guest]

I want to do Bank Reconciliation through Excel. Is there any function or
formula that can help me in matching the check number given in a column of
Data A of Sheet 1 with check number given in a column of Data B of Sheet 2
within the same workbook.

Alternatively, is there any function or formula that can identify the mis
matched check numbers given in the data on two separate worksheets of the
same work book.

I am using Excel 2003.

 

[Guest]

You need to highlight differences in the two columns:

1. checks you may not have entered on your column
2. checks that may not have clear on the bank's column

For a simple example, say we have 10 numbers in column A of both Sheet1 and
Sheet2. In Sheet1 cell B1 enter:

=COUNTIF(Sheet2!$A$1:$A$10,A1) and copy down

1's are matches and 0's are mis-matches (more than 1 is double-entry)

In the same fashion in Sheet2 use

=COUNTIF(Sheet1!$A$1:$A$10,A1)