 # Combine and calculate based on string patterns

• 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

## Files

• 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:

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!