Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default 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.
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 28
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Under data/Group&outline/group Bernard Excel Discussion (Misc queries) 1 July 2nd 09 01:02 AM
Group under Data Group and Outline Data not working. Help. Michele Excel Worksheet Functions 2 May 18th 09 07:45 PM
Copy Data from One Group of Cells to Another Group Alan Auerbach Charts and Charting in Excel 2 May 27th 07 04:12 PM
"Data Group and Outline Group" feature Limited to 8 levels MarekMG Excel Discussion (Misc queries) 3 May 25th 07 06:12 PM
Sort by Group Header or by Group SubHeader Aria[_2_] Excel Programming 4 February 28th 07 02:07 AM


All times are GMT +1. The time now is 11:12 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"