Universal Connector - transactions grouped by debtor

If the payable and receivables are extracted in a format where they are grouped by debtor, 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 desired transaction list excel format.

 

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, please click here for a guide on uploading using the universal connector.

 

1. Select the column of the debtor or creditor name

2. Click ‘Find & Select’ on the Home tab

 

3. Select ‘Go to Special'

 

4. Select ‘Blanks’ then click ‘OK’

5. All blanks will automatically be selected, type '=' and click the cell above then press ‘Ctrl+Enter’ on your keyboard

6. The cells in the contact column should now have the correct debtor or creditor name

 

7. Once the contact field has been populated you will need to convert the column from equation to value

8. A simple way to do this is to copy the contact column and then paste in the contact column as a value

 

9. Once the contact field has been converted to a value you will then need to remove any row without a transaction

10. To do this it’s best to use the Invoice Date or Invoice Reference column. Select one of these columns and apply the filter

11. Filter for ‘blanks’

 

12. Delete all the filtered rows

13. Once the rows have been deleted, clear the filter

14. The excel has now been reformatted to be compatible with the universal connecter

Once you have reformatted your payables and receivables, please click here for a guide on uploading using the universal connector.