I have a spreadsheet with more than 45000 rows with all the projects LTD, and
only few open projects. Currently I am copying the data in different tabs,
and sorting, by project, delete the others, then copy to other tab and sort
by phase€¦and so on.
I need to find all names of people that worked in certain projects and
satisfied the phase condition {Design, Test, Implementation) and list the
hours spent in these projects by phase.
Is there a way to simplify this process?
NAME PROJECT PHASE Hours
AA P600 Design 5
AA P600 Analysis 5
AA P650 Test 5
BB P650 Implementation 5
BB P750 Scoping 5
BB P650 Test Case 5
BB P600 Analysis 5
CC P750 Scoping 5
CC P800 Scoping 5
DD P650 Post-Implemetation 5
VV P750 Analysis 5

The Desired output would be

NAME PROJECT PHASE Hours
AA P600 Design 5
BB P650 Implementation 5
AA P650 Test 5

Thank you in advance,

Hi Lisa

One way
Add an extra column to your table of main data. Assuming your data has
Phase in column C enter
=(C2="Design)+(C2="Test")+(C2="Implementation")
Copy down the length of your table.
This will place a 1 in all rows where any one of those conditions is
met.
DataFilterAutofilterdropdown on new column Select 1

That will show all rows that match, then use other filters to select
just one Project etc.
--
Regards

Roger Govier


"Lisa" wrote in message
...
I have a spreadsheet with more than 45000 rows with all the projects
LTD, and
only few open projects. Currently I am copying the data in different
tabs,
and sorting, by project, delete the others, then copy to other tab and
sort
by phase.and so on.
I need to find all names of people that worked in certain projects and
satisfied the phase condition {Design, Test, Implementation) and list
the
hours spent in these projects by phase.
Is there a way to simplify this process?
NAME PROJECT PHASE Hours
AA P600 Design 5
AA P600 Analysis 5
AA P650 Test 5
BB P650 Implementation 5
BB P750 Scoping 5
BB P650 Test Case 5
BB P600 Analysis 5
CC P750 Scoping 5
CC P800 Scoping 5
DD P650 Post-Implemetation 5
VV P750 Analysis 5

The Desired output would be

NAME PROJECT PHASE Hours
AA P600 Design 5
BB P650 Implementation 5
AA P650 Test 5

Thank you in advance,

Put this code in a standard code module and run it


Sub Test()
Dim iLastRow As Long
Dim i As Long, j As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "C").Value = "Design" Or _
Cells(i, "C").Value = "Test" Or _
Cells(i, "C").Value = "Implementation" Then
j = j + 1
Rows(i).Copy Worksheets("Sheet3").Cells(j, "A")
End If
Next i

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Lisa" wrote in message
...
I have a spreadsheet with more than 45000 rows with all the projects LTD,
and
only few open projects. Currently I am copying the data in different tabs,
and sorting, by project, delete the others, then copy to other tab and
sort
by phase.and so on.
I need to find all names of people that worked in certain projects and
satisfied the phase condition {Design, Test, Implementation) and list the
hours spent in these projects by phase.
Is there a way to simplify this process?
NAME PROJECT PHASE Hours
AA P600 Design 5
AA P600 Analysis 5
AA P650 Test 5
BB P650 Implementation 5
BB P750 Scoping 5
BB P650 Test Case 5
BB P600 Analysis 5
CC P750 Scoping 5
CC P800 Scoping 5
DD P650 Post-Implemetation 5
VV P750 Analysis 5

The Desired output would be

NAME PROJECT PHASE Hours
AA P600 Design 5
BB P650 Implementation 5
AA P650 Test 5

Thank you in advance,

Bob U Rock! Thanks a bunch; you saved me a lot of trouble.

"Bob Phillips" wrote:

Put this code in a standard code module and run it


Sub Test()
Dim iLastRow As Long
Dim i As Long, j As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "C").Value = "Design" Or _
Cells(i, "C").Value = "Test" Or _
Cells(i, "C").Value = "Implementation" Then
j = j + 1
Rows(i).Copy Worksheets("Sheet3").Cells(j, "A")
End If
Next i

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Lisa" wrote in message
...
I have a spreadsheet with more than 45000 rows with all the projects LTD,
and
only few open projects. Currently I am copying the data in different tabs,
and sorting, by project, delete the others, then copy to other tab and
sort
by phase.and so on.
I need to find all names of people that worked in certain projects and
satisfied the phase condition {Design, Test, Implementation) and list the
hours spent in these projects by phase.
Is there a way to simplify this process?
NAME PROJECT PHASE Hours
AA P600 Design 5
AA P600 Analysis 5
AA P650 Test 5
BB P650 Implementation 5
BB P750 Scoping 5
BB P650 Test Case 5
BB P600 Analysis 5
CC P750 Scoping 5
CC P800 Scoping 5
DD P650 Post-Implemetation 5
VV P750 Analysis 5

The Desired output would be

NAME PROJECT PHASE Hours
AA P600 Design 5
BB P650 Implementation 5
AA P650 Test 5

Thank you in advance,