Combine and calculate based on string patterns

goku4 · Mar 14th 2013

I have attached a sample file.

I have a file that contains average user review on various products. The goal is to combine rows with similar products based on Model Number column and calculate the weighted scores in a new sheet with a summary.

Example:
I want to combine and do a weighted score on the Model Number that begins with SDAD and GES. Based on the file, there are four rows with the pattern SDAD and GES.

If you do the calculations manually:
For SDAD calculation, (10 x 4) + (3 x 5) = 55/15 = 3.6
For GES calculation, one row has no review, so it's just (5 x 3) = 15/3 = 5

I want to run a macro where it creates a new sheet in the same workbook and in the new sheet display a summary similar to the below. if I get a template, I can add more model patterns in there. Thank you!

SDAD Total Weight Score = 3.6
GES Total Weight Score = 5

dangle · Mar 15th 2013

Re: Combine and calculate based on string patterns

You could do this with a formula:

=IFERROR(SUMPRODUCT(--(LEFT($B$2:$B$8,FIND("-",B2)-1)=LEFT(B2,FIND("-",B2)-1))*($D$2:$D$8)*($F$2:$F$8))/SUMIF($B$2:$B$8,LEFT(B2,FIND("-",B2)-1)&"*",$F$2:$F$8),"")

(Copy to H2 and fill down)

But if you wanted VB:

Code

Sub AverageReviews()
Dim a, i As Long, tmp, z
Dim ws As Worksheet
a = Sheets("Sheet1").Cells(1).CurrentRegion.Value
With CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(a, 1)
        If a(i, 4) <> "" Then
            tmp = Mid(a(i, 2), 1, InStr(1, a(i, 2), "-") - 1)
            If Not .exists(tmp) Then
                .Item(tmp) = Array(tmp, (a(i, 4) * a(i, 6)) / a(i, 6), a(i, 4) * a(i, 6), a(i, 6))
            Else
                z = .Item(tmp)
                z(2) = z(2) + (a(i, 4) * a(i, 6)): z(3) = z(3) + a(i, 6)
                z(1) = z(2) / z(3)
                .Item(tmp) = z
            End If
        End If
    Next
    Set ws = Worksheets.Add(after:=Sheets(Worksheets.Count))
    ws.Cells(1).Resize(.Count, 2).Value = Application.Transpose(Application.Transpose(.items))
End With
End Sub

Display More

Combine and calculate based on string patterns

Participate now!

Share