creating individual statements

[bronzeboar]

Hello,

I have a spreadsheet full of data that I download from a database and dump into Excel. (I also dump it into a financial software package to generate payments). The problem is that there isn't a good option in the financial software for creating a statement to send with the check. So the recipients oftentimes can't determine why I am paying them what I am.

What I end up with in the Excel spreadsheet is approximately 800 rows by twelve columns of data. Some customers have one row of information, while others may have up to six or seven. Out of the roughly 800 rows are about 400 clients.

What I want to do is create a separate statement for each client that shows the information in the row, will show the one to seven rows of information, and will total the sums. Then I can stuff the individual statement with the check that matches it and my clients will have a better understanding of what I am sending them.

example

Invoice# - client #1 - date1 - date2 - # of days - amount - reason - rate
Invoice# - client #1 - date1 - date2 - # of days - amount - reason - rate
Invoice# - client #1 - date1 - date2 - # of days - amount - reason - rate
Invoice# - client #1 - date1 - date2 - # of days - amount - reason - rate
Total

Invoice# - client #2 - date1 - date2 - # of days - amount - reason - rate
Total

Invoice# - client #3 - date1 - date2 - # of days - amount - reason - rate
Invoice# - client #3 - date1 - date2 - # of days - amount - reason - rate
Total


Any ideas? Anyone here ever done this...or can point me in the right direction? I haven't used VBA or pivot tables before...is this something I will need to learn?

Thank you in advance!

 

[gnrboyd]

This probably isn't much help but here is a general idea of what I would do. I would create a template with the following: One sheet would be the statement form as you'd like to send to your customers. Another sheet would be for the billing data you dump into it. Use lookup formulas in your statement sheet to pull info from the data sheet based upon a customer number, account number, record number, etc. I would then create a 3rd sheet with a cell for a beginning and ending record number for you to type in. Then I would create a macro with a loop and add a button that runs the macro from this page. The lookup formulas pull the beginning record you typed in, the form is printed, and then it loops to the next record, etc until it reaches the end record. This would be similar to how the mail merge function works in MS Word. In fact you could probably use that also.

Again, I know the above is vague and general but I am not really qualified to help you through a step by step process for this type of project. I've done similar things like this myself but it was with a lot of trial and error. Maybe this will get you started or maybe someone will have a better and easier solution. Good Luck.

 

[jindon]

What I want to do is create a separate statement for each client that shows the information in the row, will show the one to seven rows of information, and will total the sums. Then I can stuff the individual statement with the check that matches it and my clients will have a better understanding of what I am sending them.

I think Pivot Table is the best way to go..

 

[bronzeboar]

Thank you both, I'll look deeper into that.


The other thing I was wondering, I do have Crystal 8.5 and moderate skills with that. Maybe I can pull from my Excel file with that?

 

[Norie]

How is the original data structured?

 

[gnrboyd]

I think Pivot Table is the best way to go..

The pivot table is one thing I've never used in Excel despite how much I use it for various other things. One of these days, I need to sit down and force myself to learn it. Will the pivot table approach allow you to print all of your invoices at once (such as with my suggestion) or would you have to do them one at a time?

 

[bronzeboar]

How is the original data structured?

Is this what you're looking for?

Also, regarding the question above, I am hoping to be able to print all invoices at a time...or even copy into a PDF file.

