Lookup Question

EdE

Board Regular
Joined
Apr 8, 2002
Messages
241
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!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Thanks!!

Does the search list need to be sorted in some way? If so, can this be done without sorting?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top