Groupin multiple rows into multiple groups

[Coin]

Hello,

I struggle setting up groups for my spreadsheet.
I have a list of customer locations in column A
Few Londons, few Berlins, few Tokyo's, few New York's etc.
next columns are details of their orders.

Now I want to group them so that first grouping is "ALL", next level grouping is "European cities" under one group, Asian cities under second group etc. and then 3rd level is individual customer locations. So at any time I can only display London, or at any time I can only display Tokyo.
Unfortunately when I am trying to get to level 2 continents or 3 locations then first grouping is good, but then I select rows for next city for example and the frist group is being extended so that I end up with one group. How can I have multiple groups ?

I attach a spreadsheet with the problem.
I am messing something up, can you help me please ? Maybe I am clicking in the wrong order or something...

[ByronVW]

hmmm i would suggest you use a more elegant method in displaying what you are looking for. If you want a user to be able to see either a group of cities by classification or a particular city use dynamic named ranges with a validation drop down box. I will build one for you quickly and then reply.

if you want to use grouping its a little tricky as you have to use "empty rows" to almost trick excel into not grouping groups so you are able to keep the level of granularity you want. Also you have to work back to front. So smallest level of detail grouped first and then backwards.

[ByronVW]

hmmm i would suggest you use a more elegant method in displaying what you are looking for. If you want a user to be able to see either a group of cities by classification or a particular city use dynamic named ranges with a validation drop down box. I will build one for you quickly and then reply.

if you want to use grouping its a little tricky as you have to use "empty rows" to almost trick excel into not grouping groups so you are able to keep the level of granularity you want. Also you have to work back to front. So smallest level of detail grouped first and then backwards.

[Coin]

I'd appreciate some example as I do not know what dynamic named ranges would mean, thank you !

[ByronVW]

Hi Coin,

Please see attached a working example i created for you.

There is some pretty cool code in there and I wanted to show you how you can use Dynamic validation lists and a bit of manipulation in the formulas to get a nice clean looking report. There is obviously some stuff there which is a little difficult for most, if you have any issues understanding what I've done, just let me know what you would like me to explain and I will.

BTW, the report I have given you can be improved a lot as my output calculations depend on YOU sorting the 'Data Full tab' in order of <Continent > Location>. This can be avoided by using array functions, however, this is something I'm not very strong at


Cheers,
Byron