Summarize by Name and 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.
Mustafa


[TABLE="class: cms_table, width: 959"]
<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][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/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

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel-questions/1057237-summary-report-category-vba.html#post5076591

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

If you do not receive a response, you can "bump" it by replying to it again, though we advise you to wait 24 hours before doing and not to bump a thread more than once a day.