Filling up template based on certain criterias

  • Dear All, I have a data dump that I want to extract into a template and be printed out. Initial

    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.

    1. Each template would be either limited daily OR maximum of 10 transactions as long as they are from the same credit source.
    2. Credit Source = Source + Source Name
    3. Total = All values inside the voucher
    4. Account = Item Code
    5. Detail = Item Name
    6. Unit Code = Unit Code
    7. Value = Total Debit
    8. 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?
    9. And Finally, to colour code for those that have been extracted to the template

      like below.

    Final

    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!


  • Hello and Welcome to the Forum :)


    In an Excel Forum, it would certainly be more ' natural ' to attach ... well an Excel file ...;)


    rather than images ... which are ... by definition ... dead objects ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Dear All,This is what I have reached for now... But still could not do what it is supposed to do....Please do advise

  • Since I cant figure out how to edit...

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

  • Hello,


    It seems to me you are truly over-complicating your life ...


    Is it a request from your management ?


    Can you explain the entire process of your huge number of ' vouchers ' ...


    I am afraid about the number of individual sheets for individual vouchers ... to be printed out ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi Carim,

    Thank you very much for your reply.


    It unfortunately is the requirement from management to print all the payment vouchers and save the database for all the vouchers or at least ensure that it can be retrieved any moment easily.

    So I was thinking of putting more than 10 Vouchers per sheet while we are collecting the budget to move to access or sql.

    Or do you perhaps have any suggestions?

  • Hi,


    There are many questions :


    You mention Data dump ... so probably from a main frame ...


    How many records are you dealing with approx ?


    How many vouchers would that represent ?


    What would be the frequency of this process ?


    Why is your IT department not printing these thousands of vouchers ...?


    Is printing only required for filing or for something else ?


    Seems to me a little bit of common sense ... could really help ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi Carim,

    Here you go :)

    How many records are you dealing with approx ?

    Tens of Thousands and growing


    How many vouchers would that represent ?

    Tens of Thousands, I believe. Depends on the variations of the transactions. But would definitely be around that number.

    What would be the frequency of this process ?

    Everytime there is transaction actually. So I am cleaning up the backlogs...


    Why is your IT department not printing these thousands of vouchers ...?

    Newly established procedures...


    Is printing only required for filing or for something else ?

    Filing

  • Well ... let's see


    Has your ' Extremely Brilliant Management ' defined a Payment Voucher Unique Identifier ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Re,


    With your overwhelming number of vouchers ... you should probably consider something like:


    YYYY/MM/DD/FIRST_ITEM_CODE_in_VOUCHER/LAST_ITEM_CODE_in_VOUCHER

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi Carim,

    Thank you Carim!

    The real one is actually categorised into different kind of transactions as well.
    But I thought I would request for a help with the code and alter it into the different transactions afterwards.

  • Re,


    Does it mean you are not interested in the Unique Identifier ... but only in the Loop ' printing ' process ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi,


    Attached is your test file with a ' backbone ' macro to finalize in order to populate and print your vouchers ...


    Hope this will help

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)