Summary report by Category in VBA

[chriscorpion786]

Hi All,

I have data like below and I need to summarize this data, I have done it by name, but I also need it by name and category.
I have posted the code below and have done it the way I understand it. The code works perfectly, I need help to summarize by name going down the rows and category by columns. Please keep the code simple for me to understand.

Thanks in advance.


[TABLE="width: 959"]
<colgroup><col><col><col><col><col span="9"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Products[/TD]
[TD]Price[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/2/2018[/TD]
[TD="align: right"]1/3/2018[/TD]
[TD="align: right"]1/4/2018[/TD]
[TD="align: right"]1/5/2018[/TD]
[TD="align: right"]1/6/2018[/TD]
[TD="align: right"]1/7/2018[/TD]
[TD="align: right"]1/8/2018[/TD]
[TD="align: right"]1/9/2018[/TD]
[TD="align: right"]1/10/2018[/TD]
[/TR]
[TR]
[TD]Rose[/TD]
[TD]Signal[/TD]
[TD="align: right"]$9[/TD]
[TD="align: right"]$3,840[/TD]
[TD="align: right"]$21,423[/TD]
[TD="align: right"]$43,866[/TD]
[TD="align: right"]$41,216[/TD]
[TD="align: right"]$22,205[/TD]
[TD="align: right"]$27,415[/TD]
[TD="align: right"]$16,106[/TD]
[TD="align: right"]$13,661[/TD]
[TD="align: right"]$30,739[/TD]
[TD="align: right"]$21,301[/TD]
[/TR]
[TR]
[TD]Martin[/TD]
[TD]Signal[/TD]
[TD="align: right"]$5[/TD]
[TD="align: right"]$1,683[/TD]
[TD="align: right"]$14,130[/TD]
[TD="align: right"]$22,007[/TD]
[TD="align: right"]$4,171[/TD]
[TD="align: right"]$4,875[/TD]
[TD="align: right"]$7,482[/TD]
[TD="align: right"]$7,556[/TD]
[TD="align: right"]$38,711[/TD]
[TD="align: right"]$23,799[/TD]
[TD="align: right"]$16,584[/TD]
[/TR]
[TR]
[TD]Larry[/TD]
[TD]Crest[/TD]
[TD="align: right"]$10[/TD]
[TD="align: right"]$1,385[/TD]
[TD="align: right"]$29,714[/TD]
[TD="align: right"]$26,646[/TD]
[TD="align: right"]$29,605[/TD]
[TD="align: right"]$32,303[/TD]
[TD="align: right"]$19,401[/TD]
[TD="align: right"]$12,516[/TD]
[TD="align: right"]$10,065[/TD]
[TD="align: right"]$16,425[/TD]
[TD="align: right"]$38,025[/TD]
[/TR]
[TR]
[TD]Cynthia[/TD]
[TD]Signal[/TD]
[TD="align: right"]$3[/TD]
[TD="align: right"]$2,126[/TD]
[TD="align: right"]$24,838[/TD]
[TD="align: right"]$4,508[/TD]
[TD="align: right"]$31,995[/TD]
[TD="align: right"]$10,708[/TD]
[TD="align: right"]$34,345[/TD]
[TD="align: right"]$14,862[/TD]
[TD="align: right"]$34,503[/TD]
[TD="align: right"]$37,713[/TD]
[TD="align: right"]$29,050[/TD]
[/TR]
[TR]
[TD]Dunya[/TD]
[TD]Flare[/TD]
[TD="align: right"]$9[/TD]
[TD="align: right"]$2,370[/TD]
[TD="align: right"]$17,714[/TD]
[TD="align: right"]$15,744[/TD]
[TD="align: right"]$2,367[/TD]
[TD="align: right"]$43,627[/TD]
[TD="align: right"]$41,822[/TD]
[TD="align: right"]$36,244[/TD]
[TD="align: right"]$6,514[/TD]
[TD="align: right"]$41,071[/TD]
[TD="align: right"]$38,226[/TD]
[/TR]
[TR]
[TD]Edgar[/TD]
[TD]Flare[/TD]
[TD="align: right"]$10[/TD]
[TD="align: right"]$3,973[/TD]
[TD="align: right"]$27,881[/TD]
[TD="align: right"]$39,808[/TD]
[TD="align: right"]$24,010[/TD]
[TD="align: right"]$23,306[/TD]
[TD="align: right"]$32,518[/TD]
[TD="align: right"]$4,852[/TD]
[TD="align: right"]$33,482[/TD]
[TD="align: right"]$28,120[/TD]
[TD="align: right"]$32,301[/TD]
[/TR]
[TR]
[TD]Rihab[/TD]
[TD]Fluroide[/TD]
[TD="align: right"]$9[/TD]
[TD="align: right"]$830[/TD]
[TD="align: right"]$6,740[/TD]
[TD="align: right"]$41,281[/TD]
[TD="align: right"]$10,329[/TD]
[TD="align: right"]$14,280[/TD]
[TD="align: right"]$19,681[/TD]
[TD="align: right"]$13,917[/TD]
[TD="align: right"]$13,405[/TD]
[TD="align: right"]$8,196[/TD]
[TD="align: right"]$22,091[/TD]
[/TR]
[TR]
[TD]Nicholas[/TD]
[TD]Fluroide[/TD]
[TD="align: right"]$9[/TD]
[TD="align: right"]$1,970[/TD]
[TD="align: right"]$14,595[/TD]
[TD="align: right"]$20,319[/TD]
[TD="align: right"]$1,134[/TD]
[TD="align: right"]$5,950[/TD]
[TD="align: right"]$38,813[/TD]
[TD="align: right"]$39,644[/TD]
[TD="align: right"]$3,271[/TD]
[TD="align: right"]$30,752[/TD]
[TD="align: right"]$20,275[/TD]
[/TR]
[TR]
[TD]Itab[/TD]
[TD]Crest[/TD]
[TD="align: right"]$10[/TD]
[TD="align: right"]$4,720[/TD]
[TD="align: right"]$20,497[/TD]
[TD="align: right"]$35,001[/TD]
[TD="align: right"]$42,054[/TD]
[TD="align: right"]$28,596[/TD]
[TD="align: right"]$5,265[/TD]
[TD="align: right"]$3,256[/TD]
[TD="align: right"]$15,880[/TD]
[TD="align: right"]$34,592[/TD]
[TD="align: right"]$34,039[/TD]
[/TR]
[TR]
[TD]Bassam[/TD]
[TD]Fluroide[/TD]
[TD="align: right"]$3[/TD]
[TD="align: right"]$2,660[/TD]
[TD="align: right"]$23,119[/TD]
[TD="align: right"]$5,500[/TD]
[TD="align: right"]$29,915[/TD]
[TD="align: right"]$23,576[/TD]
[TD="align: right"]$39,683[/TD]
[TD="align: right"]$16,610[/TD]
[TD="align: right"]$17,937[/TD]
[TD="align: right"]$40,609[/TD]
[TD="align: right"]$29,920[/TD]
[/TR]
[TR]
[TD]Ghawth[/TD]
[TD]Signal[/TD]
[TD="align: right"]$6[/TD]
[TD="align: right"]$2,532[/TD]
[TD="align: right"]$2,910[/TD]
[TD="align: right"]$13,391[/TD]
[TD="align: right"]$23,389[/TD]
[TD="align: right"]$2,742[/TD]
[TD="align: right"]$41,157[/TD]
[TD="align: right"]$27,545[/TD]
[TD="align: right"]$8,243[/TD]
[TD="align: right"]$2,965[/TD]
[TD="align: right"]$23,088[/TD]
[/TR]
[TR]
[TD]Judy[/TD]
[TD]Flare[/TD]
[TD="align: right"]$7[/TD]
[TD="align: right"]$3,650[/TD]
[TD="align: right"]$4,302[/TD]
[TD="align: right"]$21,379[/TD]
[TD="align: right"]$42,989[/TD]
[TD="align: right"]$44,058[/TD]
[TD="align: right"]$2,169[/TD]
[TD="align: right"]$16,835[/TD]
[TD="align: right"]$6,672[/TD]
[TD="align: right"]$22,397[/TD]
[TD="align: right"]$13,874[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD]Flare[/TD]
[TD="align: right"]$7[/TD]
[TD="align: right"]$3,216[/TD]
[TD="align: right"]$25,260[/TD]
[TD="align: right"]$38,488[/TD]
[TD="align: right"]$12,435[/TD]
[TD="align: right"]$6,235[/TD]
[TD="align: right"]$25,875[/TD]
[TD="align: right"]$29,741[/TD]
[TD="align: right"]$32,106[/TD]
[TD="align: right"]$3,718[/TD]
[TD="align: right"]$37,861[/TD]
[/TR]
[TR]
[TD]Yahya[/TD]
[TD]Flare[/TD]
[TD="align: right"]$9[/TD]
[TD="align: right"]$1,429[/TD]
[TD="align: right"]$17,217[/TD]
[TD="align: right"]$38,507[/TD]
[TD="align: right"]$20,036[/TD]
[TD="align: right"]$30,885[/TD]
[TD="align: right"]$26,426[/TD]
[TD="align: right"]$23,691[/TD]
[TD="align: right"]$27,909[/TD]
[TD="align: right"]$13,554[/TD]
[TD="align: right"]$39,062[/TD]
[/TR]
[TR]
[TD]Najiyah[/TD]
[TD]Fluroide[/TD]
[TD="align: right"]$7[/TD]
[TD="align: right"]$641[/TD]
[TD="align: right"]$25,468[/TD]
[TD="align: right"]$1,382[/TD]
[TD="align: right"]$41,895[/TD]
[TD="align: right"]$33,026[/TD]
[TD="align: right"]$27,617[/TD]
[TD="align: right"]$28,380[/TD]
[TD="align: right"]$5,173[/TD]
[TD="align: right"]$42,534[/TD]
[TD="align: right"]$25,058[/TD]
[/TR]
[TR]
[TD]Nahid[/TD]
[TD]Fluroide[/TD]
[TD="align: right"]$9[/TD]
[TD="align: right"]$2,592[/TD]
[TD="align: right"]$16,044[/TD]
[TD="align: right"]$44,058[/TD]
[TD="align: right"]$39,204[/TD]
[TD="align: right"]$2,222[/TD]
[TD="align: right"]$37,545[/TD]
[TD="align: right"]$34,881[/TD]
[TD="align: right"]$41,851[/TD]
[TD="align: right"]$33,634[/TD]
[TD="align: right"]$26,368[/TD]
[/TR]
[TR]
[TD]Itab[/TD]
[TD]Flare[/TD]
[TD="align: right"]$10[/TD]
[TD="align: right"]$3,778[/TD]
[TD="align: right"]$3,287[/TD]
[TD="align: right"]$13,134[/TD]
[TD="align: right"]$24,028[/TD]
[TD="align: right"]$41,153[/TD]
[TD="align: right"]$42,918[/TD]
[TD="align: right"]$24,741[/TD]
[TD="align: right"]$30,223[/TD]
[TD="align: right"]$10,567[/TD]
[TD="align: right"]$37,699[/TD]
[/TR]
[TR]
[TD]Nashah[/TD]
[TD]Flare[/TD]
[TD="align: right"]$5[/TD]
[TD="align: right"]$1,312[/TD]
[TD="align: right"]$13,139[/TD]
[TD="align: right"]$1,178[/TD]
[TD="align: right"]$24,346[/TD]
[TD="align: right"]$21,672[/TD]
[TD="align: right"]$19,321[/TD]
[TD="align: right"]$6,651[/TD]
[TD="align: right"]$3,569[/TD]
[TD="align: right"]$16,775[/TD]
[TD="align: right"]$14,472[/TD]
[/TR]
</tbody>[/TABLE]


Sub UsingDates2()


Dim sht As Worksheet
Dim lastrow As Long
Dim names() As String
Dim namecount As Long
Dim rptsht As Worksheet
Dim x As Long
Dim i As Long
Dim currentname As String
Dim totals() As Long
Dim startdate As Integer
Dim enddate As Integer
Dim rng As Range


Set sht = Sheets("Sales")

'rpthsht is the output sheet
Set rptsht = Sheets("By Date")


startdate = sht.Cells.Find(what:=rptsht.Range("A2"), LookIn:=xlValues).Column ' there is an input cell for the start date
enddate = sht.Cells.Find(what:=rptsht.Range("B2"), LookIn:=xlValues).Column ' there is an input cell for the end date


lastrow = sht.Cells(Rows.Count, 1).End(xlUp).Row


rptsht.Range("D:E").Columns.Clear


sht.Range("A1:A" & lastrow).AdvancedFilter _
xlFilterCopy, copytorange:=rptsht.Range("D2"), Unique:=True


With rptsht.Range("D2")
namecount = Range(.Offset(1, 0), .Offset(1, 0).End(xlDown)).Rows.Count
ReDim names(namecount)
ReDim totals(namecount)
For x = 1 To namecount
names(x) = .Offset(x, 0).value
Next x
End With




With sht.Range("A1")
For x = 1 To Range(.Offset(1, 0), .Offset(1, 0).End(xlDown)).Rows.Count
currentname = .Offset(x, 0).value

For i = 1 To namecount
If currentname = names(i) Then
Set rng = sht.Cells(x + 1, startdate).Resize(1, (enddate - startdate + 1))
totals(i) = totals(i) + .Offset(x, 2).value * _
Application.WorksheetFunction.Sum(sht.Range(rng.Address))
End If
Next i


Next x
End With


With rptsht.Range("D2")
.Offset(0, 1).value = "Totals"
For i = 1 To namecount
.Offset(i, 1).value = totals(i)
Next i
Range(.Offset(0, 0), .Offset(0, 1).End(xlDown)).Select
End With


Call FormatNumbers
Call Borders
End Sub

 

[chriscorpion786]

Hi All,

Hope anyone can VBA code this one please.

Rgds,
Mustafa

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...mmary-report-by-name-and-category-in-vba.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.