Using Advance Filter sample 5 6 pg 135 136

Example 5

To see rows of data of where one account name appear in ACCOUNT-CREDIT column or ACCOUNT CREDIT column, but not necessarily in the same row.

Say we want to select rows where an account name BANK A appears in either the column ACCOUNT-DEBIT or in the column ACCOUNT-CREDIT but not necessarily in the same row. In this case, we want to select items from 2 columns. So we will use the Advanced Filter operation.

This is useful if you want to review transactions involving a particular account.

1. Set up a criteria block to look like this:

	A		B		C
41
42	ACCOUNT-DEBIT	ACCOUNT-CREDIT
43	BANK A
44			BANK A
45

Notice that this criteria block start at row 42 because the rows above it are used for other criteria.

The above criteria indicates that we want to view rows where column ACCOUNT-DEBIT contain BANK A and column ACCOUNT-CREDIT also contain the same item, but not necessarily in the same row. Notice that in the criteria block the item BANK A is not stated in the same row under column ACCOUNT-DEBIT and ACCOUNT-CREDIT. Further, while column ACCOUNT-DEBIT contains the item BANK A, column ACCOUNT-CREDIT may contain anything in the same row, and while column ACCOUNT-CREDIT contain item BANK A, the column ACCOUNT-DEBIT may contain anything in the same row.

2. Follow steps 3 to 8 as in Example 1 above.

You should see the data filtered according to the criteria block.

135

Example 6

To get a list of uncleared bank items (assuming you have more than one bank account)

Say we want to get a list of uncleared BANK A items to help us in preparation of bank reconciliation statement (assuming that you have more than one bank account).

In this case, we want to select rows where item BANK A appears in column ACCOUNT-DEBIT and the item in column BANK RECON is open (both in the same row) and select also rows where item BANK A appears in column ACCOUNT CREDIT and the item in column BANK RECON is open (both items in the same row).

In this case, we require more than one column. So we can use an Advanced Filter operation.

1. Set up a criteria block to look like this:

	A		B		C			D
28
29	ACCOUNT-DEBIT	ACCOUNT-CREDIT	BANK RECON
30	BANK A				open
31			BANK A		open
32	

The above criteria indicates that you want to view rows where the column ACCOUNT-DEBIT contain item BANK A and column BANK RECON contain item open (both items in the same row), and select also rows where column ACCOUNT-CREDIT contain item BANK A and column BANK RECON contain item open (both items in the same row)

2. Follow steps 3 to 8 as in Example 1 above.

You should see the data filtered according to the criteria block. You can copy relevant data to your bank reconciliation working sheet.

NOTE

You can repeat the process for each of the other bank accounts.

188