I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

Reconciliation

With a reconciliation you can ensure data integrity or consistency. A simple example is a comparison of two lists. There are many possible approaches to show which elements of list A are not in list B and vice versa.

In principle you can come up either with a realtime comparison or with a batch process to do this. The realtime reconciliation can be done with a worksheet formula (including a conditional format) or with a VBA function. As a rule of thumb you do not want its runtime to be unbearably long - let's say longer than 0.2 sec. The batch process can be a VBA subroutine or a pivot table. This might be your preferred choice if the reconciliation is quite complex or if it lasts quite long (minutes).

Here is an example for a conditional format (volatile worksheet formula):

sbCompareTwoLists_01_Screen

An example for a batch process solution (a VBA subroutine):

20100816_PB_01_Compare_Lists_VBA
sbCompareTwoLists_01_Code
sbCompareTwoLists_02_Code

If you are interested in downloading a 108 KB Excel 2010 sample file go to my Download page, please.

Sulprobil   Get it done   Contact   Disclaimer   Download