Hi Steve and welcome to the forum.
Is there any chance you could clarify the report layout for us. It looks a bit unusual as it has posted.
In fact if it really does have Account/Amount/Rep against the Name line and then 2 lines down (or is that address line 2 - same thing or not?) with such close spacing it seems very very unusual.
How many Accounts might there be as a maximum?
This may be a little clearer:
Date: 01/01/01 Sales Report
Name & Address Account Amount Rep
John Jones 123 1000.00 001
123 Main St
Anytown MA 00000 333 5000.00 002
Tax ID: 111-22-3333
John Smith 457 2000.00 002
456 1st St
Anytown MA 00000 890 3000.00 002
Tax ID: 222-33-3333
Well, a simple looking little report but it does present a few challenges it the requirement is to extract complete records.
Lets work backwards from your required output.
Date, Name 1, Name 2, Name 3, Address Line 1, Address Line 2, City State ZIP, Tax ID, Total, Rep1 #, Rep2 #, Account 1, Account2, etc.
How many names might there be?
Do multiple names always stack ON TOP of the NAME position that seems to be the default for the record samples posted? (i.e. is the line of the first ACCOUNT always the last or only NAME line?
Address Details: /B
Should be easy enough to use an address block to process for the address BUT not on one pass ... see below.
Is this always on the line below the address or might it be on the line below the last reported Account?
Looks like it should be a simple footer BUT do you really need the total since, if presented as a summary, each client (Tax ID) can be totaled from the values in the Account details and that would be preferable, I would suggest, for further analysis.
The "etc." bit needs to be considered. How many columns might this create in an Excel export?
Does the export need to be consistent from run to run (for instance if the extraction is to be appended to an existing worksheet) or can it simply work with what is found? (Number of columns likely to vary?)
If the 'client' has 5 accounts from 3 reps is is important to know which rep relates to which account? If it is that would suggest treating the Rep/Account as a single data entity.
There are a number of very different ways one could approach this. The simplest, in terms of Monarch modeling, would be a minimum 2 step approach.
The first model would extract the name(s), address and Tax ID and export to a file to be used as a lookup table later. (I am assuming Monarch Pro availability.)
A second model would process and extract the Account, Amount and Rep values, each as detail records, and also include the Tax Id for each using an append or footer.
The Tax ID would then be the key that links the Name and address information to the detail records and would also allow a method to key a Summary that would offer the output format required. (Although I have some reservations about that format.)
However, if the Tax Id data is not consistently positioned in relation to the Account records, especially where there are more than 2 accounts but maybe with Name and address variations as well, it may become difficult to capture consistently for the Account/Amount/Rep model.
It looks like the default 'Name' field is consistently found on the first line of the first Account reported. This MIGHT offer an alternative for a link key for the lookup BUT there are risks. Names are not always unique and if multiple names are involved, whilst the uniqueness (connected to a Tax ID) might improve dramatically I'm not sure that the trapping would be 'clean' since it's not obvious to me so far how to pick only the multiple names from the rest of the address in a consistent way. That is not to say it cannot be done but it does not look like a straightforward mapping based on the samples if 100% accuracy needs to be guaranteed.
I am wondering whether a more radical approach, involving an interim restructuring of the report to make modeling the final extraction easier, would make more sense here. The answers to my questions, raised above, would still be very relevant to such an approach.