+ Reply to Thread
Results 1 to 11 of 11

Looking back through cells in a column to find Specific Text(s)

  1. #1
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    380

    Smile Looking back through cells in a column to find Specific Text(s)

    Good morning,

    In the attached sheet I need to check on the Destination in column B, and if it matches set criteria, divide the figure in Column E by 2.

    eg If LAX is the destination, then for any rows directly under LAX until the next destination appears, column E needs to equal column D divided by 2.

    If the Destination is not LAX, then Column E equals Column D.

    the difficulty I am having is that there can be a variety of row numbers for each destination. I am having trouble creating a formula that can basically work backwards from Row-1 in column B until it gets to a text string, and check that text string against the criteria. so it may be row -1 up to about row -9 dependant on the number of flight times available to each destination throughout the week.

    Hope you can assist, and thanks in advance

    Darren
    Attached Files Attached Files
    Last edited by Grimace; 02-14-2010 at 10:24 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Looking back through cells in a column to find Specific Text(s)

    In E2 enter:

    =IF(TRIM(D2)="","",D2/IF(LOOKUP(REPT("z",255),$B$1:B2)="LAX",2,1))

    then copy it right down.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    380

    Re: Looking back through cells in a column to find Specific Text(s)

    Thank you very much NBVC, works a treat

    If I need to add to the list of Destinations (eg LAX or MEL) that have to be halved, should I add an "Or" statement, or would you recommend another way? I am not 100% on how it works, I just know it works

    I am just making sure I can adapt the formula if future changes occur.

    Thanks again for your help and quick response.

    Darren

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Looking back through cells in a column to find Specific Text(s)

    You would adapt it like this...

    =IF(TRIM(D2)="","",D2/IF(OR(LOOKUP(REPT("z",255),$B$1:B2)={"LAX","MEL"}),2,1))

    you can add more strings within the curly brackets if desired.

  5. #5
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    380

    Re: Looking back through cells in a column to find Specific Text(s)

    Awesome .. thanks again.

    I will go through it step by step so i understand how it works, and can use it again in the future.

    Darren

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Looking back through cells in a column to find Specific Text(s)

    Note that the LOOKUP(REPT("Z",255),B$1:B2) function finds the last text string in a range that is less than or equal to a string of "Z" repeated 255. Lookup() looks for the last value that is smaller than or equal to the lookup value... since it is unlikely you will have a bunch of "Z" 's in a cell, than it would make sense that the last text string it comes across in the range, is the string you want... the rest of the formula is I think logical...

  7. #7
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    380

    Re: Looking back through cells in a column to find Specific Text(s)

    Good evening all,

    I have tried to use the same process on a different part of my sheet to identify the originating port, and I must have missed something.

    Column F of the attached does change if the origin is in the same row, however I am trying to have it look up the last origin in column A and change all rows relating to that origin in column F, until a new origin is encountered.

    I am sure it is just a missing comma or something??

    Thanks
    Darren
    Attached Files Attached Files

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Looking back through cells in a column to find Specific Text(s)

    The formula doesn't work, because your blanks in column A are not true blanks, and the formula picks that up...

    So then try:

    =IF(LEFT(LOOKUP(2,1/($A$6:$A7<>""),$A$6:A7),4)={"PHUK","NADI"},0.5,1) in F7, copied down.

    Note: This is not an "array formula" and hence only need to confirm with ENTER.

  9. #9
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    380

    Re: Looking back through cells in a column to find Specific Text(s)

    Thanks NBVC

    I entered the formula you suggested, and it worked great for the PHUKET section of the test data. However when copied down it missed the NADI section and left the values at 1 instead of changing to 0.5?

    I cannot see any difference in the coding or cell formats. Any suggestions?

    Do the curly brackets around the origin names of PHUK and NADI make a difference considering it is not an array formula? My knowledge of this side of things is very minimal

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Looking back through cells in a column to find Specific Text(s)

    I apologize...

    Should have been:

    =IF(OR(LEFT(LOOKUP(2,1/($A$6:$A7<>""),$A$6:A7),4)={"PHUK","NADI"}),0.5,1)

    copied down.

  11. #11
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    380

    Re: Looking back through cells in a column to find Specific Text(s)

    Worked a treat ..... thanks very much again !

+ 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