Universal Connector - Splitting debtor/creditor names in to a seperate column

If the payable are 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 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 are 2 empty 

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 of the first debtor or creditor and set that 'B' cell equal to it. 

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)'.

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 zero's 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' input '0', leave 'Replace with' black 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 split the debtor or creditor into its 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.