Insert Subtotal Row based on Category on Different Sheet

[vwjettagirl26]

Hi,

I am fairly proficient with VBA, but have stumped myself. I have hundreds of columns (transactions/amounts) with hundreds of rows of data (names). I need to get my column data into a loadable format, which I am capable of doing. The part that is stumping me is adding a grouping line with the subtotal for that group. I essentially need to add a subtotal record to the end of each grouping, for a tiered posting, adding the grouping label to the Amounts tab is not feasible since there are many categories and at times a name can be included in multiple categories.

My workbook has 3 sheets:

Mapping - maps a group to each name

[TABLE="class: grid, width: 193"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Category[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]Group 1[/TD]
[TD]James Taylor[/TD]
[/TR]
[TR]
[TD]Group 1[/TD]
[TD]Robert Frost[/TD]
[/TR]
[TR]
[TD]Group 1[/TD]
[TD]Tom Brady[/TD]
[/TR]
[TR]
[TD]Group 1[/TD]
[TD]Michael Jo[/TD]
[/TR]
[TR]
[TD]Group 1[/TD]
[TD]Atticus Fince[/TD]
[/TR]
[TR]
[TD]Group 1[/TD]
[TD]Group 2[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]Glover One[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]James Madi[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]Roger Rabbit[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]Sugar Ray[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]Forrest Gump[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]Tom Hanks[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]Bubba Gump[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]John Smith[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]Arthur Mouse[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]Keith Rich[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]Group 3[/TD]
[/TR]
[TR]
[TD]Group 3[/TD]
[TD]Sam Winters[/TD]
[/TR]
[TR]
[TD]Group 3[/TD]
[TD]George Jet[/TD]
[/TR]
</tbody>[/TABLE]

Amounts - has amounts for each name

[TABLE="class: grid, width: 298"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]3/17/2015[/TD]
[/TR]
[TR]
[TD]James Taylor[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Robert Frost[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Tom Brady[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Michael Jo[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]Atticus Fince[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Glover One[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]James Madi[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Roger Rabbit[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]Sugar Ray[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Forrest Gump[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Tom Hanks[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]Bubba Gump[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]Arthur Mouse[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD]Keith Rich[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Sam Winters[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]George Jet[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]22[/TD]
[/TR]
</tbody>[/TABLE]

File - desired end result (includes subtotal line for each grouping from the Mapping tab)

[TABLE="class: grid, width: 294"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]3/17/2015[/TD]
[/TR]
[TR]
[TD]James Taylor[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Robert Frost[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Tom Brady[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Michael Jo[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]Atticus Fince[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]205[/TD]
[TD="align: right"]155[/TD]
[/TR]
[TR]
[TD]Glover One[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]James Madi[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Roger Rabbit[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]Sugar Ray[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Forrest Gump[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Tom Hanks[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]Bubba Gump[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]Arthur Mouse[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD]Keith Rich[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Group 3[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]43[/TD]
[/TR]
[TR]
[TD]Sam Winters[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]George Jet[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]22[/TD]
[/TR]
</tbody>[/TABLE]

I think I may need to use an array, but may also be over complicating it. Many thanks in advance.

 

[vwjettagirl26]

My desired output is incorrect, it should be:

The following formula yields 41 rather than the expected 105, {=SUMPRODUCT(Amounts!B2:B18*(LOOKUP(Amounts!A2:A18,Mapping!B:B,Mapping!C:C)="Group 2"))}

File - desired end result (includes subtotal line for each grouping from the Mapping tab)
[TABLE="width: 255"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]James Taylor[/TD]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Robert Frost[/TD]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Tom Brady[/TD]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Michael Jordan[/TD]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Atticus Fince[/TD]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]105[/TD]
[/TR]
[TR]
[TD]Glover One[/TD]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]James Madison [/TD]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Roger Rabbit[/TD]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Sugar Ray[/TD]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]Forrest Gump[/TD]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Tom Hanks[/TD]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]Bubba Gump[/TD]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]Arthur Mouse[/TD]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Keith Richards[/TD]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Group 3[/TD]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]Sam Winters[/TD]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]George Jetson[/TD]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]James Taylor[/TD]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]Robert Frost[/TD]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Tom Brady[/TD]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]Michael Jordan[/TD]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Atticus Fince[/TD]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]205[/TD]
[/TR]
[TR]
[TD]Glover One[/TD]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]James Madison [/TD]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]17[/TD]
[/TR]
</tbody>[/TABLE]

 

[vwjettagirl26]

Resolved with formula: {=SUM(SUMIF(Amounts!A:A,IF(Mapping!C:C="Group 2",Mapping!B:B),Amounts!B:B))}

Will now build code to make it more dynamic