Log In
Register
Home
Active Member
January 8, 2020
Offline
Dear All,
I understand that this might be a long process, So I thought we can take it solve the intentions one by one and I would keep on editing the post 🙂
I have a data dump that I want to extract into a template and be printed out.
Here are my intentions:
1. From the data dump (sample above), is it possible to automatically extract the appropriate value into the Template with a maximum of 10 rows for each set of Template.
Each template would be either limited daily OR maximum of 10 transactions as long as they are from the same credit source.
2. Values for the templates are:
3. Credit Source = Source + Source Name
4. Total = All values inside the voucher
5. Account = Item Code
6. Detail = Item Name
7. Unit Code = Unit Code
8. Value = Total Debit
9. As the datadump is extensive, is it possible to have ~10 sets of template in 1 Sheet and once they are printed out individually, they are deleted?
10. And Finally, to colour code for those that have been extracted to the template
Since I am new to VBA, I would have no issue with the inputs to their appropriate places and to colour code. But I am still learning about the loop function that I believe would be required for this?
Any help would be much appreciated!
Trusted Members
December 20, 2019
Offline
So you want a template created for each Souce & day?
This would be invoice 1 (99909900 for the 01/01/2020)
| Date | Item Code | Item Name | Unit Code | Quantity | Value | Total Debit | Souce | Source Name | Quantity | Value | Total Credit |
| 01/01/2020 | 00000001 | Item A | ABC123 | 1 | 2,000 | 2,000 | 99909900 | A | 1 | 2,000 | 2,000 |
| 01/01/2020 | 00000004 | Item D | LLL000 | 1 | 131 | 131 | 99909900 | A | 1 | 131 | 131 |
| 01/01/2020 | 00000005 | Item E | LLL000 | 5 | 550 | 2,750 | 99909900 | A | 5 | 550 | 2,750 |
| 01/01/2020 | 00000005 | Item E | LLL000 | 5 | 550 | 2,750 | 99909900 | A | 5 | 550 | 2,750 |
This would be invoice 2 (99909900 for the 02/01/2020)
| Date | Item Code | Item Name | Unit Code | Quantity | Value | Total Debit | Souce | Source Name | Quantity | Value | Total Credit |
| 02/01/2020 | 00000002 | Item B | ABC122 | 1 | 3,500 | 3,500 | 99909900 | B | 1 | 3,500 | 3,500 |
| 02/01/2020 | 00000002 | Item B | ABC122 | 1 | 3,500 | 3,500 | 99909900 | B | 1 | 3,500 | 3,500 |
and then the same for the next Souce and so on?
And the template would look like the Below? Is it mapped correctly? what goes in the yellow boxes?
Active Member
January 8, 2020
Offline
Hi Purfleet!
Thank you very much for replying!
Yes please! and if there is more than 10 transactions for that source on the same day, is it possible to split them into X no. of vouchers?
Yellow boxes would be the Item Code 🙂
I have provided a before and after for reference example.
And love that name!
Active Member
January 8, 2020
Offline
Further clarification of the purpose:
Here are my intentions:
1. From the data dump (sample above), is it possible to automatically extract the appropriate value into the Template with a maximum of 10 rows for each set of [Template][2]. And then colour-code those that have been printed to the Template.
These are the Template (Payment Voucher) limitations:
a. Each template contains the data from only **1 (one) day**
If in 1st January 2020 & 2nd January 2020, there are 5 transactions **/day**, there would have to be **2 Templates (1 for each day).**
b. Each Template should only be from **1 Source**
So if in 1st January 2020 & 2nd January 2020, there are 5 transactions/day from **each Source A & B**, there would be **4 Templates (1 for each source/day)**.
c. Each template could only contain **10 lines.**
So if in 1st January 2020 & 2nd January 2020, there are **11** transactions/day from each Source A & B, there would be **8 Templates (2 for each source/day)**.
Trusted Members
December 20, 2019
Offline
Hi Albert
To check that i am on the right tracks, can you please have a look at the attached workbook.
It has a macro to extract all the dates in column A and all the Sources in column I, then apply them, in turn, to the datadump page and create a new work sheet for each one - therefore the 51 records will be split out in to however many worksheets. There is also a button to get back to just the datadump and template rather than manually deleting loads of worksheets.
Dont worry about the over 10 records part yet, just want to know that i am splitting the data up correctly
Purfleet
1 Guest(s)
