VBA to Create Grouping (Difficult)

Can anyone pls help?

Biz

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

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

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

Sektor said:

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

Click to expand...

"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

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

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