Lookup Question

[EdE]

I have two columns of data and need to find the beginning and end info for a criteria.
In column A are weekly dates going downward.
In column B rows 2 to 10 are Type A, rows 11 to 20 type B, rows 21 to 30 type C etc. The date of the types changes but they are always together. How do I find the starting and end dates?

Thanks!

[Iain Lewis]

Try this macro :

Sub aaa()
Range("b1").Select
typeastart = Cells.Find(What:="type a", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Range("f2").Value = ActiveCell.Offset(0, -1)
Range("f3").Value = ActiveCell.Offset(10, -1)
End Sub

this finds the first occurance of "Type A" and in F2 returns the value of the cell to the left of it. It then returns the value 10 rows down & 1 col to the left in F3.

Iain

[EdE]

Thanks it works. The problem I have is that sometimes Type A may have 10 rows and then tomorrow it may only have 3. I was hoping I could do a formula to help create a Ghannt(sp?) charge for my project management reviews. I was hoping to avoid having to do it manually.

Thanks

[Aladin Akyurek]

On 2002-04-23 08:30, EdE wrote:
I have two columns of data and need to find the beginning and end info for a criteria.
In column A are weekly dates going downward.
In column B rows 2 to 10 are Type A, rows 11 to 20 type B, rows 21 to 30 type C etc. The date of the types changes but they are always together. How do I find the starting and end dates?

Thanks!

I'll assume that your data start at row 2.

Make a list of types of interest in D from D2 on downwards.

In E2 enter:

=OFFSET($A$2,MATCH(D2,$B$2:$B$200,0)-1,0,1,1)

This gives you the start date for the type criterion in D2.

In F2 enter:

=OFFSET($A$2,MATCH(D2,$B$2:$B$200)-1,0,1,1)

This gives you the end date for the type criterion in D2.

Another set of formulas that you can use is:

=INDEX($A$2:$A$200,MATCH(D2,$B$2:$B$200,0)) [ to be entered in E2 ]

=INDEX($A$2:$A$200,MATCH(D2,$B$2:$B$200)) [ to be entered in F2 ]

Select E2:F2 and copy down as far as needed.

Aladin

[EdE]

Thanks!!

Does the search list need to be sorted in some way? If so, can this be done without sorting?

[Aladin Akyurek]

On 2002-04-23 09:32, EdE wrote:
Thanks!!

Does the search list need to be sorted in some way? If so, can this be done without sorting?

Yes, the data must be sorted on column B.

If you don't want to sort, you can use the following array formulas:

=MIN(IF($B$2:$B$200=D2,$A$2:$A$200))

for the start date &

=MAX(IF($B$2:$B$200=D2,$A$2:$A$200))

for the end date for the type in D2.

To array enter a formula, hit control+shift+enter at the same time, not just enter.

Aladin