Need help for VBA coding to do grouping

pprahul2003

New Member
Joined
Sep 21, 2014
Messages
7
Hi,


I am looking to use grouping function multiple times. Can you please help me in this regard?
I have a chart of accounts with Hierarchy relationship as parent and child. each child may act as parent.
Child1 is acting as parent contains 1x1 and 1x2 as children. while chid 1x2 also acting as parent with 1x21 and 1x22 as children. I have to do manual multiple grouping to ensure every children comes under parent's group.
in below case one group for 1x21 & 1x22 with 1x2 as parent.
then 1x1 & 1x2 are grouped under child1
similarly child 1 and child 2 groped under parent1.


I am looking for a macro for the same. Please help.




Relation ship as shown below.
Parent1
Child1
Child1x1

<tbody>
</tbody>
Child1x2

<tbody>
</tbody>
Child1x21

<tbody>
</tbody>
Child1x22

<tbody>
</tbody>
Child2

<tbody>
</tbody>
Child2x1

<tbody>
</tbody>
Child2x2

<tbody>
</tbody>

<tbody>
</tbody>



















Parent1
Child1
Child1x1
Child1x2
Child1x21
Child1x22
Child2
Child2x1
Child2x2

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 
Last edited:

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
pprahul2003,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs (both will display what cells, rows, columns, your data is in):
1. MrExcel HTMLMaker20101230
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Installation instructions here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

2. Excel Jeanie
Download


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 

pprahul2003

New Member
Joined
Sep 21, 2014
Messages
7
pprahul2003,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs (both will display what cells, rows, columns, your data is in):
1. MrExcel HTMLMaker20101230
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Installation instructions here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

2. Excel Jeanie
Download


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.



Hi Thanks for your response. Due to data integrity issue I cannot share you actual data. However How my data look like and actual results are on two different tabs. You may see the rough data and actual working. What I did is sorted the whole data from down to up and did the grouping. This is the manual work. In actual data columns may increase as there may be many parent child relation ship. Below is the link to understand what am I trying to say. I hope you can help me out. I use excel 2010, PC.

https://www.dropbox.com/s/yxsat343g5g3pvq/Rough Data.xls?dl=0
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
pprahul2003,

Thanks for the workbook.

You have only displayed one group on worksheet Rough Data?????

And, on worksheet Results, you have displayed the above data in reverse order?????


Here are the screenshots of your worksheets, just in case someone else will be able to assist you:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Parent1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style=";">Child1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Child1x1A</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Child1x2B</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Child1x21C</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Child1x22D</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style=";">Child2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Child2x1E</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Child2x2F</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Rough Data</p><br /><br />

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Child2x2F</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Child2x1E</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style=";">Child2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Child1x22D</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Child1x21C</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Child1x2B</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Child1x1A</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style=";">Child1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Parent1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Results</p><br /><br />


Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 

pprahul2003

New Member
Joined
Sep 21, 2014
Messages
7

ADVERTISEMENT

Hi, yes the data in results tab is in reverse order. however, you can see that i have used group data function to group. you can see 12345 buttons on extreme left top outside work sheet. this is what i want to get coded. i want to group every child with its immediate parent and then those parent to group with there immediate parent.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
pprahul2003,

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,984
Messages
5,526,062
Members
409,684
Latest member
Nazmul00

This Week's Hot Topics

Top