bank recon

melody_ph · External Since: May 15, 2009 Posts: 3

I have been reading the blogs but couldn't really find what I'm looking for.
I'm doing a bank rec, I have 2 sheets. One is our ledger that has 3 columns
as follows:

A - Date
B - Description
C - Amount

The other sheet is our bank statement. I couldn't match by amount since one
amount in the first sheet (ledger) could be split in different amounts and on
different dates in the second sheet (bank statement) and vise versa. I'm
matching them by the description or text.

I want to have a formula that will highlight the descriptions (plus the
amounts that matches if possible) that have NO matches. Or is there a
specific programming for this. A query program maybe?

I hope someone can help me...

Thanks a lot

OssieMac · External Since: Jan 25, 2007 Posts: 122

Hi Melody,

Assume that in both worksheets:
Column A contains Date
Column B contains Description
Column C contains amount
Actual data starts in row 2.

Select Bank Statement worksheet.
Select the data in Description column only from the cell below the column
header to the bottom of the data.
Select Define Name (see Help for how to Define a name)
Name the range Statement.
Close Define name dialog box.

Change to Ledger worksheet.
Select all three columns of data of the data from the row below the column
headers to the bottom of the data.
Select Conditional Format (See Help for where to find Conditional format)
Select Formula option.
Enter the following formula. (You can copy this formula from here and paste
it into the field in Conditional Format)
=ISERROR(VLOOKUP($B2,Statement,1,FALSE))
Click Format button in the dialog box and set Fill color.
Click Apply or OK as applicable until Conditional Format dialog box closed.

Repeat the above but start on the Ledger sheet and name the range Ledger and
then change to Bank Statement and set the conditional format again as above
but the formula will be as follows: (You can copy this formula from here and
paste it into the field in conditional format)
=ISERROR(VLOOKUP($B2,Ledger,1,FALSE))

If you can't work out how to Define Names and Conditional Format then get
back to me and tell me what version of Excel you have and I will attempt to
provide detailed instructions.

--
Regards,

OssieMac

my_melody · External Since: May 16, 2009 Posts: 1

Hi OssieMac,

This is very helpful...I'm still figuring out though how to define
names..Conditional formatting I'm OK....

Also, I want to tell you I put put the date from the other work sheet side
by side on the first worksheet so it now look like this

A B C D E F
Date Desc Amt. Date Desc Amt

and use this formula and put in G =MATCH("*"&E1&"*",$B$1:$B$2001,0) and
it works great but since my worksheet is so long (from B1:B2001), it takes
too long to find where it is posted because the date is all screwed. SO I
just want a formula to add or change to this formula that would highlight the
ones that don't have matches..

Am I getting more confusing. Anyways, I will work on this formula that you
gave me first. Thanks a lot!

"OssieMac" wrote:

> Hi Melody,
>
> Assume that in both worksheets:
> Column A contains Date
> Column B contains Description
> Column C contains amount
> Actual data starts in row 2.
>
> Select Bank Statement worksheet.
> Select the data in Description column only from the cell below the column
> header to the bottom of the data.
> Select Define Name (see Help for how to Define a name)
> Name the range Statement.
> Close Define name dialog box.
>
> Change to Ledger worksheet.
> Select all three columns of data of the data from the row below the column
> headers to the bottom of the data.
> Select Conditional Format (See Help for where to find Conditional format)
> Select Formula option.
> Enter the following formula. (You can copy this formula from here and paste
> it into the field in Conditional Format)
> =ISERROR(VLOOKUP($B2,Statement,1,FALSE))
> Click Format button in the dialog box and set Fill color.
> Click Apply or OK as applicable until Conditional Format dialog box closed.
>
> Repeat the above but start on the Ledger sheet and name the range Ledger and
> then change to Bank Statement and set the conditional format again as above
> but the formula will be as follows: (You can copy this formula from here and
> paste it into the field in conditional format)
> =ISERROR(VLOOKUP($B2,Ledger,1,FALSE))
>
> If you can't work out how to Define Names and Conditional Format then get
> back to me and tell me what version of Excel you have and I will attempt to
> provide detailed instructions.
>
> --
> Regards,
>
> OssieMac
>
>

melody_ph · External Since: May 15, 2009 Posts: 3

If you can please provide me with detail instructions...

"OssieMac" wrote:

> Hi Melody,
>
> Assume that in both worksheets:
> Column A contains Date
> Column B contains Description
> Column C contains amount
> Actual data starts in row 2.
>
> Select Bank Statement worksheet.
> Select the data in Description column only from the cell below the column
> header to the bottom of the data.
> Select Define Name (see Help for how to Define a name)
> Name the range Statement.
> Close Define name dialog box.
>
> Change to Ledger worksheet.
> Select all three columns of data of the data from the row below the column
> headers to the bottom of the data.
> Select Conditional Format (See Help for where to find Conditional format)
> Select Formula option.
> Enter the following formula. (You can copy this formula from here and paste
> it into the field in Conditional Format)
> =ISERROR(VLOOKUP($B2,Statement,1,FALSE))
> Click Format button in the dialog box and set Fill color.
> Click Apply or OK as applicable until Conditional Format dialog box closed.
>
> Repeat the above but start on the Ledger sheet and name the range Ledger and
> then change to Bank Statement and set the conditional format again as above
> but the formula will be as follows: (You can copy this formula from here and
> paste it into the field in conditional format)
> =ISERROR(VLOOKUP($B2,Ledger,1,FALSE))
>
> If you can't work out how to Define Names and Conditional Format then get
> back to me and tell me what version of Excel you have and I will attempt to
> provide detailed instructions.
>
> --
> Regards,
>
> OssieMac
>
>

OssieMac · External Since: Jan 25, 2007 Posts: 122

Sorry I have been unable to get back to you sooner.

What version of Excel are you using so that I can give the detailed
instructions for that version.

Also is my assumption of the layout of data as follows correct? If not can
you list the columns and what data you have in each column on both the Bank
Satement and Ledger sheets.

I am still assuming that you are comparing the Descriptions. Are these
descriptions exactly the same on each sheet? If not, are there any part/s of
the description that always matches like and Invoice number and if so, can
you give me an example of the format of the number?

Assume that in both worksheets:
Column A contains Date
Column B contains Description
Column C contains amount
Actual data starts in row 2.

--
Regards,

OssieMac

melody_ph · External Since: May 15, 2009 Posts: 3

OssieMac...can I possibly email you? Just once I promise...To show you the
worksheet...You can put your formula in and email it back to me and I will
not bother you anymore...^.^...my email is my.melody614.TakeThisOut@gmail.com

"OssieMac" wrote:

> Sorry I have been unable to get back to you sooner.
>
> What version of Excel are you using so that I can give the detailed
> instructions for that version.
>
> Also is my assumption of the layout of data as follows correct? If not can
> you list the columns and what data you have in each column on both the Bank
> Satement and Ledger sheets.
>
> I am still assuming that you are comparing the Descriptions. Are these
> descriptions exactly the same on each sheet? If not, are there any part/s of
> the description that always matches like and Invoice number and if so, can
> you give me an example of the format of the number?
>
> Assume that in both worksheets:
> Column A contains Date
> Column B contains Description
> Column C contains amount
> Actual data starts in row 2.
>
> --
> Regards,
>
> OssieMac
>