If the payables and receivables are extracted in a format where the debtor or creditor do not have their own column, the excel will need slight reformatting.
This is an example of a receivables report with the transactions grouped by debtor which will need to be reformatted.
This is an example of the debtor or creditor being in it's own column which is desired.
To convert the file to this format you will need to follow each of the following steps:
Once you have reformatted your payables and receivables they will still need to be put in a transaction list format to upload using the Universal Connector.
Please click here for a guide on putting the payables and receivables in to a transaction list format.
1. If there are not 2 empty columns before the first column, select the first column and insert columns until there is 2 empty columns, A & B
2. Enter the desired header for the debtor and creditors in Cell 'B1' in this example it's 'Company'
3. Select the cell in 'column B' which corresponds to the row and column of the first debtor or creditor and set that 'B Cell' equal to it, in this case it is 'C2'
4. Select the cell in 'column B' which corresponds to the row of the second debtor or creditor in this example that cell is 'B9'. Set that 'B Cell' with the equation '=IF(ISBLANK(C8),C9,A9)'
- 'C8' is the blank cell
- 'C9' is the debtor or creditor name
- 'A9' should always be a blank cell
Note: The equation changes depending on the Cell If the 'B cell' was 'B10' the equation would be '=IF(ISBLANK(C9),C10,A10)'. The algebra formula of the equation if the 'row corresponding to B'='N' is '=IF(ISBLANK(C(N-1)),C(N),A(N)'
5. Drag the cell with the equation down to the bottom of the transactions
6. This will pull the debtors or creditors into their own column with zero values
7. To remove the equations copy column B and paste it as a value in Column B
9. Replace the zeros in column B with blanks, to do this select column B and click 'Find & Select'
10. Once you click on 'Find & Select' click 'Replace'
11. In 'Find' type '0', leave 'Replace with' blank and click 'Replace All'
12. This has removed all zeros from column B
13. Delete column A as this was only needed for the equations
14. You have now split the debtors or creditors into their own column
Once you have reformatted your payables and receivables they will still need to be put in a transaction list format to upload using the Universal Connector.
Please click here for a guide on putting the payables and receivables in to a transaction list format.
Comments
0 comments
Please sign in to leave a comment.