How to reconcile a a floorplan account between books and statement

[Paul Bass]

Please give me advice on how to efficiently reconcile my floorpan account
that is composed of a few hundred stock #s and corresponding $ amount. I
know how
to download the books (stock# and $ amount) and the statement (stock # and $
amount). However, there are numerous reconciling items (on stmt, not on bks &
on bks, not on stmt). Is there a quick way to match all the like stock #s ?
Right now
I sort the books by stock # and the statement by stock# and subtract the two
stock# columns from each other. This still takes a long time.

 

[Excelenator]

Have a look at this file.

5119


+-------------------------------------------------------------------+
|Filename: ReconcileBooks.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5119 |
+-------------------------------------------------------------------+

 

[Excelenator]

you know that file I posted earlier? Forget it there is a MUCH easier
way to do this.

Put the two sets of data in a workbook on separate worksheets. Name
the Book Stock# range (column A?) BookStockNo and in the Statement Name
the Statement Stock# range (column A also?) StmtStockNo. Then go to the
Book worksheet, highlight all the data and insert conditional formatting
where the formulas are

Book worksheet
=COUNTIF(StmtStockNo,A1)=0

Statement worksheet
=COUNTIF(BookStockNo,A1)=0

Basically each sheet will check the other for the stock numbers and
when they are NOT found your conditional formatting will be applied to
that row of data. IF you chose to bold the font and yellow highlight
the cells you will EASILY see which items are ON the Statement and NOT
on the BOOKS and vice versa!

Much less work.