Sub-total, Grand-total with dynamic Range - VBA

L

luvbite38

Active Member
Joined
Jun 25, 2008
Messages
368
Hi all,

I am hoping to get some help from experts:rolleyes:.

I have a table which summarises the total cost of the items (see Table 1). However, I need a macro which insert two rows under each Main category in Column A (where my main catergory is 1, 2 or so on and sub category is 1.1, 1.2 ..... etc). And after adding two rows under each main category, it should find the value in header (the value = "Total Cost") and sub-total each category. and right at the end it must provide a grand total of all the sub-totals.

I have attached my existing and desired summary sheets in Table1 and 2 below for better understanding.

More importantly, a user can add as many items into the spreadsheets (right now it only shows Apple and Banana but user can add more columns and items) so the sub-total should be done under Total Cost. Similarly, user can add other cities or in other words other sub-categories so the sub-total range has to be dynamic.

Thank you very much in advance.

Kind Regards,

:confused:


Existing Summary

<TABLE style="WIDTH: 245pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=326 border=0><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><TBODY><TR style="HEIGHT: 30pt" height=40><TD class=xl76 style="BORDER-RIGHT: #f2f2f2 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #ffffff; HEIGHT: 146.25pt; BACKGROUND-COLOR: #0065a4" width=64 height=195 rowSpan=5>Seq. No</TD><TD class=xl77 style="BORDER-RIGHT: #f2f2f2 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #f2f2f2 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #f2f2f2 0.5pt solid; BACKGROUND-COLOR: #0065a4" width=64 rowSpan=5>Location</TD><TD class=xl79 style="BORDER-RIGHT: #ffffff; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #f2f2f2; WIDTH: 96pt; BORDER-BOTTOM: #f2f2f2 0.5pt solid; BACKGROUND-COLOR: #538dd5" width=128 colSpan=2>Item Details</TD><TD class=xl81 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #f2f2f2 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #f2f2f2 0.5pt solid; BACKGROUND-COLOR: #0065a4" width=70 rowSpan=5>Total Cost </TD></TR><TR style="HEIGHT: 56.25pt; mso-height-source: userset" height=75><TD class=xl65 style="BORDER-RIGHT: #f2f2f2 0.5pt solid; BORDER-TOP: #f2f2f2; BORDER-LEFT: #f2f2f2; WIDTH: 48pt; BORDER-BOTTOM: #f2f2f2 0.5pt solid; HEIGHT: 56.25pt; BACKGROUND-COLOR: #538dd5" width=64 height=75>Apple</TD><TD class=xl65 style="BORDER-RIGHT: #f2f2f2 0.5pt solid; BORDER-TOP: #f2f2f2; BORDER-LEFT: #f2f2f2; WIDTH: 48pt; BORDER-BOTTOM: #f2f2f2 0.5pt solid; BACKGROUND-COLOR: #538dd5" width=64>Banana</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #f2f2f2 0.5pt solid; BORDER-TOP: #f2f2f2; BORDER-LEFT: #f2f2f2; WIDTH: 48pt; BORDER-BOTTOM: #f2f2f2 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #538dd5" width=64 height=20></TD><TD class=xl66 style="BORDER-RIGHT: #f2f2f2 0.5pt solid; BORDER-TOP: #f2f2f2; BORDER-LEFT: #f2f2f2; WIDTH: 48pt; BORDER-BOTTOM: #f2f2f2 0.5pt solid; BACKGROUND-COLOR: #538dd5" width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #f2f2f2; BORDER-LEFT: #f2f2f2; WIDTH: 48pt; BORDER-BOTTOM: #f2f2f2 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #538dd5" width=64 height=20>20</TD><TD class=xl67 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #f2f2f2; BORDER-LEFT: #f2f2f2 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #f2f2f2 0.5pt solid; BACKGROUND-COLOR: #538dd5" width=64>30</TD></TR><TR style="HEIGHT: 30pt" height=40><TD class=xl70 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #f2f2f2 0.5pt solid; BORDER-LEFT: #f2f2f2; BORDER-BOTTOM: #ffffff; HEIGHT: 30pt; BACKGROUND-COLOR: #0065a4" colSpan=2 height=40>No. of items sold</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl85 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>1</TD><TD class=xl72 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9 0.5pt solid; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>Australia</TD><TD class=xl74 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9 0.5pt solid; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white"></TD><TD class=xl74 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9 0.5pt solid; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white"></TD><TD class=xl86 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d9d9d9 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: white; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 53pt; COLOR: white; BORDER-BOTTOM: #d9d9d9 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" width=70>£ - </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl87 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>1.1</TD><TD class=xl75 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>Brisbane</TD><TD class=xl73 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>3000</TD><TD class=xl73 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>7000</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 53pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=70>£ 270,000 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl87 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>1.2</TD><TD class=xl75 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>Perth</TD><TD class=xl74 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white">2020</TD><TD class=xl74 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white">8000</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 53pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=70>£ 280,400 </TD></TR><TR style="HEIGHT: 24pt" height=32><TD class=xl85 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 24pt; BACKGROUND-COLOR: white" width=64 height=32>2</TD><TD class=xl72 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>United Kingdom</TD><TD class=xl74 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white"></TD><TD class=xl74 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white"></TD><TD class=xl86 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d9d9d9 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: white; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 53pt; COLOR: white; BORDER-BOTTOM: #d9d9d9 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" width=70>£ - </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl87 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>2.1</TD><TD class=xl75 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>London</TD><TD class=xl74 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white">2500</TD><TD class=xl74 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white">2600</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 53pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=70>£ 128,000 </TD></TR><TR style="HEIGHT: 24pt" height=32><TD class=xl87 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 24pt; BACKGROUND-COLOR: white" width=64 height=32>2.2</TD><TD class=xl75 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>Manchester</TD><TD class=xl74 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white">1500</TD><TD class=xl74 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white">1600</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 53pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=70>£ 78,000 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl87 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>2.3</TD><TD class=xl75 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>Liverpool</TD><TD class=xl74 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white">500</TD><TD class=xl74 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white">600</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 53pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=70>£ 28,000 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl87 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>2.4</TD><TD class=xl75 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>Bristol</TD><TD class=xl74 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white">400</TD><TD class=xl74 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white">300</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 53pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=70>£ 17,000 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl85 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>3</TD><TD class=xl72 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>India</TD><TD class=xl74 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white"></TD><TD class=xl74 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white"></TD><TD class=xl86 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d9d9d9 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: white; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 53pt; COLOR: white; BORDER-BOTTOM: #d9d9d9 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" width=70>£ - </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl88 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" width=64 height=21>3.1</TD><TD class=xl89 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=64>Mumbai</TD><TD class=xl91 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white">200</TD><TD class=xl91 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white">300</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 53pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=70>£ 13,000 </TD></TR></TBODY></TABLE>

