Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Lookup Question

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    A Mile High!!
    Posts
    241
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Midlands, UK
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    A Mile High!!
    Posts
    241
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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


  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    A Mile High!!
    Posts
    241
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks!!

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

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •