Excel Auto Group

[wrightlefty]

I would like to know if there is a way to have Excel automatically group rows based on a Level Number. For instance, in the example below Excel would group everything from Rows 2-7 under Row 1, Rows 5-6 would be grouped under Row 4. You can manually do this using:

Data -> Group and Outline -> Group

However, I would like to have a macro or VB script that would do this automatically.


Row Level
1 1
2 2
3 2
4 2
5 3
6 3
7 2
8 2

 

[SkipVought]

Hi,

How about the AutoFilter?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wrightlefty]

Autofilter does not accomplish what I'm looking for. In the example above if I ran an autofilter for level 2's this would be the output:

Row Level
2 2
3 2
4 2
7 2
8 2

What I would like to have is a spreadsheet that allows me to explode and/or colapse a BOM based on the level with groupings. Autofilter would hide any level other than the filtered level. Grouping is the function I want, I just want it done automatically.

 

[SkipVought]

What LOGIC are you using that would group "Rows 2-7 under Row 1" and not rows 2-8 as per your example?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Your example is of an Indented BOM. The actual BOM relationship is a Parent-Child row in a table.

You would need to write a formula that groups the N+1 level with the most recent N level.

As you have defined the example...
[tt]
Row Level Item Grouped With
1 1 A
2 2 B A
3 2 C A
4 2 D A
5 3 E D
6 3 F D
7 2 G A
8 2 H A
[/tt]
You could write a simple formula to do that. Post back if you need help.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I think it will need to be coded in VBA, because you have multiple levels that you would need to store the latest item value.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

OK, here's an approch:

1. Table structure
[tt]
Level Item Grp 1 2 3 4
1 a
2 b
2 c
2 d
3 e
4 f
2 g
2 h
3 i
[/tt]
where ITEM could be your part number,Grp is the Item value for grouping, 1, 2, 3, 4 ... n are your levels in separate columns.

Here's the formula in D3...
[tt]
Level Item Grp 1 2 3 4
1 a
2 b =IF($A3=D$1,$B3,D2)
2 c
2 d
3 e
3 f
2 g
2 h
3 i
[/tt]
copy across all levels and dlwn thru all items.

Then the formula in Grp in C3...
[tt]
Level Item Grp 1 2 3 4
1 a
2 b =IF(A3=1,"",INDEX(D3:G3,1,A3-1))
2 c
2 d
3 e
3 f
2 g
2 h
3 i
[/tt]
and the result
[tt]
Level Item Grp 1 2 3
1 a a
2 b a a b 0
2 c a a c 0
2 d a a d 0
3 e d a d e
3 f d a d f
2 g a a g f
2 h a a h f
[/tt]
If you AutoFilter on level 2 column F, for Item d, you get your grouping as requested.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]