Desired Summary

<TABLE style="WIDTH: 245pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=326 border=0><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><TBODY><TR style="HEIGHT: 30pt" height=40><TD class=xl65 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9 0.5pt solid; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 158.25pt; BACKGROUND-COLOR: #0065a4" width=64 height=211 rowSpan=5>Seq. No</TD><TD class=xl65 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9 0.5pt solid; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: #0065a4" width=64 rowSpan=5>Location</TD><TD class=xl67 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9 0.5pt solid; BORDER-LEFT: #d9d9d9; WIDTH: 96pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: #538dd5" width=128 colSpan=2>Item Details</TD><TD class=xl68 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9 0.5pt solid; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: #0065a4" width=70 rowSpan=5>Total Cost </TD></TR><TR style="HEIGHT: 68.25pt; mso-height-source: userset" height=91><TD class=xl69 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 68.25pt; BACKGROUND-COLOR: #538dd5" width=64 height=91>Apple</TD><TD class=xl69 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: #538dd5" width=64>Banana</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #538dd5" width=64 height=20></TD><TD class=xl70 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: #538dd5" width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #538dd5" width=64 height=20>20</TD><TD class=xl70 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: #538dd5" width=64>30</TD></TR><TR style="HEIGHT: 30pt" height=40><TD class=xl72 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9 0.5pt solid; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: #0065a4" colSpan=2 height=40>No. of items sold</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl74 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>1</TD><TD class=xl75 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>Australia</TD><TD class=xl77 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white"></TD><TD class=xl77 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white"></TD><TD class=xl78 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: white; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 53pt; COLOR: white; BORDER-BOTTOM: #d9d9d9 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" width=70>£ - </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl79 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>1.1</TD><TD class=xl80 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>Brisbane</TD><TD class=xl76 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>3000</TD><TD class=xl76 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>7000</TD><TD class=xl78 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 53pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=70>£ 270,000 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl79 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>1.2</TD><TD class=xl80 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>Perth</TD><TD class=xl77 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white">2020</TD><TD class=xl77 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white">8000</TD><TD class=xl78 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 53pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=70>£ 280,400 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl81 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #dce6f1" width=64 height=20>Sub-total</TD><TD class=xl82 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #d9d9d9; BORDER-LEFT: #ffffff; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: #dce6f1" width=64></TD><TD class=xl84 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #d9d9d9; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: #dce6f1"></TD><TD class=xl84 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #d9d9d9; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: #dce6f1"></TD><TD class=xl85 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #ffffff; WIDTH: 53pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: #dce6f1" width=70>£ 550,400 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl79 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20></TD><TD class=xl80 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64></TD><TD class=xl77 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white"></TD><TD class=xl77 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white"></TD><TD class=xl78 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: white; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 53pt; COLOR: white; BORDER-BOTTOM: #d9d9d9 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" width=70></TD></TR><TR style="HEIGHT: 24pt" height=32><TD class=xl74 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 24pt; BACKGROUND-COLOR: white" width=64 height=32>2</TD><TD class=xl75 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>United Kingdom</TD><TD class=xl77 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white"></TD><TD class=xl77 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white"></TD><TD class=xl78 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: white; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 53pt; COLOR: white; BORDER-BOTTOM: #d9d9d9 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" width=70>£ - </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl79 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>2.1</TD><TD class=xl80 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>London</TD><TD class=xl77 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white">2500</TD><TD class=xl77 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white">2600</TD><TD class=xl78 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 53pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=70>£ 128,000 </TD></TR><TR style="HEIGHT: 24pt" height=32><TD class=xl79 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 24pt; BACKGROUND-COLOR: white" width=64 height=32>2.2</TD><TD class=xl80 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>Manchester</TD><TD class=xl77 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white">1500</TD><TD class=xl77 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white">1600</TD><TD class=xl78 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 53pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=70>£ 78,000 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl79 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>2.3</TD><TD class=xl80 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>Liverpool</TD><TD class=xl77 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white">500</TD><TD class=xl77 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white">600</TD><TD class=xl78 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 53pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=70>£ 28,000 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl79 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>2.4</TD><TD class=xl80 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>Bristol</TD><TD class=xl77 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white">400</TD><TD class=xl77 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white">300</TD><TD class=xl78 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 53pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=70>£ 17,000 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl81 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #dce6f1" width=64 height=20>Sub-total</TD><TD class=xl82 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #d9d9d9; BORDER-LEFT: #ffffff; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: #dce6f1" width=64></TD><TD class=xl84 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #d9d9d9; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: #dce6f1"></TD><TD class=xl84 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #d9d9d9; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: #dce6f1"></TD><TD class=xl85 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #ffffff; WIDTH: 53pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: #dce6f1" width=70>£ 251,000 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl79 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20></TD><TD class=xl80 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64></TD><TD class=xl77 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white"></TD><TD class=xl77 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white"></TD><TD class=xl78 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: white; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 53pt; COLOR: white; BORDER-BOTTOM: #d9d9d9 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" width=70></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl74 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>3</TD><TD class=xl75 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>India</TD><TD class=xl77 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white"></TD><TD class=xl77 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white"></TD><TD class=xl78 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: white; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 53pt; COLOR: white; BORDER-BOTTOM: #d9d9d9 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" width=70>£ - </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl79 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>3.1</TD><TD class=xl80 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=64>Mumbai</TD><TD class=xl77 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white">200</TD><TD class=xl77 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white">300</TD><TD class=xl78 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; WIDTH: 53pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: white" width=70>£ 13,000 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl81 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #dce6f1" width=64 height=20>Sub-total</TD><TD class=xl82 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #d9d9d9; BORDER-LEFT: #ffffff; WIDTH: 48pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: #dce6f1" width=64></TD><TD class=xl84 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #d9d9d9; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: #dce6f1"></TD><TD class=xl84 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #d9d9d9; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: #dce6f1"></TD><TD class=xl85 style="BORDER-RIGHT: #d9d9d9 0.5pt solid; BORDER-TOP: #d9d9d9; BORDER-LEFT: #ffffff; WIDTH: 53pt; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: #dce6f1" width=70>£ 13,000 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl86 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #d9d9d9 0.5pt solid; BORDER-LEFT: #d9d9d9 0.5pt solid; BORDER-BOTTOM: #d9d9d9 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #0065a4" colSpan=4 height=20>Grand-total</TD><TD class=xl89 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #d9d9d9; BORDER-LEFT: #d9d9d9; BORDER-BOTTOM: #d9d9d9 0.5pt solid; BACKGROUND-COLOR: #0065a4">£ 814,400 </TD></TR></TBODY></TABLE>


Please Help
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
1.preferably do cosmetic changes like merging etc only at the last instance before presenting
2. enter numbers only as number without any commas pound signals etc you can format as you like.

now add one more column F as given here the data will be like this

<table border="0" cellpadding="0" cellspacing="0" width="384"><col style="width:48pt" span="6" width="64"> <tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:48pt" height="17" width="64">Seq. No</td> <td style="width:48pt" width="64">Location</td> <td style="width:48pt" width="64">Item Details</td> <td style="width:48pt" width="64">Total Cost</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Apple</td> <td>Banana</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">20</td> <td align="right">30</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td colspan="2" style="height:12.75pt;mso-ignore:colspan" height="17">No. of items sold</td> <td>
</td> <td>
</td> <td>
</td> <td>class</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">1</td> <td>Australia</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">1.1</td> <td>Brisbane</td> <td align="right">3000</td> <td align="right">7000</td> <td align="right">270000</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">1.2</td> <td>Perth</td> <td align="right">2020</td> <td align="right">8000</td> <td align="right">280400</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">2</td> <td colspan="2" style="mso-ignore:colspan">United Kingdom</td> <td>
</td> <td>
</td> <td align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">2.1</td> <td>London</td> <td align="right">2500</td> <td align="right">2600</td> <td align="right">128000</td> <td align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">2.2</td> <td>Manchester</td> <td align="right">1500</td> <td align="right">1600</td> <td align="right">78000</td> <td align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">2.3</td> <td>Liverpool</td> <td align="right">500</td> <td align="right">600</td> <td align="right">28000</td> <td align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">2.4</td> <td>Bristol</td> <td align="right">400</td> <td align="right">300</td> <td align="right">17000</td> <td align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">3</td> <td>India</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">3</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">3.1</td> <td>Mumbai</td> <td align="right">200</td> <td align="right">300</td> <td align="right">13000</td> <td align="right">3</td> </tr> </tbody></table>

