Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba code to filter the data like a group with group header
Hi,
I need some help. I am trying to filter the data by unique value in the column and copy paste it to the different sheet like a groups. ex:- I have data like this Item1 Item2 Sales Group1 1 $10 Group1 2 $20 Group1 3 $20 Group2 1 $20 Group2 1 $30 Group3 1 $10 Group3 1 $15 I want the data should look like this Group 1 Item2 Sales 1 $10 2 $20 3 $20 Group 2 Item2 Sales 1 $20 1 $30 Group 3 Item2 Sales 1 $10 1 $15 Please help me how can we do that. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba code to filter the data like a group with group header
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba code to filter the data like a group with group header
Hi
A Pivot Table will do this better, faster, without code. Give it half an hour, you won't believe how anyone can work without pivot tables afterwards: http://chandoo.org/wp/2009/08/19/exc...bles-tutorial/ http://www.howtogeek.com/howto/13336...bles-in-excel/ HTH. Best wishes Harald skrev i melding ... Hi, I need some help. I am trying to filter the data by unique value in the column and copy paste it to the different sheet like a groups. ex:- I have data like this Item1 Item2 Sales Group1 1 $10 Group1 2 $20 Group1 3 $20 Group2 1 $20 Group2 1 $30 Group3 1 $10 Group3 1 $15 I want the data should look like this Group 1 Item2 Sales 1 $10 2 $20 3 $20 Group 2 Item2 Sales 1 $20 1 $30 Group 3 Item2 Sales 1 $10 1 $15 Please help me how can we do that. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba code to filter the data like a group with group header
Hi Archana,
Am Fri, 12 Apr 2013 07:43:10 -0700 (PDT) schrieb Archana: But I don't want to create the different sheets, I want to move the data to different sheet with all of the groups like the Group1 header and data below and Group2 header and data below.....and so on. And some times I have only 2 groups in the sheet and some times I have 10 groups. in that case I agree with Harald. To create a Pivot table is the easiest and quickest way. If you define a dynamic range name as source for the table, you only need to refresh if data has changed. But if you want your layout, then try: Sub Filter() Dim LRow As Long Dim i As Integer, j As Integer Dim myArr As Variant Dim myCount As Integer Application.ScreenUpdating = False j = 1 With Sheets("Sheet1") LRow = .Cells(.Rows.Count, 1).End(xlUp).Row .Range("A1:A8").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=.Range("K1"), Unique:=True myCount = .Cells(.Rows.Count, "K").End(xlUp).Row myArr = .Range("K2:K" & myCount) .Range("K1:K" & myCount).ClearContents For i = LBound(myArr) To UBound(myArr) .Range("A1:C" & LRow).AutoFilter _ field:=1, Criteria1:=myArr(i, 1) Sheets("Sheet2").Cells(j, 1) = myArr(i, 1) j = j + 1 .Range("B1:C" & LRow).Copy _ Sheets("Sheet2").Cells(j, 1) j = j + myCount + 1 Next ..AutoFilterMode = False End With Application.ScreenUpdating = True End Sub Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba code to filter the data like a group with group header
On Friday, April 12, 2013 10:56:29 AM UTC-5, Claus Busch wrote:
Hi Archana, Am Fri, 12 Apr 2013 07:43:10 -0700 (PDT) schrieb Archana: But I don't want to create the different sheets, I want to move the data to different sheet with all of the groups like the Group1 header and data below and Group2 header and data below.....and so on. And some times I have only 2 groups in the sheet and some times I have 10 groups. in that case I agree with Harald. To create a Pivot table is the easiest and quickest way. If you define a dynamic range name as source for the table, you only need to refresh if data has changed. But if you want your layout, then try: Sub Filter() Dim LRow As Long Dim i As Integer, j As Integer Dim myArr As Variant Dim myCount As Integer Application.ScreenUpdating = False j = 1 With Sheets("Sheet1") LRow = .Cells(.Rows.Count, 1).End(xlUp).Row .Range("A1:A8").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=.Range("K1"), Unique:=True myCount = .Cells(.Rows.Count, "K").End(xlUp).Row myArr = .Range("K2:K" & myCount) .Range("K1:K" & myCount).ClearContents For i = LBound(myArr) To UBound(myArr) .Range("A1:C" & LRow).AutoFilter _ field:=1, Criteria1:=myArr(i, 1) Sheets("Sheet2").Cells(j, 1) = myArr(i, 1) j = j + 1 .Range("B1:C" & LRow).Copy _ Sheets("Sheet2").Cells(j, 1) j = j + myCount + 1 Next .AutoFilterMode = False End With Application.ScreenUpdating = True End Sub Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 Hi Claus, Thank you very much! This code is working great after little bit tweeks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Under data/Group&outline/group | Excel Discussion (Misc queries) | |||
Group under Data Group and Outline Data not working. Help. | Excel Worksheet Functions | |||
Copy Data from One Group of Cells to Another Group | Charts and Charting in Excel | |||
"Data Group and Outline Group" feature Limited to 8 levels | Excel Discussion (Misc queries) | |||
Sort by Group Header or by Group SubHeader | Excel Programming |