Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
collapsing columns base on criteria
Hi,
and thanks in advance even if you are just reading this post. :) I have few columns that I would like to collapse and Uncollapse base on criteria. kind like a filter but horizontally. this is how the data is set up NYC UNITS NC UNITS FLA UNITS CA UNITS NYC SALES NC SALES FLA SALES CA SALES NYC MARGIN NC MARGIN FLA MARGIN CA MARGIN 215 11395 603935 32 1696 89912 55 5 1321 7 3 1 3321 176013 9328689 494 26204 1388827 66 161 11 1 5 3 23 1219 64607 3 181 9618 56 1 1 7 2 131 6943 367979 19 1033 54783 11 11 4 2 1 38516 2041348 108191444 5734 303909 16107217 651 1 2 1 6 3 6546 346938 18387714 974 51651 2737507 1 2 1 6 I would like to have a button or outliner so the user can choose the state and all the columns starting with that state will group. the other can hide. so if I choose NYC then I'll get NYC units--- NYC Sales ----- NYC margin. is there any ideas how can be acomplish? Thanks, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
collapsing columns base on criteria
Try selecting your columns you want to hide and then play around with Data|Group
And Outline. Then you can click on the giant -/+ symbols to hide/show detail. Cesar Zapata wrote: Hi, and thanks in advance even if you are just reading this post. :) I have few columns that I would like to collapse and Uncollapse base on criteria. kind like a filter but horizontally. this is how the data is set up NYC UNITS NC UNITS FLA UNITS CA UNITS NYC SALES NC SALES FLA SALES CA SALES NYC MARGIN NC MARGIN FLA MARGIN CA MARGIN 215 11395 603935 32 1696 89912 55 5 1321 7 3 1 3321 176013 9328689 494 26204 1388827 66 161 11 1 5 3 23 1219 64607 3 181 9618 56 1 1 7 2 131 6943 367979 19 1033 54783 11 11 4 2 1 38516 2041348 108191444 5734 303909 16107217 651 1 2 1 6 3 6546 346938 18387714 974 51651 2737507 1 2 1 6 I would like to have a button or outliner so the user can choose the state and all the columns starting with that state will group. the other can hide. so if I choose NYC then I'll get NYC units--- NYC Sales ----- NYC margin. is there any ideas how can be acomplish? Thanks, -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
collapsing columns base on criteria
Cesar,
Create a special sheet and list your states in column A (CA, FLA, NC, NYC) Insert the name "Sts" refers to: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$N),1) [this is a self-expanding name and allows adding other states, cities) Create a userform with a combobox and a command button. Set the RowSource of the combobox to Sts Use this code for the button: Private Sub CommandButton1_Click() UserForm1.Hide End Sub Than add this sub in a standard module Sub MyState() Dim lcol As Long, x As Long, st As String Application.ScreenUpdating = False ' prevents screen flicker UserForm1.Show st = UserForm1.ComboBox1.Value lcol = ActiveSheet.Range("IV1").End(xlToLeft).Column ' finds last column on sheet Range(Columns(1), Columns(lcol)).Hidden = True ' hides all columns For x = 1 To lcol ' unhides specific columns If InStr(1, Cells(1, x), st, 1) 0 Then Columns(x).EntireColumn.AutoFit End If Next Application.Goto Reference:=Range("A1"), Scroll:=True ' scrolls to left side of worksheet Application.ScreenUpdating = True ' resets screen updating End Sub Now add a button on your sheet or toolbar and assign the macro MyState to this button. hope this is what you are looking for... steve "Cesar Zapata" wrote in message ... Hi, and thanks in advance even if you are just reading this post. :) I have few columns that I would like to collapse and Uncollapse base on criteria. kind like a filter but horizontally. this is how the data is set up NYC UNITS NC UNITS FLA UNITS CA UNITS NYC SALES NC SALES FLA SALES CA SALES NYC MARGIN NC MARGIN FLA MARGIN CA MARGIN 215 11395 603935 32 1696 89912 55 5 1321 7 3 1 3321 176013 9328689 494 26204 1388827 66 161 11 1 5 3 23 1219 64607 3 181 9618 56 1 1 7 2 131 6943 367979 19 1033 54783 11 11 4 2 1 38516 2041348 108191444 5734 303909 16107217 651 1 2 1 6 3 6546 346938 18387714 974 51651 2737507 1 2 1 6 I would like to have a button or outliner so the user can choose the state and all the columns starting with that state will group. the other can hide. so if I choose NYC then I'll get NYC units--- NYC Sales ----- NYC margin. is there any ideas how can be acomplish? Thanks, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
collapsing columns base on criteria
After looking at Steve's macro solution, another option is to set up custom
views with columns hiddent the way you want. You can define/show these custom views by using: View|custom view. Cesar Zapata wrote: Hi, and thanks in advance even if you are just reading this post. :) I have few columns that I would like to collapse and Uncollapse base on criteria. kind like a filter but horizontally. this is how the data is set up NYC UNITS NC UNITS FLA UNITS CA UNITS NYC SALES NC SALES FLA SALES CA SALES NYC MARGIN NC MARGIN FLA MARGIN CA MARGIN 215 11395 603935 32 1696 89912 55 5 1321 7 3 1 3321 176013 9328689 494 26204 1388827 66 161 11 1 5 3 23 1219 64607 3 181 9618 56 1 1 7 2 131 6943 367979 19 1033 54783 11 11 4 2 1 38516 2041348 108191444 5734 303909 16107217 651 1 2 1 6 3 6546 346938 18387714 974 51651 2737507 1 2 1 6 I would like to have a button or outliner so the user can choose the state and all the columns starting with that state will group. the other can hide. so if I choose NYC then I'll get NYC units--- NYC Sales ----- NYC margin. is there any ideas how can be acomplish? Thanks, -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro - Copy row base on criteria | Excel Discussion (Misc queries) | |||
collapsing rows together | Excel Worksheet Functions | |||
Creating graph (floating columns with top and base depth values) | Excel Discussion (Misc queries) | |||
Collapsing columns in Excel? | Excel Discussion (Misc queries) | |||
Collapsing Rows | Excel Discussion (Misc queries) |