HOW DO MATCH AND ELIMINATE AMOUNTS IN TWO COLUMNS IN EXCEL
 

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"
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,VLOOKU P(A1,D1:E1000,2,false))-B1

and copy down

Similarly in F1 enter

=If(ISERROR(VLOOKUP(D1,A1:B1000,2,false)),0,VLOOKU P(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
__________________________