simple vba code to consolidate rows (sum up amounts)

[Brandonfromsingapore]

Hello, i have an excel file (sheet 1 only) that has data for 3 columns and multiple rows. The student number (ie name) are at column 1. The header is row 1.
The name of fruits (ie apple, lemon, pear, orange) are at column b and the number of fruits are at column c.

So it looks something like this below.

I know pivot table does the trick but is there a way to use code to consolidate this?

Student number	Fruits	Number of Fruits
3443	apple	3
3322	orange	2
3443	apple	44
5533	orange	1
3322	orange	9
5533	apple	4
3443	orange	78
5533	orange	3

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[hurgadion]

If Your Data there is in Columns A:C try the code:

 Sub Makro1()
Dim a&, b&, i&, j&, x&

Range("E:G").ClearContents

a = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:B" & a).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("E1"), Unique:=True

b = Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To b
  x = 0
  For j = 2 To a
    If Cells(i, 5).Value = Cells(j, 1).Value And Cells(i, 6).Value = Cells(j, 2).Value Then
      x = x + Cells(j, 3).Value
    End If
  Next j
Cells(i, 7).Value = x
Next i
End Sub

Best regards.

 

[Brandonfromsingapore]

Hello! Yes it works! Thanks! But it provided me with full numbers. Sometimes there are decimals (eg 5.665 in column c). Is there any way to not do rounding up? I like to keep the decimals. Thanks!

If Your Data there is in Columns A:C try the code:

 Sub Makro1()
Dim a&, b&, i&, j&, x&

Range("E:G").ClearContents

a = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:B" & a).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("E1"), Unique:=True

b = Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To b
  x = 0
  For j = 2 To a
    If Cells(i, 5).Value = Cells(j, 1).Value And Cells(i, 6).Value = Cells(j, 2).Value Then
      x = x + Cells(j, 3).Value
    End If
  Next j
Cells(i, 7).Value = x
Next i
End Sub

Best regards.

 

[hurgadion]

You have to change the declaration

x&

on

x As Double

Best regards.

 

[Brandonfromsingapore]

SOLVED!! Thank you so so much!!!

You have to change the declaration

x&

on

x As Double

Best regards.