PDA

View Full Version : grouping tasks



ioncila
03-23-2009, 03:26 PM
Hi
Here's something i would like to know if it is possible to do in excel (via vba or not):

I have this data

THIS TO ENTER DATA (A, B, C, D) THIS TO CALCULATE (E, F)
A B C D F G
Tasks SDate EDate SDate EDate
01 Task01 1 20/03/09 20/04/09 20/03/09 20/04/09
02 Task02 2 20/03/09 14/04/09 20/03/09 14/04/09
03 Task03 2 15/04/09 20/04/09 15/04/09 20/04/09
04 Task04 1 31/03/09 20/05/09 31/03/09 20/05/09
05 Task05 2 31/03/09 20/04/09 31/03/09 20/04/09
06 Task06 2 21/04/09 20/05/09 21/04/09 20/05/09
07 Task07 3 21/04/09 31/04/09 21/04/09 31/04/09
08 Task08 3 01/05/09 15/05/09 01/05/09 15/05/09
09 Task09 4 01/05/09 10/05/09 01/05/09 10/05/09
10 Task10 4 11/05/09 15/05/09 11/05/09 15/05/09

What I wish to do

Values in F and G columns should be calculated by one of two conditions:
Condition 1 - If B1=B2 Then F1=C1 And G1=D1
Condition 2 - If B1<B2 Then F1=Min(all cells until next B.value=B1) And G1=Max(all cells until next B.value=B1) and so on (cells in red)

Is this clear? Is it possible?

I know Ms Project or similar do this quite good, but i need to do it in excel for specific and circunstancial porpose.

Thank You All

Bob Phillips
03-23-2009, 04:30 PM
I am not clear as to what the tests are, you just seem to have replicated the values in C & D to E & F, and coloured some red.

ioncila
03-24-2009, 09:00 AM
I am not clear as to what the tests are, you just seem to have replicated the values in C & D to E & F, and coloured some red.

Well, I'm afraid I was not clear enough. Sorry for that.

C and D columns are for data entering, F and G are to input automatically one of those conditions I want to implement. This is only a possible option because I dont know if it is possible to do it directly in C and D columns.

Actually, I input max and min formulas manually in cells which are "summary tasks".

Thats why I call for help.

Thanks

Bob Phillips
03-24-2009, 09:27 AM
Sorry but that has cleared up nothing for me. Looking at your data, row 2, B2 is not equal to B1, but F1 is equal to C1, so I don't get it.

ioncila
03-24-2009, 09:35 AM
I think I get the misunderstood. Please dont assume first column of data. I just put it there to refer line number:

Col A = Tasks
Col B = Level
Col C = Sdate
Col D = Edate

Bob Phillips
03-24-2009, 09:57 AM
I didn't, that is what I assumed.

ioncila
03-24-2009, 10:16 AM
I didn't, that is what I assumed.

Maybe if I put it like this

Condition 1 - If B1=B2 Then F1=C1 And G1=D1
Condition 2 - If B1<B2 Then F1=Min(F2:Fn) -Fn is last cell before B.value=B1 And G1=Max(G2:Gn;1) -Gn last cell before B.value=B1

Sagy
03-24-2009, 10:47 AM
Maybe if I put it like this

Condition 1 - If B1=B2 Then F1=C1 And G1=D1
Condition 2 - If B1<B2 Then F1=Min(F2:Fn) -Fn is last cell before B.value=B1 And G1=Max(G2:Gn;1) -Gn last cell before B.value=B1
Using formulas:
In column H calculate/put the row number for "last cell before B.value=B1" you can use Match to find this.
in F1
=If(B1=B2,C1,If(B1<B2,Min(Indirect("F2:F"&H1),""))
in G1
=If(B1=B2,D1,If(B1<B2,Max(Indirect("G2:G"&H1),""))

ioncila
03-25-2009, 10:30 AM
Using formulas:
In column H calculate/put the row number for "last cell before B.value=B1" you can use Match to find this.
in F1
=If(B1=B2,C1,If(B1<B2,Min(Indirect("F2:F"&H1),""))
in G1
=If(B1=B2,D1,If(B1<B2,Max(Indirect("G2:G"&H1),""))

I tried your tip but nothing happens.
Maybe I'm doing wrong reading of those formulas.
See attached file, please

Thank you

ioncila
03-26-2009, 04:40 AM
Any help?

ioncila
03-26-2009, 10:43 AM
Made some progress, but still dont know how to solve MIN and MAX formulas (Sagy's tip)

I think the issue is in cell reference, but my knowledge isnt enough.

See attached file

Thanks

ioncila
03-28-2009, 08:01 AM
I have made a bigger progress. Its almost solved.

However, there 2 issues that can work better, if i find the correct formula:

1. This formula in column G
=IF (B9>=B10;D9;IF(B9<B10;LARGE((INDIRECT("G10:G"&H9);1)))
when you paste it to cells below, "G10" must be G11, G12, ...
Now I have to do it manually. Is there a way to make it automatically?

2.This Formula in colums H
=IF(B10>0;Row(B8)+MATCH(B9;B10:$B$21;0);"")
This is to get real row number of the cell found below before next cell with same value of B9.
Is the a formula more simplified to get same result?

I hope this is clear enough.
See attached file please

Bob Phillips
03-28-2009, 09:48 AM
1. =IF(B8>=B9,D8,IF(B8<B9,LARGE(INDIRECT("G"&ROW(G9)&":G"&H8),1)))

2. =ROW()-1+MATCH(B8,B9:B$21,0)

ioncila
03-28-2009, 12:37 PM
Hi xld

Thank you very much for the tip.
It works perfectly just like I intend to.

Thank you again