More conditions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lisa - 20 Oct 2006 10:01 GMT

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,

Reply to this Message

Roger Govier - 20 Oct 2006 10:41 GMT

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.

Signature

Regards

Roger Govier

>I have a spreadsheet with more than 45000 rows with all the projects
>LTD, and
[quoted text clipped - 29 lines]
>
> Thank you in advance,

Reply to this Message

Bob Phillips - 20 Oct 2006 10:46 GMT

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

Signature

HTH

Bob Phillips

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

> 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,
[quoted text clipped - 25 lines]
>
> Thank you in advance,

Reply to this Message

Lisa - 20 Oct 2006 15:42 GMT

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

> Put this code in a standard code module and run it
>
[quoted text clipped - 45 lines]
> >
> > Thank you in advance,

Reply to this Message