I have attached an excel file.
It is a reconciliation of vendor statement.
There is one input name Foreign which is vendor statement of accounts.
Only items which do not have any value in the clearing doc. will be considered for reconciliation.
All the other excel sheets are inputs for India books.
Again only items without clearing doc. should be cleared.
The field doc no. in Vendor sheet and Reference in India are common fields.
Columns M to T in vendor statement are only for reconciliation. They are not actual inputs
Similarly columns M and N are only for reconciliation. They are not actual inputs.
Reconciliation is done as follows:
1. Reference field from India is matched with Doc no. with foreign. If the vales match, there is no difference. This will not be part of reconciliation.
2. If the invoice no. is matching, but difference in value, the difference will be part of reconciliation.
3. If the invoice no. is matching but one of the account is not cleared(i.e. Clearing doc is blank for one and has a number in another), this will also appear as a reconciliation with the remark 'Already cleared'
4. If the invoice no. is not available in 'India', then the invoice no. is foreign is compared with invoice no. in all other sheets in the below order.
a. Managing committee
b. GRN made
c. Gate entry
f. wrong invoice no.
In the case of any clarification, please ping me for details.