crashing1912
New Member
- Joined
- Sep 8, 2011
- Messages
- 19
I need a macro to "subtotal" the data below. Basically, there are invoices that can have different "Types" of costs on them, however, sometimes the information provided by the billing dept. the types can be repeated. For example, invoice 57 for Kansas has P listed twice, as well as T. I need the macro to sum columns Gross, Discount & billed for "P" on to one line.
The criteria would be sum Gross, Discount, & billed when the state, invoice & type are the same.
[TABLE="width: 500"]
<tbody>[TR]
[TD]State[/TD]
[TD]Invoice[/TD]
[TD]Type[/TD]
[TD]Gross[/TD]
[TD]Discount[/TD]
[TD]Billed[/TD]
[/TR]
[TR]
[TD]Michigan[/TD]
[TD]56[/TD]
[TD]T[/TD]
[TD]500[/TD]
[TD]0[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]Michigan[/TD]
[TD]56[/TD]
[TD]R[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Kanas[/TD]
[TD]57[/TD]
[TD]P[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Kansas[/TD]
[TD]57[/TD]
[TD]P[/TD]
[TD]300[/TD]
[TD]0[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]Kansas[/TD]
[TD]57[/TD]
[TD]T[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Texas[/TD]
[TD]58[/TD]
[TD]R[/TD]
[TD]200[/TD]
[TD]0[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Texas[/TD]
[TD]58[/TD]
[TD]R[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Oklahoma[/TD]
[TD]59[/TD]
[TD]T[/TD]
[TD]200[/TD]
[TD]0[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Oklahoma[/TD]
[TD]59[/TD]
[TD]T[/TD]
[TD]300[/TD]
[TD]0[/TD]
[TD]300[/TD]
[/TR]
</tbody>[/TABLE]
The end result should look like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]State[/TD]
[TD]Invoice[/TD]
[TD]Type[/TD]
[TD]Gross[/TD]
[TD]Discount[/TD]
[TD]Billed[/TD]
[/TR]
[TR]
[TD]Michigan[/TD]
[TD]56[/TD]
[TD]T[/TD]
[TD]500[/TD]
[TD]0[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]Michigan[/TD]
[TD]56[/TD]
[TD]R[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Kansas[/TD]
[TD]57[/TD]
[TD]P[/TD]
[TD]400[/TD]
[TD]0[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]Kansas[/TD]
[TD]57[/TD]
[TD]T[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Texas[/TD]
[TD]58[/TD]
[TD]R[/TD]
[TD]300[/TD]
[TD]0[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]Oklahoma[/TD]
[TD]59[/TD]
[TD]T[/TD]
[TD]500[/TD]
[TD]0[/TD]
[TD]500[/TD]
[/TR]
</tbody>[/TABLE]
The criteria would be sum Gross, Discount, & billed when the state, invoice & type are the same.
[TABLE="width: 500"]
<tbody>[TR]
[TD]State[/TD]
[TD]Invoice[/TD]
[TD]Type[/TD]
[TD]Gross[/TD]
[TD]Discount[/TD]
[TD]Billed[/TD]
[/TR]
[TR]
[TD]Michigan[/TD]
[TD]56[/TD]
[TD]T[/TD]
[TD]500[/TD]
[TD]0[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]Michigan[/TD]
[TD]56[/TD]
[TD]R[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Kanas[/TD]
[TD]57[/TD]
[TD]P[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Kansas[/TD]
[TD]57[/TD]
[TD]P[/TD]
[TD]300[/TD]
[TD]0[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]Kansas[/TD]
[TD]57[/TD]
[TD]T[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Texas[/TD]
[TD]58[/TD]
[TD]R[/TD]
[TD]200[/TD]
[TD]0[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Texas[/TD]
[TD]58[/TD]
[TD]R[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Oklahoma[/TD]
[TD]59[/TD]
[TD]T[/TD]
[TD]200[/TD]
[TD]0[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Oklahoma[/TD]
[TD]59[/TD]
[TD]T[/TD]
[TD]300[/TD]
[TD]0[/TD]
[TD]300[/TD]
[/TR]
</tbody>[/TABLE]
The end result should look like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]State[/TD]
[TD]Invoice[/TD]
[TD]Type[/TD]
[TD]Gross[/TD]
[TD]Discount[/TD]
[TD]Billed[/TD]
[/TR]
[TR]
[TD]Michigan[/TD]
[TD]56[/TD]
[TD]T[/TD]
[TD]500[/TD]
[TD]0[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]Michigan[/TD]
[TD]56[/TD]
[TD]R[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Kansas[/TD]
[TD]57[/TD]
[TD]P[/TD]
[TD]400[/TD]
[TD]0[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]Kansas[/TD]
[TD]57[/TD]
[TD]T[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Texas[/TD]
[TD]58[/TD]
[TD]R[/TD]
[TD]300[/TD]
[TD]0[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]Oklahoma[/TD]
[TD]59[/TD]
[TD]T[/TD]
[TD]500[/TD]
[TD]0[/TD]
[TD]500[/TD]
[/TR]
</tbody>[/TABLE]
