VBA to Create Grouping (Difficult)

[Biz]

Dear All,

I have attached a link. Before Worksheet tab refers to Group Accounts before Grouping. After Worksheet tab refers to Group Accounts after Groupings that must be created by VBA.

http://www.megaupload.com/?d=09E125MR

Your help would be greatly appreciated.

Biz

 

[Biz]

Can anyone pls help?

Biz

 

[GlennUK]

That link is blocked here where I work, due to corporate security policies ... I'm sure many others here are the same. Describe what your requirements are. Maybe post some worksheet ranges using Excel Jeanie:

http://www.excel-jeanie-html.de/index.php?f=1

 

[Sektor]

I downloaded your file, but I don't understand what do you want.

 

[Biz]

Hi GlennUK,

Is there any other way of posting a link to where file can be stored for download?

Using Excel Jeanie won't help because need to view whole data structure is important.

Biz

 

[Biz]

I downloaded your file, but I don't understand what do you want.

"Before" worksheet tab needs to be converted like "After" worksheet tab using Excel VBA to do groupings.

Look at After Worksheet tab it groups accounts together.

Hope this brief explaination helps.

Biz

 

[Sektor]

Rows 806 and 463 are orphans. Is it this done intentionally?

 

[Biz]

Yes, it is. If you click on 1 on "After" Worksheet tab. It gives summarised view of as below

<TABLE style="WIDTH: 207pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=274 x:str><COLGROUP><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" span=3 width=21><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1060" width=29><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" width=40><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; WIDTH: 100pt; HEIGHT: 12.75pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed; mso-ignore: colspan" class=xl63 height=17 width=132 colSpan=5>Group Accounts</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl63 width=56></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl63 width=86></TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64 height=16 align=right x:num>41</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed; mso-ignore: colspan" class=xl64 colSpan=2>Profit and Loss</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64 height=16></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64 align=right x:num>39</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed; mso-ignore: colspan" class=xl64 colSpan=2>Net Surplus / (Deficit) After Tax</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64 height=16></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64 align=right x:num>42</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64>Net Operating Surplus / (Deficit)</TD></TR><TR style="HEIGHT: 12pt; mso-outline-parent: collapsed" height=16><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64 height=16></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64 align=right x:num>43</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64>Net Non-Operating Surplus / (Deficit)</TD></TR><TR style="HEIGHT: 12pt; mso-outline-parent: collapsed" height=16><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64 height=16 align=right x:num>6</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed; mso-ignore: colspan" class=xl64 colSpan=3>Liabilities & Equity</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64 height=16></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64 align=right x:num>28</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed; mso-ignore: colspan" class=xl64 colSpan=2>Liabilities</TD></TR><TR style="HEIGHT: 12pt; mso-outline-parent: collapsed" height=16><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64 height=16></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64 align=right x:num>29</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed; mso-ignore: colspan" class=xl64 colSpan=2>Ratepayers equity</TD></TR><TR style="HEIGHT: 12pt; mso-outline-parent: collapsed" height=16><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64 height=16 align=right x:num>7</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64>Asset</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64 height=16></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64 align=right x:num>8</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64>Assets</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64 height=16></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64 align=right x:num>9</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed; mso-ignore: colspan" class=xl64 colSpan=2>Non-Current Assets</TD></TR><TR style="HEIGHT: 12pt; mso-outline-parent: collapsed" height=16><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64 height=16></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64 align=right x:num>10</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl64></TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed; mso-ignore: colspan" class=xl64 colSpan=2>Current Assets</TD></TR></TBODY></TABLE>

Biz