New accounting year item 18 Method 2 pg 171 172

Method 2: Use AdvanceFilter

We can use an AdvanceFilter operation to identify the bank amounts. We use AdvanceFilter because we want to find rows based on 2 columns (ACCOUNT-DEBIT and ACCOUNT-CREDIT).

1. To use an AdvanceFilter operation, we need to set the criteria in another sheet. The criteria may look like the following:

	A		B
1	ACCOUNT-DEBIT	ACCOUNT-CREDIT
2	BANK A
3			BANK A
4

The above criteria means that we want to select rows which contain item BANK A under the column ACCOUNT DEBIT but the same row can contain any item under the column ACCOUNT-CREDIT, and select also rows which contain the same item (BANK A) under column ACCOUNT-CREDIT but the same row can contain any item under column ACCOUNT-DEBIT.

2. After setting the criteria, go to sheet DATA.

3. At sheet DATA, make sure the active cell is a non-blank cell in the data area.

4. Click menu Data/Filter/Advance Filter to open the Advance Filter dialog box.

5. The List range box is automatically filled with the address of the data area. Check that it is correct; otherwise you have to correct your data area.

171

6. Fill the Criteria range box with the address of the criteria you set above. (To easily fill the box with the address, follow these steps: Make sure the insertion bar is already in the box. Click on the tab of the sheet where the criteria reside. Highlight the criteria block. You will notice that as you highlight the criteria block, the Criteria Range box is automatically filled with the address of the criteria block).

7. Click OK.

8. Now you should see only rows containing transactions of the bank account you set in the criteria block.

9. Zerorise amounts in column RM-DEBIT where the corresponding item in column ACCOUNT-DEBIT is the bank account, and zerorise also amounts in RM-CREDIT where the corresponding items in column ACCOUNT CREDIT is the same bank account. Be careful not to zerorise opening balance and current year amounts.

TIP

If your bank has many transactions, you will take some time to zerorise all amounts one by one. A faster way is to copy a zero figure and paste it to relevant cells using the Paste icon. (Refer to the section on 'Shortcuts and Techniques' to see how to do this). Alternatively, paste to relevant cells using the Ctrl+V key combination.

10. Save the file.

11. Remove the filtering mode (menu Data/Filter/Show A11). Save again. Repeat the operation for each bank account.

TIP

Be careful not to replace the bank account opening balances with 0 (zero). If this happens, just replace the figure.