[RANGE=cls:xl2bb-100][XR][XH=cs:10]Book2[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][XH]F[/XH][XH]G[/XH][XH]H[/XH][XH]I[/XH][/XR][XR][XH]1[/XH][XD=h:l|fw:b|fz:11pt]Invoice#[/XD][XD=h:l|fw:b|fz:11pt]Vendor[/XD][XD=h:c|fw:b|fz:11pt]Begdate[/XD][XD=h:c|fw:b|fz:11pt]Enddate[/XD][XD=h:l|fw:b|fz:11pt]Account#[/XD][XD=h:c|fw:b|fz:11pt]Nights[/XD][XD=h:l|fw:b|fz:11pt]Amount[/XD][XD=h:l|fw:b|fz:11pt]Client[/XD][XD=h:l|fw:b|fz:11pt]Notes[/XD][/XR][XR][XH]2[/XH][XD=h:l|fz:11pt]23425[/XD][XD=h:l|fz:11pt]Adams[/XD][XD=h:c|fz:11pt]10/1/08[/XD][XD=h:c|fz:11pt]10/31/08[/XD][XD=h:l|fz:11pt]30033[/XD][XD=h:c|fz:11pt]31[/XD][XD=h:r|fz:11pt]900.00[/XD][XD=h:l|fz:11pt]client1[/XD][XD=h:l|fz:11pt]rate1[/XD][/XR][XR][XH]3[/XH][XD=h:l|fz:11pt]25343[/XD][XD=h:l|fz:11pt]Johnson[/XD][XD=h:c|fz:11pt]10/1/08[/XD][XD=h:c|fz:11pt]10/31/08[/XD][XD=h:l|fz:11pt]32021[/XD][XD=h:c|fz:11pt]31[/XD][XD=h:r|fz:11pt]800.00[/XD][XD=h:l|fz:11pt]client2[/XD][XD=h:l|fz:11pt]rate4[/XD][/XR][XR][XH]4[/XH][XD=h:l|fz:11pt]24356[/XD][XD=h:l|fz:11pt]Johnson[/XD][XD=h:c|fz:11pt]10/1/08[/XD][XD=h:c|fz:11pt]10/31/08[/XD][XD=h:l|fz:11pt]32021[/XD][XD=h:c|fz:11pt]31[/XD][XD=h:r|fz:11pt]850.00[/XD][XD=h:l|fz:11pt]client3[/XD][XD=h:l|fz:11pt]rate2[/XD][/XR][XR][XH]5[/XH][XD=h:l|fz:11pt]23467[/XD][XD=h:l|fz:11pt]Johnson[/XD][XD=h:c|fz:11pt]10/1/08[/XD][XD=h:c|fz:11pt]10/31/08[/XD][XD=h:l|fz:11pt]32021[/XD][XD=h:c|fz:11pt]31[/XD][XD=h:r|fz:11pt]850.00[/XD][XD=h:l|fz:11pt]client4[/XD][XD=h:l|fz:11pt]rate2[/XD][/XR][XR][XH]6[/XH][XD=h:l|fz:11pt]25435[/XD][XD=h:l|fz:11pt]Smith[/XD][XD=h:c|fz:11pt]10/15/08[/XD][XD=h:c|fz:11pt]10/31/08[/XD][XD=h:l|fz:11pt]30068[/XD][XD=h:c|fz:11pt]16[/XD][XD=h:r|fz:11pt]420.00[/XD][XD=h:l|fz:11pt]client5[/XD][XD=h:l|fz:11pt]rate3[/XD][/XR][XR][XH]7[/XH][XD=h:l|fz:11pt]24332[/XD][XD=h:l|fz:11pt]Smith[/XD][XD=h:c|fz:11pt]10/15/08[/XD][XD=h:c|fz:11pt]10/31/08[/XD][XD=h:l|fz:11pt]30068[/XD][XD=h:c|fz:11pt]16[/XD][XD=h:r|fz:11pt]420.00[/XD][XD=h:l|fz:11pt]client6[/XD][XD=h:l|fz:11pt]rate3[/XD][/XR][XR][XH]8[/XH][XD=h:l|fz:11pt]23226[/XD][XD=h:l|fz:11pt]Zeller[/XD][XD=h:c|fz:11pt]10/1/08[/XD][XD=h:c|fz:11pt]10/8/08[/XD][XD=h:l|fz:11pt]31012[/XD][XD=h:c|fz:11pt]7[/XD][XD=h:r|fz:11pt]175.00[/XD][XD=h:l|fz:11pt]client7[/XD][XD=h:l|fz:11pt]rate5[/XD][/XR][XR][XH=cs:10][RANGE][XR][XD]Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

 

[Norie]

Well you could adapt something like this to split the data out by Vendor, if that's what you want.

Option Explicit
Sub DistributeRows()
Dim wsAll As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim LastRow As Long
Dim LastRowCrit As Long
Dim I As Long
    
    Set wsAll = Worksheets("All") ' change All to the name of the worksheet the existing data is on
    
    LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row
    
    Set wsCrit = Worksheets.Add
    
    ' column G has the criteria eg project ref
    wsAll.Range("G1:G" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True
    
    LastRowCrit = wsCrit.Range("A" & Rows.Count).End(xlUp).Row
    For I = 2 To LastRowCrit
    
        Set wsNew = Worksheets.Add
        wsNew.Name = wsCrit.Range("A2")
        wsAll.Rows("1:" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsCrit.Range("A1:A2"), _
         CopyToRange:=wsNew.Range("A1"), Unique:=False
        wsCrit.Rows(2).Delete
        
    Next I
    
    Application.DisplayAlerts = False
    wsCrit.Delete
    Application.DisplayAlerts = True
    
End Sub

It should be easy to change to just print the worksheets then delete them..

 

[bronzeboar]

Well you could adapt something like this to split the data out by Vendor, if that's what you want.


It should be easy to change to just print the worksheets then delete them..

Sorry to be a bit obtuse...but what language is that?