More conditions

[Guest]

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,

 

[Roger Govier]

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.
Data>Filter>Autofilter>dropdown on new column >Select 1

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

 

[Bob Phillips]

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)

 

[Guest]

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