Filter Table With Macro

[4rcher91]

Need help with macro..


A J C S V M

- - - - - - -

A 2 0 0 0 0

A 0 0 7 0 0

B 0 3 0 0 0

B 0 0 0 5 0

C 0 0 0 0 7


Above is a simple table in Sheet 1. The first column 'A' shows activity A, B, and C. The other columns show names of people involve in one of the activities. For instance, person J has completed 2% of activity A. Person S has completed 7% also of activity A.


What is the macro to create a new filtered table in a new sheet (i.e. Sheet 2)? For example, I want to filter only activity A. I run the macro, it displays the following in Sheet 2:


A J

A S


The same goes to if I want to filter only B. The macro displays the following:


B C

B V


The new filtered table only contains two columns as shown. One for activity column. One for people's column.


Any response and help is greatly appreciated. Many thanks

 

[Faseeh]

Hi 4rcher91,


Can't we use pivot table for that if we restructure that data ?? Will it be OK if you select from a drop down list for Person & Activity and shows the status?


Faseeh

 

[kchiba]

Hi,


try the following macro. It assumes that there is only one entry for a project per row, that the data is on Sheet1 and that the Data starts in A1. The Criteria you wantto filter on is entered in Cell K1


Sub Copy2Sheet2()


Sheets("Sheet1").Activate 'Sheet with Data

Crit = Range("K1").Value 'The Project to Filter is in K1


Range("A1").Select 'First Cell of Data Table

Selection.End(xlDown).Select

LastRow = Selection.Row

Columns("B:B").Select


Selection.Insert Shift:=xlToRight

Range("B1").FormulaR1C1 = "Per"

Range("B2").Select

ActiveCell.FormulaR1C1 = "=INDEX(R1C3:R1C7,1,MATCH(0,RC[1]:RC[5],-1))"

Selection.AutoFill Destination:=Range("B2:B" & LastRow), Type:=xlFillDefault

Range("A1").Select

Selection.AutoFilter

ActiveSheet.Range("$A$1:$G$" & LastRow).AutoFilter Field:=1, Criteria1:=Crit

Selection.CurrentRegion.Select

Selection.Offset(1).Resize(Selection.Rows.Count - 1, 2).Select

Selection.Copy

Sheets("Sheet2").Select

Range("A1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False


Sheets("Sheet1").Select

Application.CutCopyMode = False

ActiveSheet.ShowAllData

Columns("B:B").Delete Shift:=xlToLeft

End Sub

 

[4rcher91]

@Faseeh


Sorry, in other words?


@kchiba


I tested the given macro, it doesn't exactly give me the desired outcome as in the case. It shows me this:


A C

A J

B J

B J

C J


I need an outcome with only one type of activity (eg. activity A) and filter out others, like the following :


A J

A S


I thank for the replies so far. Do keep them coming

 

[SirJB7]

Hi, 4rcher91!

Give a look at this file: http://dl.dropbox.com/u/60558749/Filter%20Table%20With%20Macro%20%28for%204rcher91%20at%20chandoo.org%29.xlsm

Regards!

 

[kchiba]

HI have updated the Macro,


Sub Copy2Sheet2()


Sheets("Sheet1").Activate 'Sheet with Data

Crit = Range("J1").Value 'The Project to Filter is in J1


Range("A1").Select 'First Cell of Data Table

Selection.End(xlDown).Select

LastRow = Selection.Row

Columns("B:B").Select


Selection.Insert Shift:=xlToRight

Range("B1").FormulaR1C1 = "Per"

Range("B2").Select

ActiveCell.FormulaR1C1 = "=INDEX(R1C3:R1C7,1,MATCH(0,RC[1]:RC[5],-1))"

Selection.AutoFill Destination:=Range("B2:B" & LastRow), Type:=xlFillDefault

Range("A1").Select

Selection.AutoFilter

ActiveSheet.Range("$A$1:$G$" & LastRow).AutoFilter Field:=1, Criteria1:=Crit

Selection.CurrentRegion.Select

Selection.Offset(1).Resize(Selection.Rows.Count - 1, 2).Select

Selection.Copy

Sheets("Sheet2").Select

Range("A1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False


Sheets("Sheet1").Select

Application.CutCopyMode = False

ActiveSheet.ShowAllData

Columns("B:B").Delete Shift:=xlToLeft

End Sub

 

[4rcher91]

@SirJB7


Is the file a sort of template? Sorry for asking, but how do you utilize that file?


@kchiba


Sorry, the updated macro still doesn't return the outcome the example is trying to get. It doesn't filter the table in "Sheet 1" into a 2-columned outcome in "Sheet 2"

 

[SirJB7]

Hi, 4cher91!


I wouldn't call it a template, it only tried to be a sample file. As a sample file you have two options:

a) copy/implement the formulas, named ranges and all other stuff in the workbook with your real data

b) copy/move your real data to this sample file


Each method has its pro & cons, it just depends on data complexity, workbooks structure and personal skills which one of the two is preferable.


The uploade file has this characteristics:


a) sheet Hoja1 has your posted data, with the addition of the following named ranges:

- ActivityList: A2:A7 (note the yellow highlighted cell to mark end of list, used later)

- PeopleList: B1:F1

- ActivityPeopleTable: B2:F6


b) sheet Hoja2 has:

- a dropdown box (B1 -named range TargeValue- validated against named range UniqueActivityValidationList) for entering the desired activity

- a target area (column D -named range PeopleSelectedActivityList-) with the desired output

- an auxiliary named range UniqueActivityList (A2:A12)


How to use it?


1) in the example

- select and activity value in Hoja2 cell B1 from the dropdown box and get the people involved in column D


2) with real data

- assume that your data is just and only the table activity vs. people as you posted

- in sheet Hoja1 expand rows 2:6 (copying and inserting) for allocating all activities (be careful to not insert before row 2 or after row 6; if so, adjust ranges properly)

- in sheet Hoja1 expand columns B:F for allocating all people (same care)

- in sheet Hoja2 expand rows 2:12 for allocating unique activities, insert & copy as necessary until a #¡NUM! value appears (same care)

- proceed as stated in 1)


If you need further help, just upload the sample file (see the second green sticky post at this forums main page for guidelines), post your e-mail, or just ask as needed.


Regards!