Filtering Data and Sum the Values in the Adjacent Column

[madhuchelliah]

Hello Folks, i am preparing the sales report of books. I want to filter a book name in column C and want to add the number of books sold. the result should be in next sheet column C book name and column D total sold quantity. Please see the example below.

C	D
Book A	1
Book B	3
Book C	4
Book A	5
Book C	2
Book A	6
Book C	2
Book B	1
Book D	5

<tbody>
</tbody>

C	D
Book A	12
Book B	4
Book C	8
Book D	5

<tbody>
</tbody>

 

[sandy666]

use PivotTable and put it on sheet2 or anywhere you want

Book	Sold		Book	Sum of Sold
Book A	1​		Book A	12​
Book B	3​		Book B	4​
Book C	4​		Book C	8​
Book A	5​		Book D	5​
Book C	2​		Grand Total	29​
Book A	6​
Book C	2​
Book B	1​
Book D	5​

 

[madhuchelliah]

use PivotTable and put it on sheet2 or whereever you want

Thanks for your suggestion. But i am running batch of macros to complete a process. This is one among that, so i think macros is the good solution.

 

[sandy666]

I do not see any mention of vba in the title or post

have a nice day

 

[madhuchelliah]

I do not see any mention of vba in the title or post

have a nice day

Oh that's my mistake. My apologies.

 

[Fluff]

How about

Sub madhuchelliah()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      .comparemode = 1
      For Each Cl In Range("C2", Range("C" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = .Item(Cl.Value) + Cl.Offset(, 1).Value
      Next Cl
      Sheets("sheet2").Range("C2").Resize(.Count, 2).Value = Application.Transpose(Array(.Keys, .Items))
   End With
End Sub