Macro to subtotal column d at each change in column a, b & c

[JayK22]

Hello,

Is there someone who can help me, please?

I have a report:

FundCcyDateAmountabcUSD18/04/2008 5,000.00 abcUSD19/04/2008 4,000.00 abcEUR18/04/2008 2,000.00 abcJPY18/04/2008 100,000.00 xyzUSD18/04/2008 45,000.00 xyzUSD18/04/2008 40,000.00 xyzEUR18/04/2008 10,000.00 mnoJPY18/04/2008 30,000.00 mnoJPY19/04/2008 24,000.00 apMXN18/04/2008 130,000.00 apINR19/04/2008 230,000.00 mpINR18/04/2008 123,000.00 mpGBP19/04/2008 2,344,000.00

I would like to create a macro to format above report into below one:

FundCcySettlement DateAmountabcUSD18/04/2008 5,000.00 subtotalUSD 5,000.00 abcUSD19/04/2008 4,000.00 subtotalUSD 4,000.00 abcEUR18/04/2008 2,000.00 subtotalEUR 2,000.00 abcJPY18/04/2008 100,000.00 subtotalJPY 100,000.00 xyzUSD18/04/2008 45,000.00 xyzUSD18/04/2008 40,000.00 subtotalUSD 85,000.00 xyzEUR18/04/2008 10,000.00 subtotalEUR 10,000.00 mnoJPY18/04/2008 30,000.00 subtotalJPY 30,000.00 mnoJPY19/04/2008 24,000.00 subtotalJPY 24,000.00 apMXN18/04/2008 130,000.00 subtotalMXN 130,000.00 mpINR19/04/2008 230,000.00 mpINR19/04/2008 123,000.00 subtotalINR 353,000.00 mpGBP19/04/2008 2,344,000.00 subtotalGBP 2,344,000.00

For each subtotal, insert a blank line above and under.

Thank you very much for your help.

Jay

 

[c_m]

Jay,
Welcome to the Board.

Try to post that sample data again using:
http://www.excel-jeanie-html.de/index.php?f=1

 

[JayK22]

Sure - i will download excel jeanie from home tonite and will upload the data again.

many thansk for your help .

Jay

 

[JayK22]

Hi c_m,

I did downloaded excel jeanie but couldn't able to find out the link to send threads. can you pls advise.

thanks - jay

 

[JayK22]

Hi c_m,

i just posted thread at excel jeanie, when you have some time, could you pls have a look.

thanks, Jay

 

[JayK22]

Hi C_m,

I've the below data sorted by Fund, Ccy & Date. Now i would like to sum at proceeds column at each change in Fund, Ccy & date. Not sure if subtotal function would be appropriate or loop to insert rows and then sum at proceeds column. pls help as i'm not good with VBA.

Sheet1


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 70px"><COL style="WIDTH: 63px"><COL style="WIDTH: 71px"><COL style="WIDTH: 90px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffff00">Fund</TD><TD style="BACKGROUND-COLOR: #ffff00">Ccy</TD><TD style="BACKGROUND-COLOR: #ffff00">Date</TD><TD style="BACKGROUND-COLOR: #ffff00">Proceeds</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD> </TD><TD>abc</TD><TD>USD</TD><TD style="TEXT-ALIGN: right">18/04/2008</TD><TD style="TEXT-ALIGN: right"> 5,000.00 </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD> </TD><TD>abc</TD><TD>EUR</TD><TD style="TEXT-ALIGN: right">18/04/2008</TD><TD style="TEXT-ALIGN: right"> 2,000.00 </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD> </TD><TD>ap</TD><TD>MXN</TD><TD style="TEXT-ALIGN: right">18/04/2008</TD><TD style="TEXT-ALIGN: right"> 130,000.00 </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD> </TD><TD>ap</TD><TD>MXN</TD><TD style="TEXT-ALIGN: right">18/04/2008</TD><TD style="TEXT-ALIGN: right"> 130,000.00 </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD> </TD><TD>mno</TD><TD>JPY</TD><TD style="TEXT-ALIGN: right">18/04/2008</TD><TD style="TEXT-ALIGN: right"> 30,000.00 </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD> </TD><TD>mp</TD><TD>INR</TD><TD style="TEXT-ALIGN: right">18/04/2008</TD><TD style="TEXT-ALIGN: right"> 123,000.00 </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD> </TD><TD>xyz</TD><TD>USD</TD><TD style="TEXT-ALIGN: right">18/04/2008</TD><TD style="TEXT-ALIGN: right"> 45,000.00 </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD> </TD><TD>xyz</TD><TD>USD</TD><TD style="TEXT-ALIGN: right">18/04/2008</TD><TD style="TEXT-ALIGN: right"> 40,000.00 </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

The below is the result i would like to achive after macro.

Sheet1


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 70px"><COL style="WIDTH: 63px"><COL style="WIDTH: 71px"><COL style="WIDTH: 90px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffff00">Fund</TD><TD style="BACKGROUND-COLOR: #ffff00">Ccy</TD><TD style="BACKGROUND-COLOR: #ffff00">Date</TD><TD style="BACKGROUND-COLOR: #ffff00">Proceeds</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD> </TD><TD>abc</TD><TD>USD</TD><TD style="TEXT-ALIGN: right">18/04/2008</TD><TD style="TEXT-ALIGN: right"> 5,000.00 </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right"> 5,000.00 </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD> </TD><TD>abc</TD><TD>EUR</TD><TD style="TEXT-ALIGN: right">18/04/2008</TD><TD style="TEXT-ALIGN: right"> 2,000.00 </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right"> 2,000.00 </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD> </TD><TD>ap</TD><TD>MXN</TD><TD style="TEXT-ALIGN: right">18/04/2008</TD><TD style="TEXT-ALIGN: right"> 130,000.00 </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD> </TD><TD>ap</TD><TD>MXN</TD><TD style="TEXT-ALIGN: right">18/04/2008</TD><TD style="TEXT-ALIGN: right"> 130,000.00 </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right"> 260,000.00 </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD> </TD><TD>mno</TD><TD>JPY</TD><TD style="TEXT-ALIGN: right">18/04/2008</TD><TD style="TEXT-ALIGN: right"> 30,000.00 </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right"> 30,000.00 </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">29</TD><TD> </TD><TD>mp</TD><TD>INR</TD><TD style="TEXT-ALIGN: right">18/04/2008</TD><TD style="TEXT-ALIGN: right"> 123,000.00 </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">30</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right"> 123,000.00 </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">31</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">32</TD><TD> </TD><TD>xyz</TD><TD>USD</TD><TD style="TEXT-ALIGN: right">18/04/2008</TD><TD style="TEXT-ALIGN: right"> 45,000.00 </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">33</TD><TD> </TD><TD>xyz</TD><TD>USD</TD><TD style="TEXT-ALIGN: right">18/04/2008</TD><TD style="TEXT-ALIGN: right"> 40,000.00 </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">34</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right"> 85,000.00 </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">35</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">36</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">37</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

Many Thanks in advance - Jay