VBA code to sum duplicate rows based on Uniuqe ID and t copy latest row based on date criteria

[motherindia]

Hello Sir,

I have 2 sheets. Sheet 1 contains 5 columns and Cust No is unique id. I need to combine the Column " Amt" based on Cust No and also to copy the latest value for Type /1002 against SAP Period in separate column. Output is shown below to be appeared on Sheet 2.

I need to automate to do this in VBA.
[TABLE="class: cms_table, width: 624"]
<tbody>[TR]
[TD="colspan: 2"]Input on Sheet 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust No[/TD]
[TD]Name[/TD]
[TD]SAP Period[/TD]
[TD]Type[/TD]
[TD]Amt[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]201005[/TD]
[TD]/1001[/TD]
[TD]-30,637.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]200804[/TD]
[TD]/1001[/TD]
[TD]-1,974.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]200805[/TD]
[TD]/1001[/TD]
[TD]1,000.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]C[/TD]
[TD]200810[/TD]
[TD]/1001[/TD]
[TD]-8,606.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]D[/TD]
[TD]200802[/TD]
[TD]/1001[/TD]
[TD]-2,074.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]D[/TD]
[TD]200803[/TD]
[TD]/1002[/TD]
[TD]-10,721.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]D[/TD]
[TD]200805[/TD]
[TD]/1002[/TD]
[TD]-2,531.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Output on Sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust No[/TD]
[TD][/TD]
[TD]Total (/1001+/1002)[/TD]
[TD]//1002[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]-30,637.00[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]-974.00[/TD]
[TD]1,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]-8,606.00[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]-15,236.00[/TD]
[TD]-2,531.00[/TD]
[/TR]
</tbody>[/TABLE]


Regards,
motherindia

 

[Peter_SSs]

Duplicate to https://www.mrexcel.com/forum/excel-questions/1022798-vba-code-change-sheet-1-data-per-sheet-2-a.html

Please do not post the same question multiple times. Questions of a duplicate nature will be locked or deleted, per #12 of the Forum Rules and points 6 & 7 of the Forum Use Guidelines.

Any bumps, clarifications, or follow-ups should be posted to the linked thread.