+ Reply to Thread
Results 1 to 7 of 7

recognizing data in adjacent columns

  1. #1
    Registered User
    Join Date
    04-03-2009
    Location
    yorkshire
    MS-Off Ver
    Office 365
    Posts
    87

    recognizing data in adjacent columns

    Hi

    I am trying to find a formula that calculates the longest period from data in an adjacent cell. The attached spreadsheet might better explain this
    Attached Files Attached Files
    Last edited by Pickygame; 04-08-2009 at 01:16 PM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: recognizing data in adjacent columns

    In cell E1 enter this array formula:

    =INDEX(B1:B100,MATCH(2,1/(B1:B100<>"")))

    ...and press CTRL-SHIFT-ENTER to confirm. The number 2 should appear. If you just press ENTER you will break the array and get a #DIV/0 error.

    In E2, put this array formula:

    =INDEX(A2:A100,MATCH(INDEX(C2:C100,MATCH(2,1/(C2:C100<>""))),C2:C100,0))

    ...also confirmed with CSE.

  3. #3
    Registered User
    Join Date
    04-03-2009
    Location
    yorkshire
    MS-Off Ver
    Office 365
    Posts
    87

    Re: recognizing data in adjacent columns

    The second array does the trick. I only put two columns in to assist because both questions should return the corresponding data in Column A. Thanks a lot

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: recognizing data in adjacent columns

    If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

  5. #5
    Registered User
    Join Date
    04-03-2009
    Location
    yorkshire
    MS-Off Ver
    Office 365
    Posts
    87

    Re: recognizing data in adjacent columns

    Thread marked as solved

  6. #6
    Registered User
    Join Date
    04-03-2009
    Location
    yorkshire
    MS-Off Ver
    Office 365
    Posts
    87

    Re: recognizing data in adjacent columns

    I thought i had this solved but if any of the data in the adjacent column is the same excel gives the first value
    Attached Files Attached Files
    Last edited by Pickygame; 05-01-2009 at 01:46 PM. Reason: not solved

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: recognizing data in adjacent columns

    Try this alternate array formula:

    =INDEX(A:A,SUMPRODUCT(MAX((B3:B12=INDEX(B3:B12,MATCH(2,1/(B3:B12<>""))))*(ROW(B3:B12)))))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1