Add and calculate very 4th row

blarp1

New Member
Joined
Jan 11, 2011
Messages
29
I have a spreadsheet that I would like to add a row every 4th row and subtotal the numbers in the columns D-H (last five columns:
<TABLE style="WIDTH: 590pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=786 border=0><COLGROUP><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5339" width=146><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 48pt" height=64><TD class=xl63 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 110pt; BORDER-BOTTOM: #e0e0e0; HEIGHT: 48pt; BACKGROUND-COLOR: transparent" width=146 height=64>Service Name </TD><TD class=xl63 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 75pt; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" width=100>Half Hour </TD><TD class=xl63 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 95pt; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" width=127>Date </TD><TD class=xl63 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 78pt; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" width=104>Calls</TD><TD class=xl63 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 70pt; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" width=93>Abandoned Calls </TD><TD class=xl63 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 66pt; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" width=88>Queue Time For Abandoned Calls </TD><TD class=xl65 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 48pt; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" width=64>Avg Speed Of Answer </TD><TD class=xl65 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 48pt; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" width=64>Avg Abandon Time </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>C_InboundFMAC</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">08:00</TD><TD class=xl67 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">1/10/2011 :: AM</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">12</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:19</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>C_InboundFMAC</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">08:00</TD><TD class=xl67 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">1/10/2011 :: AM</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">13</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">2</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:13</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:20</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:07</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>C_InboundMortgageColDirect</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">08:00</TD><TD class=xl67 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">1/10/2011 :: AM</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>C_InboundMortgageColDirect</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">08:00</TD><TD class=xl67 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">1/10/2011 :: AM</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>C_InboundFMAC</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">08:30</TD><TD class=xl67 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">1/10/2011 :: AM</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">9</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:19</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>C_InboundFMAC</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">08:30</TD><TD class=xl67 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">1/10/2011 :: AM</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">12</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:19</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>C_InboundMortgageColDirect</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">08:30</TD><TD class=xl67 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">1/10/2011 :: AM</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>C_InboundMortgageColDirect</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">08:30</TD><TD class=xl67 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">1/10/2011 :: AM</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>C_InboundFMAC</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">09:00</TD><TD class=xl67 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">1/10/2011 :: AM</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">10</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:19</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>C_InboundFMAC</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">09:00</TD><TD class=xl67 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">1/10/2011 :: AM</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">24</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:24</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>C_InboundMortgageColDirect</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">09:00</TD><TD class=xl67 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">1/10/2011 :: AM</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>C_InboundMortgageColDirect</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">09:00</TD><TD class=xl67 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">1/10/2011 :: AM</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">1</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0

</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:01:35</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD></TR></TBODY></TABLE>

This goes on for several hundred lines. Any assistance would be appreciated. Thanks
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,648
blarp1,

Welcome to the MrExcel forum.

When I try to copy your data into Excel, everything comes in as text.


What version of Excel are you using?

Please attach screenshots of your workbook or a sample workbook that accurately portrays your current workbook on one sheet, and what it should look like 'After' on another sheet.

This makes it much easier to see exactly what you want to do, as well as shows us whether there is a consistent number of rows between tables and such.

Here are three possible ways to post small (copyable) screen shots directly in your post:

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

or
RichardSchollar’s beta HTML Maker - see the red text in his signature block at the bottom of his post

or
Borders-Copy-Paste


Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.
 

blarp1

New Member
Joined
Jan 11, 2011
Messages
29
I am unable to upload the sheet, but essentially I am just adding a blank line after every fourth line (will always be after the second C_InboundMortgageColDirect)) then sum the last five columns in the blank line produced by the first part...so it would look like this:

<TABLE style="WIDTH: 523pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=697 border=0><COLGROUP><COL style="WIDTH: 134pt; mso-width-source: userset; mso-width-alt: 6546" width=179><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4900" width=134><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 134pt; BORDER-BOTTOM: #e0e0e0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=179 height=20>C_InboundFMAC</TD><TD class=xl63 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 48pt; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" width=64>08:00</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 101pt; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" width=134>1/10/2011 :: AM</TD><TD class=xl65 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 48pt; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" width=64>12</TD><TD class=xl65 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 48pt; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" width=64>0</TD><TD class=xl66 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 48pt; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" width=64>00:00:00</TD><TD class=xl66 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 48pt; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" width=64>0:00:19</TD><TD class=xl66 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 48pt; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" width=64>00:00:00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>C_InboundFMAC</TD><TD class=xl63 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">08:00</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">1/10/2011 :: AM</TD><TD class=xl65 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">13</TD><TD class=xl65 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">2</TD><TD class=xl66 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0:00:13</TD><TD class=xl66 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0:00:20</TD><TD class=xl66 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0:00:07</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>C_InboundMortgageColDirect</TD><TD class=xl63 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">08:00</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">1/10/2011 :: AM</TD><TD class=xl65 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0</TD><TD class=xl65 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD><TD class=xl66 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD><TD class=xl66 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>C_InboundMortgageColDirect</TD><TD class=xl63 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">08:00</TD><TD class=xl64 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">1/10/2011 :: AM</TD><TD class=xl65 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0</TD><TD class=xl65 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD><TD class=xl66 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD><TD class=xl66 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">00:00:00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 15pt; BACKGROUND-COLOR: yellow" height=20></TD><TD class=xl67 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: yellow">08:00</TD><TD class=xl68 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: yellow"></TD><TD class=xl69 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: yellow">25</TD><TD class=xl69 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: yellow">2</TD><TD class=xl70 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: yellow">0:00:13</TD><TD class=xl70 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: yellow">0:00:39</TD><TD class=xl70 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: yellow">0:00:07</TD></TR></TBODY></TABLE>
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,648
blarp1,


Sample data before the macro:


Excel Workbook
ABCDEFGH
1Service NameHalf HourDateCallsAbandoned CallsQueue Time For Abandoned CallsAvg Speed Of AnswerAvg Abandon Time
2C_InboundFMAC8:001/10/2011 :: AM1200:00:000:00:190:00:00
3C_InboundFMAC8:001/10/2011 :: AM1320:00:130:00:200:00:07
4C_InboundMortgageColDirect8:001/10/2011 :: AM000:00:000:00:000:00:00
5C_InboundMortgageColDirect8:001/10/2011 :: AM000:00:000:00:000:00:00
6C_InboundFMAC8:301/10/2011 :: AM900:00:000:00:190:00:00
7C_InboundFMAC8:301/10/2011 :: AM1200:00:000:00:190:00:00
8C_InboundMortgageColDirect8:301/10/2011 :: AM000:00:000:00:000:00:00
9C_InboundMortgageColDirect8:301/10/2011 :: AM000:00:000:00:000:00:00
10C_InboundFMAC9:001/10/2011 :: AM1000:00:000:00:190:00:00
11C_InboundFMAC9:001/10/2011 :: AM2400:00:000:00:240:00:00
12C_InboundMortgageColDirect9:001/10/2011 :: AM000:00:000:00:000:00:00
13C_InboundMortgageColDirect9:001/10/2011 :: AM100:00:000:01:350:00:00
14
Sheet1





After the macro:


Excel Workbook
ABCDEFGH
1Service NameHalf HourDateCallsAbandoned CallsQueue Time For Abandoned CallsAvg Speed Of AnswerAvg Abandon Time
2C_InboundFMAC8:001/10/2011 :: AM1200:00:000:00:190:00:00
3C_InboundFMAC8:001/10/2011 :: AM1320:00:130:00:200:00:07
4C_InboundMortgageColDirect8:001/10/2011 :: AM000:00:000:00:000:00:00
5C_InboundMortgageColDirect8:001/10/2011 :: AM000:00:000:00:000:00:00
62520:00:130:00:390:00:07
7C_InboundFMAC8:301/10/2011 :: AM900:00:000:00:190:00:00
8C_InboundFMAC8:301/10/2011 :: AM1200:00:000:00:190:00:00
9C_InboundMortgageColDirect8:301/10/2011 :: AM000:00:000:00:000:00:00
10C_InboundMortgageColDirect8:301/10/2011 :: AM000:00:000:00:000:00:00
112100:00:000:00:380:00:00
12C_InboundFMAC9:001/10/2011 :: AM1000:00:000:00:190:00:00
13C_InboundFMAC9:001/10/2011 :: AM2400:00:000:00:240:00:00
14C_InboundMortgageColDirect9:001/10/2011 :: AM000:00:000:00:000:00:00
15C_InboundMortgageColDirect9:001/10/2011 :: AM100:00:000:01:350:00:00
163500:00:000:02:180:00:00
17
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code:
Option Explicit
Sub SumGroups()
' hiker95, 01/11/2010
' http://www.mrexcel.com/forum/showthread.php?t=520606
Dim LR As Long, a As Long, MySum As String
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
For a = LR To 2 Step -1
  If Range("A" & a) = "C_InboundMortgageColDirect" And Range("A" & a - 1) = "C_InboundMortgageColDirect" Then
    MySum = ""
    Rows(a + 1).EntireRow.Insert
    MySum = "=Sum(D" & a - 3 & ":D" & a & ")"
    Range("D" & a + 1) = MySum
    Range("D" & a + 1).AutoFill Destination:=Range("D" & a + 1 & ":H" & a + 1)
    Range("F" & a + 1 & ":H" & a + 1).NumberFormat = "[h]:mm:ss;@"
  End If
Next a
Application.ScreenUpdating = True
End Sub

Then run the SumGroups macro.


I could make all the SUM formulae bold.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,648
blarp1,


For the formulae to be bold:


Excel Workbook
ABCDEFGH
1Service NameHalf HourDateCallsAbandoned CallsQueue Time For Abandoned CallsAvg Speed Of AnswerAvg Abandon Time
2C_InboundFMAC8:001/10/2011 :: AM1200:00:000:00:190:00:00
3C_InboundFMAC8:001/10/2011 :: AM1320:00:130:00:200:00:07
4C_InboundMortgageColDirect8:001/10/2011 :: AM000:00:000:00:000:00:00
5C_InboundMortgageColDirect8:001/10/2011 :: AM000:00:000:00:000:00:00
62520:00:130:00:390:00:07
7C_InboundFMAC8:301/10/2011 :: AM900:00:000:00:190:00:00
8C_InboundFMAC8:301/10/2011 :: AM1200:00:000:00:190:00:00
9C_InboundMortgageColDirect8:301/10/2011 :: AM000:00:000:00:000:00:00
10C_InboundMortgageColDirect8:301/10/2011 :: AM000:00:000:00:000:00:00
112100:00:000:00:380:00:00
12C_InboundFMAC9:001/10/2011 :: AM1000:00:000:00:190:00:00
13C_InboundFMAC9:001/10/2011 :: AM2400:00:000:00:240:00:00
14C_InboundMortgageColDirect9:001/10/2011 :: AM000:00:000:00:000:00:00
15C_InboundMortgageColDirect9:001/10/2011 :: AM100:00:000:01:350:00:00
163500:00:000:02:180:00:00
17
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


Rich (BB code):
Option Explicit
Sub SumGroups()
' hiker95, 01/11/2010
' http://www.mrexcel.com/forum/showthread.php?t=520606
Dim LR As Long, a As Long, MySum As String
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
For a = LR To 2 Step -1
  If Range("A" & a) = "C_InboundMortgageColDirect" And Range("A" & a - 1) = "C_InboundMortgageColDirect" Then
    MySum = ""
    Rows(a + 1).EntireRow.Insert
    MySum = "=Sum(D" & a - 3 & ":D" & a & ")"
    Range("D" & a + 1) = MySum
    Range("D" & a + 1).AutoFill Destination:=Range("D" & a + 1 & ":H" & a + 1)
    Range("F" & a + 1 & ":H" & a + 1).NumberFormat = "[h]:mm:ss;@"
    Range("D" & a + 1 & ":H" & a + 1).Font.Bold = True
  End If
Next a
Application.ScreenUpdating = True
End Sub

Then run the SumGroups macro.
 

blarp1

New Member
Joined
Jan 11, 2011
Messages
29
Works great. Looks like I need to work on the format for the cells with times...summation comes up with 0:00:00...only way the summation works is if I type in the time in the upper cells that are being added. Since the sheet is a transfer from a business objects report for some reason just changing the cell format is not correcting the problem. Great work on your part and I appreciate the time. Sorry I didn't get back sooner but my shift at work was over before I saw your answer.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,648
blarp1,

In order for the macro to work with you posted data, I had to copy each of the columns (D thru H ), one at a time to an empty column, and do a PasteSpecial Values Add, which turned them back into numbers. Then they were copied back to their original column.

If columns D thru H are coming in as TEXT, I can change them in the macro, now that I know the problem.

Is this acceptable?
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,648
blarp1,


Try the updated code on your raw data.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


Code:
Option Explicit
Option Base 1
Sub SumGroupsV2()
' hiker95, 01/12/2010
' http://www.mrexcel.com/forum/showthread.php?t=520606
Dim LR As Long, a As Long, MySum As String, CAry, C As String
Application.ScreenUpdating = False
CAry = Array("D", "E", "F", "G", "H")
LR = Cells(Rows.Count, 1).End(xlUp).Row
Range("J2:J" & LR).Clear
For a = LBound(CAry) To UBound(CAry)
  C = CAry(a)
  Range(C & "2:" & C & LR).Copy
  Range("J2").PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False
  Range("J2:J" & LR).Copy Range(C & "2")
  Range("J2:J" & LR).Clear
Next a
Range("F2:H" & LR).NumberFormat = "[h]:mm:ss;@"
Range("A1").Select
For a = LR To 2 Step -1
  If Range("A" & a) = "C_InboundMortgageColDirect" And Range("A" & a - 1) = "C_InboundMortgageColDirect" Then
    MySum = ""
    Rows(a + 1).EntireRow.Insert
    MySum = "=Sum(D" & a - 3 & ":D" & a & ")"
    Range("D" & a + 1) = MySum
    Range("D" & a + 1).AutoFill Destination:=Range("D" & a + 1 & ":H" & a + 1)
    Range("F" & a + 1 & ":H" & a + 1).NumberFormat = "[h]:mm:ss;@"
    Range("D" & a + 1 & ":H" & a + 1).Font.Bold = True
  End If
Next a
Application.ScreenUpdating = True
End Sub

Then run SumGroupsV2 macro.
 

Forum statistics

Threads
1,085,255
Messages
5,382,600
Members
401,797
Latest member
meloanthony

Some videos you may like

This Week's Hot Topics

Top