G
Guest
I am trying to plot start and end dates. I am able to do this although I am
using a table that can have multiple entries to be plotted.
I am using this formula:
=IF(B$1=INDEX($B$13:$B$20,MATCH($A2,$A$13:$A$20,0)),"S",IF(B$1=INDEX($C$13:$C$20,MATCH($A2,$A$13:$A$20,1)),"E",IF(AND(B$1>INDEX($B$13:$B$20,MATCH($A2,$A$13:$A$20,0)),B$1<INDEX($C$13:$C$20,MATCH($A2,$A$13:$A$20,1))),"M","-")))
This is a nested IF that will search the List of names for a match, it will
then try and match the start date, then end date and finally if the date is
in between the two.
The problem I have is AA for example will have 2 entries, I want to plot
both Entries on a particular row although after the matches finds the first
Match, it stops looking.
Is there anyway to have a formula where is will look for a match, and
continue looking through out the array.
In a programming sense I would want an IF Loop Until it reaches end of array
or meets critera.
Below is an illustration of the table.
1 2 3 4 5 6
AA S M E - - -
BB - S M M E -
CC - - - - S M
DD - - S M M E
EE - S M M M E
FF - E S - - -
CC - - - - S M
Name Sdate Edate
AA 1 3
BB 2 5
CC 5 8
DD 3 6
EE 2 6
FF 3 2
AA 1 6
CC 1 5
using a table that can have multiple entries to be plotted.
I am using this formula:
=IF(B$1=INDEX($B$13:$B$20,MATCH($A2,$A$13:$A$20,0)),"S",IF(B$1=INDEX($C$13:$C$20,MATCH($A2,$A$13:$A$20,1)),"E",IF(AND(B$1>INDEX($B$13:$B$20,MATCH($A2,$A$13:$A$20,0)),B$1<INDEX($C$13:$C$20,MATCH($A2,$A$13:$A$20,1))),"M","-")))
This is a nested IF that will search the List of names for a match, it will
then try and match the start date, then end date and finally if the date is
in between the two.
The problem I have is AA for example will have 2 entries, I want to plot
both Entries on a particular row although after the matches finds the first
Match, it stops looking.
Is there anyway to have a formula where is will look for a match, and
continue looking through out the array.
In a programming sense I would want an IF Loop Until it reaches end of array
or meets critera.
Below is an illustration of the table.
1 2 3 4 5 6
AA S M E - - -
BB - S M M E -
CC - - - - S M
DD - - S M M E
EE - S M M M E
FF - E S - - -
CC - - - - S M
Name Sdate Edate
AA 1 3
BB 2 5
CC 5 8
DD 3 6
EE 2 6
FF 3 2
AA 1 6
CC 1 5