now highlight data from the row "no of items sole" till colulmn F (that is row no. 5) and down the data and click Data-subtotals-(if dialog box comes whether the first row is header row-click yes) and in the top window choose (at each cahnge in" choose "class".
"use function" choose sum
"add subtotal to" choose column E

check summary below data

click ok

you will get

<table border="0" cellpadding="0" cellspacing="0" width="448"><col style="width:48pt" span="7" width="64"> <tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:48pt" height="17" width="64">Seq. No</td> <td style="width:48pt" width="64">Location</td> <td style="width:48pt" width="64">Item Details</td> <td style="width:48pt" width="64">Total Cost</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Apple</td> <td>Banana</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">20</td> <td align="right">30</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td colspan="2" style="height:12.75pt;mso-ignore:colspan" height="17">No. of items sold</td> <td>
</td> <td>
</td> <td>
</td> <td>class</td> <td>
</td> </tr> <tr style="mso-outline-level:2;height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">1</td> <td>Australia</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">1</td> <td>
</td> </tr> <tr style="mso-outline-level:2;height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">1.1</td> <td>Brisbane</td> <td align="right">3000</td> <td align="right">7000</td> <td align="right">270000</td> <td align="right">1</td> <td>
</td> </tr> <tr style="mso-outline-level:2;height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">1.2</td> <td>Perth</td> <td align="right">2020</td> <td align="right">8000</td> <td align="right">280400</td> <td align="right">1</td> <td>
</td> </tr> <tr style="mso-outline-level:1;height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">550400</td> <td class="xl24">1 Total</td> <td>
</td> </tr> <tr style="mso-outline-level:2;height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">2</td> <td colspan="2" style="mso-ignore:colspan">United Kingdom</td> <td>
</td> <td>
</td> <td align="right">2</td> <td>
</td> </tr> <tr style="mso-outline-level:2;height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">2.1</td> <td>London</td> <td align="right">2500</td> <td align="right">2600</td> <td align="right">128000</td> <td align="right">2</td> <td>
</td> </tr> <tr style="mso-outline-level:2;height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">2.2</td> <td>Manchester</td> <td align="right">1500</td> <td align="right">1600</td> <td align="right">78000</td> <td align="right">2</td> <td>
</td> </tr> <tr style="mso-outline-level:2;height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">2.3</td> <td>Liverpool</td> <td align="right">500</td> <td align="right">600</td> <td align="right">28000</td> <td align="right">2</td> <td>
</td> </tr> <tr style="mso-outline-level:2;height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">2.4</td> <td>Bristol</td> <td align="right">400</td> <td align="right">300</td> <td align="right">17000</td> <td align="right">2</td> <td>
</td> </tr> <tr style="mso-outline-level:1;height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">251000</td> <td class="xl24">2 Total</td> <td>
</td> </tr> <tr style="mso-outline-level:2;height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">3</td> <td>India</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">3</td> <td>
</td> </tr> <tr style="mso-outline-level:2;height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">3.1</td> <td>Mumbai</td> <td align="right">200</td> <td align="right">300</td> <td align="right">13000</td> <td align="right">3</td> <td>
</td> </tr> <tr style="mso-outline-level:1;height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">13000</td> <td class="xl24">3 Total</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">814400</td> <td class="xl24" colspan="2" style="mso-ignore:colspan">Grand Total</td> </tr> </tbody></table>
 
Upvote 0
in future data bases in excel do cosmetic changes only as the last job
always enter numbers in excel as simple numbers for e.g. 200000 only as this not with commas or currency etc. You can format as you like

however as your database is already having these problems I am giving comprehensive macro, the first part of which remodels the data as it should be in excel (I have not done anything on the merging etc at the top)

FIRST COPY THE DATA AS IT IS SHEET1 TO SHEET2 AS A PRECAUTION AND ALSO FOR UNDOING.


Code: 
Sub test()
Dim r As Range, c As Range
Application.DisplayAlerts = False
Worksheets("sheet1").Activate
Columns("E:E").Replace Chr(163), "", lookat:=xlPart
Columns("E:E").Replace ",", "", lookat:=xlPart
Columns("E:E").Replace "-", "", lookat:=xlPart
Set r = Range(Range("E6"), Cells(Rows.Count, "E").End(xlUp))
For Each c In r
c.Offset(0, 1) = Left(Cells(c.Row, "A").Value, 1) + 0
Next c
Set r = Range(Range("A5"), Range("A5").End(xlDown).End(xlToRight))

  r.Subtotal GroupBy:=6, Function:=xlSum, TotalList:=Array(5), _
        Replace:=False, PageBreaks:=False, SummaryBelowData:=True

Application.DisplayAlerts = True
End Sub

Code: 
Sub undo()
Worksheets("sheet1").Cells.Clear
Worksheets("sheet2").Cells.Copy Worksheets("sheet1").Range("A1")

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
570
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
536
mumps
mumps
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,223,905
Messages
6,175,297
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top