I HAVE EXPORTED A G/L ACCOUNT FROM ACCPAC TO EXCEL AND WISH TO MATCH AMOUNTS
IN ONE COLUMN WITH AMOUNTS IN OTHER COLUMN AND THEN ELIMINATE BOTH COLUMNS
LEAVING ME WITH THE UNMATCHED AMOUNTS IN BOTH COLUMNS - I AM USING EXCEL 98
I HAVE EXPORTED A G/L ACCOUNT FROM ACCPAC TO EXCEL AND WISH TO MATCH AMOUNTS
IN ONE COLUMN WITH AMOUNTS IN OTHER COLUMN AND THEN ELIMINATE BOTH COLUMNS
LEAVING ME WITH THE UNMATCHED AMOUNTS IN BOTH COLUMNS - I AM USING EXCEL 98
On Thu, 13 Oct 2005 12:32:03 -0700, "SNOWBREEZE3"
<[email protected]> wrote:
>I HAVE EXPORTED A G/L ACCOUNT FROM ACCPAC TO EXCEL AND WISH TO MATCH AMOUNTS
>IN ONE COLUMN WITH AMOUNTS IN OTHER COLUMN AND THEN ELIMINATE BOTH COLUMNS
>LEAVING ME WITH THE UNMATCHED AMOUNTS IN BOTH COLUMNS - I AM USING EXCEL 98
What are you trying to match. The GL account codes or the amounts?
It sounds like you have columns A & B with GL Codes and Values
respectively, and ditto with columns C & D, and that you're trying to
check which code values in B & D don't agree.
Checkout VLOOKUP in the Help, it's the most widely used function for
this type of stuff.
Insert a column between B & C and in C1 enter the formula
=If(ISERROR(VLOOKUP(A1,D1:E1000,2,false)),0,VLOOKUP(A1,D1:E1000,2,false))-B1
and copy down
Similarly in F1 enter
=If(ISERROR(VLOOKUP(D1,A1:B1000,2,false)),0,VLOOKUP(D1,A1:B1000,2,false))-E1
and copy down
Where you have non zero values in C & F it means the code values don't
match. Sort A:C on column C and delete the cells in A:C where C=0 ,
similarly sort D:F on column F and delete cells in D:F where F=0
HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks