Making one row of many

**Marcell** · 12-20-2012, 08:01 AM

Hi Guys

I'm looking for a way to make one row of many others, and summarize only the numbers they contain. My example is the following:

Raw data:

A1: Anything, B1: 2
A2: Something, B2: 6
A3: Something, B3: 1
A4: Nothing, B4: 1

The result I want:

A1: Anything, B1: 2
A2: Something, B2: 7
A3: Nothing, B4: 1

**nested.if1@gmail.com** · 12-20-2012, 08:10 AM

Hi,

If I am getting it correctly....you need the sum of values in colum B if condition matches.

I think Sumif function will be helpful. Please check the attached s/s.

**Kyle123** · 12-20-2012, 08:33 AM

or a pivot table

**Marcell** · 12-21-2012, 07:29 AM

Thank you Guys! But I was looking for a VBA solution. So I'm going to make a similar post and ask my question more correctly.

**mike7952** · 12-21-2012, 08:10 AM

This might work

Sub abc()
 Dim a, i As Long
 
 a = Range("a1", Cells(Rows.Count, "a").End(xlUp).Offset(, 1))
 
 With CreateObject("scripting.dictionary")
    .comparemode = 1
    For i = 1 To UBound(a)
        If Not .exists(a(i, 1)) Then
            .Item(a(i, 1)) = Array(a(i, 1), a(i, 2))
        Else
             a(i, 2) = .Item(a(i, 1))(1) + a(i, 2)
            .Item(a(i, 1)) = Array(a(i, 1), a(i, 2))
        End If
    Next
    a = .items
 End With
 
 Worksheets.Add
 For i = 0 To UBound(a)
    Cells(i + 1, "A") = a(i)(0)
    Cells(i + 1, "B") = a(i)(1)
 Next
End Sub