Reconciling two lists

[ED]

I have two sheets of data and need to Reconcile them
against each other in excel. One list is a list of
invoices and the other is a list of open items on our
accounting system.

I use our booking reference to match them up but there
could be a number of invoices for one open item or visa
versa. Is there any way any one can think of for
automatically reconciling this?

Many thanks for your help in advance.

Ed

 

[Bob Phillips]

Ed,

One visual way is to use COUNTIF.

Assuming that booking reference is in column A on both sheets, add this
formula to sheet C1 and copy down
=COUNTIF(Sheet2!$A$1:$A$100,A1)

and similarly on sheet2 C1
=COUNTIF(Sheet1!$A$1:$A$100,A1)

You will get a 0 for no match, 1 for singletons, >1 for multiples.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ken Wright]

Are you reconciling invoice amounts as well??

Assuming not for the moment, and that you just want to reconcile open items vs
list of invoices.

On one sheet (Make a copy of your Invoice list to start with), with Invoice
number in Col A and Booking reference in Col B. Insert a Col at the beginning
and give it a header of List. For each row in Col A put InvList You will now
have the following:-

List Inv Num Bkg Ref
InvList xxxx xxxx
InvList xxxx xxxx

Now put a 4th Col in and call it Compare and in each cell put a 1, so that you
now have:-

List Inv Num Bkg Ref Compare
InvList xxxx xxxx 1
InvList xxxx xxxx 1

Now copy your other list of Open Items directly under this list into the
appropriate columns, but for the moment leave a single blank row in between so
you know where the break is. In Col A call each entry OpenItems, and in the
Compare column put a -1, so that you now have the following:-

List Inv Num Bkg Ref Compare
InvList xxxx xxxx 1
InvList xxxx xxxx 1

OpenItems xxxx xxxx -1
OpenItems xxxx xxxx -1

Bkg references must be under Bkg Ref and Invoice numbers under Inv Num

Now delete the blank row in between them, select all the data and do Data /
Pivot table and Pivot Chart Report

Hit Next / Next / Finish

A new sheet with an empty table will appear, and on the right will be another
table with a list of items called List / Inv Num / Bkg Ref / Compare

Drag the List field to the top where it says 'Drop Column fields here'
Drag the Bkg Ref field to the left where it says 'Drop Row fields here'
Drag the Inv Num field to the left and drop it literally just in front of where
the Bkg Ref is
Drag the Compare field to the middle where it says 'Drop Data Items here'

Now you will see a list of Invoices by Bkg Ref number, and you will see a list
of 1s and 0s. Where you see a 0 on teh right you have a corresponding item in
both lists, and where you see a 1 you don't.