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.

[TABLE="class: grid, width: 200, align: center"]
<tbody>[TR]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]Book A
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Book B
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Book C
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Book A
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Book C
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Book A
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]Book C
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Book B
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Book D
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 200, align: center"]
<tbody>[TR]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]Book A
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]Book B
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Book C
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]Book D
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

use PivotTable and put it on sheet2 or anywhere you want

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Book[/td][td]Sold[/td][td][/td][td=bgcolor:#DDEBF7]Book[/td][td=bgcolor:#DDEBF7]Sum of Sold[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Book A[/td][td]

1​

[/td][td][/td][td]Book A[/td][td]

12​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Book B[/td][td]

3​

[/td][td][/td][td]Book B[/td][td]

4​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Book C[/td][td]

4​

[/td][td][/td][td]Book C[/td][td]

8​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Book A[/td][td]

5​

[/td][td][/td][td]Book D[/td][td]

5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Book C[/td][td]

2​

[/td][td][/td][td=bgcolor:#DDEBF7]Grand Total[/td][td=bgcolor:#DDEBF7]

29​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Book A[/td][td]

6​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Book C[/td][td]

2​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Book B[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Book D[/td][td]

5​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[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