SUM rows based on multiple criteria and insert below

[sjk1193]

So what i need to do is sum the values in the Units and amount columns if :
- the person is the same
- "x" starting number of characters is same in the desc column (ex. one__ or three____)
- the Cur being "U"

Then i need to insert a new BOLDED row underneath each change in colour in the rows with the sums of the units and amount columns


[TABLE="width: 500"]
<tbody>[TR]
[TD]group[/TD]
[TD]Person[/TD]
[TD]Desc[/TD]
[TD]Cur[/TD]
[TD]xx[/TD]
[TD]Units[/TD]
[TD]xx[/TD]
[TD]amount[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]abc[/TD]
[TD]one - 1[/TD]
[TD]U[/TD]
[TD]xx[/TD]
[TD]531[/TD]
[TD]xx[/TD]
[TD]35146[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]abc[/TD]
[TD]one - 23[/TD]
[TD]U[/TD]
[TD]xx[/TD]
[TD]785[/TD]
[TD]xx[/TD]
[TD]584684[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]abc[/TD]
[TD]one - 4 [/TD]
[TD]U[/TD]
[TD]xx[/TD]
[TD]9[/TD]
[TD]xx[/TD]
[TD]4125[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]def[/TD]
[TD]one - 1[/TD]
[TD]U[/TD]
[TD]xx[/TD]
[TD]-65[/TD]
[TD]xx[/TD]
[TD]452[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]def[/TD]
[TD]one - 23[/TD]
[TD]U[/TD]
[TD]xx[/TD]
[TD]45[/TD]
[TD]xx[/TD]
[TD]-52[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]def[/TD]
[TD]one - 4 [/TD]
[TD]U[/TD]
[TD]xx[/TD]
[TD]41[/TD]
[TD]xx[/TD]
[TD]852[/TD]
[/TR]
[TR]
[TD]333[/TD]
[TD]hij[/TD]
[TD]three- 9[/TD]
[TD]U[/TD]
[TD]xx[/TD]
[TD]-546[/TD]
[TD]xx[/TD]
[TD]452[/TD]
[/TR]
[TR]
[TD]333[/TD]
[TD]hij[/TD]
[TD]three - 4[/TD]
[TD]U[/TD]
[TD]xx[/TD]
[TD]254[/TD]
[TD]xx[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD]klm[/TD]
[TD]two - 9[/TD]
[TD]U[/TD]
[TD]xx[/TD]
[TD]-2[/TD]
[TD]xx[/TD]
[TD]2263[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD]klm[/TD]
[TD]two - 4[/TD]
[TD]U[/TD]
[TD]xx[/TD]
[TD]45[/TD]
[TD]xx[/TD]
[TD]-35[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD]klm[/TD]
[TD]two- 6[/TD]
[TD]C[/TD]
[TD]xx[/TD]
[TD]345[/TD]
[TD]xx[/TD]
[TD]4587[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD]klm[/TD]
[TD]two - 8[/TD]
[TD]C[/TD]
[TD]xx[/TD]
[TD]35[/TD]
[TD]xx[/TD]
[TD]753[/TD]
[/TR]
</tbody>[/TABLE]


so after it would kind of look like it looks for the brown rows for all of the colours

[TABLE="width: 500"]
<tbody>[TR]
[TD]group[/TD]
[TD]Person[/TD]
[TD]Desc[/TD]
[TD]Cur[/TD]
[TD]xx[/TD]
[TD]Units[/TD]
[TD]xx[/TD]
[TD]amount[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]abc[/TD]
[TD]one - 1[/TD]
[TD]U[/TD]
[TD]xx[/TD]
[TD]531[/TD]
[TD]xx[/TD]
[TD]35146[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]abc[/TD]
[TD]one - 23[/TD]
[TD]U[/TD]
[TD]xx[/TD]
[TD]785[/TD]
[TD]xx[/TD]
[TD]584684[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]abc[/TD]
[TD]one - 4 [/TD]
[TD]U[/TD]
[TD]xx[/TD]
[TD]9[/TD]
[TD]xx[/TD]
[TD]4125[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]abc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1325[/TD]
[TD][/TD]
[TD]623955[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]def[/TD]
[TD]one - 1[/TD]
[TD]U[/TD]
[TD]xx[/TD]
[TD]-65[/TD]
[TD]xx[/TD]
[TD]452[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]def[/TD]
[TD]one - 23[/TD]
[TD]U[/TD]
[TD]xx[/TD]
[TD]45[/TD]
[TD]xx[/TD]
[TD]52[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]def[/TD]
[TD]one - 4 [/TD]
[TD]U[/TD]
[TD]xx[/TD]
[TD]41[/TD]
[TD]xx[/TD]
[TD]852[/TD]
[/TR]
[TR]
[TD]333[/TD]
[TD]hij[/TD]
[TD]three- 9[/TD]
[TD]U[/TD]
[TD]xx[/TD]
[TD]-546[/TD]
[TD]xx[/TD]
[TD]452[/TD]
[/TR]
[TR]
[TD]333[/TD]
[TD]hij[/TD]
[TD]three - 4[/TD]
[TD]U[/TD]
[TD]xx[/TD]
[TD]254[/TD]
[TD]xx[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD]klm[/TD]
[TD]two - 9[/TD]
[TD]U[/TD]
[TD]xx[/TD]
[TD]-2[/TD]
[TD]xx[/TD]
[TD]2263[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD]klm[/TD]
[TD]two - 4[/TD]
[TD]U[/TD]
[TD]xx[/TD]
[TD]45[/TD]
[TD]xx[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD]klm[/TD]
[TD]two- 6[/TD]
[TD]C[/TD]
[TD]xx[/TD]
[TD]345[/TD]
[TD]xx[/TD]
[TD]4587[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD]klm[/TD]
[TD]two - 8[/TD]
[TD]C[/TD]
[TD]xx[/TD]
[TD]35[/TD]
[TD]xx[/TD]
[TD]753

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Johnny Thunder]

Why not just make a pivot table? You could get the desired result for all the columns that have the matching criteria you are explaining in your question.

 

[sjk1193]

I think your right, I've been thinking to complex

 

[Johnny Thunder]

It's fine. We've all done it before. Sometimes over engineering a solution that Excel can already handle with Pivots or other means.

If the data set grows one approach for automation with a macro could be for you to give your pivot source data a named range so that you can control that aspect with a macro that redefines the last row as the report grows.