Extract Multiple Rows into a Limited Template based on Multiple Criterias

[albertd]

Dear All,

I have a data dump that I want to extract into a template and be printed out.

Here are my intentions:

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


2. Values for the templates are:
- Credit Source = Source + Source Name
- Total = All values inside the voucher
- Account = Item Code
- Detail = Item Name
- Unit Code = Unit Code
- Value = Total Debit



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!Sample.xlsmCapture.jpgFinal.jpg

---

(

Also posted here:
http://www.vbaexpress.com/forum/show...iple-Criterias
https://www.myonlinetraininghub.com/...iple-criterias
https://chandoo.org/forum/threads/ex...iterias.43376/
https://stackoverflow.com/questions/...iple-criterias
https://www.excelforum.com/excel-pro...criterias.html
https://superuser.com/questions/1515...iple-criterias

[DocAElstein]

Hi albertd,
Welcome to Excel Fox…

I can help with some basic Excel VBA, but I no nothing about your project so I find it difficult to understand what you want.

It might be obvious to me later , as it is to you now, what you are asking.
But I need explanations set at a level for someone that has no idea at all about your project, and is starting from scratch.

All I can glean from your explanation is that you have a large data sheet, “Datadump”. Some how information from that needs to be put somewhere else. That is as much as I understand so far.


One of the images in your post looks like your worksheet “Template”
The other image, “Final.jpg” , has come out very small, so I can’t really see anything from it.


It might be helpful to upload a couple of files, or alternatively , a single file with a few worksheets.
Try to show the situation “Before” and “After” with a small sample set of data. Use just enough data to demonstrate what you want to do. (Desensitize the data if necessary: remove real names or make up data, but keep the format looking as realistic as possible)

“Before” is what you start with before any coding is run.

The “After” is based on the “Before” and is hand filled by you. You do in the “After” what you want the coding to do . And explain exactly what you did and why.

Then I or someone may be able to help develop coding to do what you did. Your original explanation might then help further, but initially you need to walk someone through exactly what you want to do with a small amount of sample data.



Alan

[albertd]

Hey Alan,

Firstly, thank you so very much for your reply and patience.
As what you have suggested, (Thank you!) I have attached the before and after sample that I did manually.
Before.xlsmAfter Voucher.xlsxAfter.xlsm

Would really appreciate the help

[DocAElstein]

Hi albertd,

At first glance, the only different between your Before.xlsm and After.xlsm that I can see is that you have highlighted some rows in the After.xlsm.
So my guess is that they are both the before, and that possibly you are trying to tell me something by highlighting some rows in green. But that is just a guess. Please remember again that I know nothing about your project , so you need to tell me what ever is you are trying to show me, assuming you are trying to indicate something with those two files.
Another possibility is that you want those lines to be highlighted by the coding, in which case the After.xlsm is the After. You may have been saying words to that effect in your first post.
As far as your project is concerned, assume I am a total uninformed idiot, which I am. You must explain everything in detail or I waste time trying to guess.

If I am not mistaken , then you have missed out colouring in green line 42 in your After.xlsm. Also I think some of your numbers in the Value column in worksheet V0002 in workbook "After Voucher.xlsx" may be in error. Or maybe I am in error. I will leave you to check that

I am still struggling a bit to fully understand everything that you want. But I a think I am slowly getting it..

---

Let me see if I can make a start….. then we will take it from there..
I have not yet figured out for sure what you are talking about when you say "Source". Has this got anything to do with source code and/ or source name???... I am going to take a stab that a "source" is like a pair "99909900