+ Reply to Thread
Results 1 to 4 of 4

MATCH previous occurrence of text in column, use OFFSET to get input variable

  1. #1
    Registered User
    Join Date
    03-29-2009
    Location
    Oregon, USA
    MS-Off Ver
    Excel 2016
    Posts
    48

    Arrow MATCH previous occurrence of text in column, use OFFSET to get input variable

    I was trying to find first previous entry of text (“Mits”) in column B and return a cell address that I could use w/ OFFSET to grab the date from one cell up and to right (column C) of text then use DAY function to compute elapsed number of days between occassional reoccurances of "Mits" in the B column – but maybe I’m totally barking up the wrong tree so I will explain what I’m trying to accomplish.

    My spreadsheet tracks a recurring process. User inputs dates, weights & time for each batch in color-coded cells - spreadsheet tracks days between batches, output weights (pounds, gallons, %, time & totals) in non-colored cells. To keep information compact (& allow occasional double batch inputs) I use 2 rows for each batch with some upper/lower cells merged (mostly user input cells), then display output data in upper and lower cells.

    Main area of question involves:

    Column B is unmerged – upper cell automatically tracks days between batches, lower cell allows input for occasional text indicating non-normal batch source OR automatically tracks days between 1st & 2nd drum used in occassional double batch.

    Column C is unmerged – top cell is source date of drum for single batch, bottom cell is only used occasionally for 2nd drum source date if a double batch is run.

    As a beginner my formula is probably messy, but using DAY function I managed to take date from previous batch source “C9”and subtract from next batch “C11” to track elapsed days between source batches. Then if previous batch was from source “Mits” it skips up to previous batch before “Mits” batch “C7” for date, then subtracts. If no date is entered in column C then cell in column B is left blank. The formula used:
    =IF(C11>0,IF(EXACT(B10,"Mits"),DAY(C11-C7),DAY(C11-C9)),"")

    The following will likely make sense only if looking at attached spreadsheet:
    I haven’t updated formula to accomplish this yet, but eventually, if I run a double batch from standard recurring source (it wouldn’t need source ID like “Mits” entered in lower “B” cell) the 2 cells in “C” column would track both source dates – upper one (row 21) the older source and lower cell (row 22) the newest source, then the upper cell in B column (B21) displays elapsed time in days between previous batch (C19) & 1st drum (C21) while lower cell (B22) would track elapsed time between 1st & 2nd source drums (C21 & C22) for double batch.

    Don't know if this affects anything:
    I use conditional formatting to color cells of “Mits” batch orange, and may possibly use 2nd or 3rd non-normal sources in future that would be another color and need to search for and subtract those dates separately.
    Attached Files Attached Files
    Last edited by r.coon; 03-24-2011 at 02:06 AM. Reason: add green messege icon

  2. #2
    Registered User
    Join Date
    03-17-2011
    Location
    GuangDong, China
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: MATCH previous occurrence of text in column, use OFFSET to get input variable

    i am just trying to catch your meaning, but it's too hard.
    This is the formula i write to set in cell "B11", is it right? ^_^

    =IF(C11,C11-OFFSET(C11,-2-(B10="Mits")*2,),"")

  3. #3
    Registered User
    Join Date
    03-29-2009
    Location
    Oregon, USA
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: MATCH previous occurrence of text in column, use OFFSET to get input variable

    Thanks zzmxy! my ignorance is embarrassing, I'm not familiar with the syntax of the formula you offered so can't understand what it does.

    I've attached an updated sample spreadsheet with example of how data validation list might be used for batch source ID in B16 with conditional formatting in B15:G16.

    I will try to simplify a description of my goal:

    I'm using column B (usually odd numbered cell) to track elapsed time (in days) between entries made in column C. The difficulty is the entries are not consecutive - meaning dates are usually entered on every other row (in my spreadsheet example rows 19 & 21 for single batches). I would like to track elapsed time in days between rows for double batches also as shown in rows 21 & 22 (would like to show elapsed days in B22 where "?" is shown).

    Sometimes I need to track elapsed time between special batches that will be labeled in column B (even numbered cell). In this case I want to track number of days between special batches from same source ID only (Mits, M-600, etc).


    This requires one formula in column B cells that would determine:

    1) if NO text is entered in even row of column B and...

    2) if NO date is entered in even row of column C and...

    3) if date is entered in odd row of column C, show elapsed days between that date and previous date entered (unless previous date entered has associated text in column B)
    Examples: B19=C19-C17 or B17=C17-C13 (because C15 has associated text in B16 - see criteria #5 below)

    4) if date is entered in even row of column C then show elapsed days in adjacent cell in column B between even cell and uneven cell directly above it. Example B22=C22-C21

    5) if text is entered in even row of column B then find previous entry using that text and show elapsed days between previous (text associated) date and currant (text associated) date. Example: B16=C15-C9

    One of the difficulties I ran into - I used the word "Mits" in the cell formula so when I try to write a formula to search for the next previous occurrence of "Mits" it finds it in any cell using "Mits" in the formula, not just the cell where it was manually entered. If formula were written to look for re-occurrence of any identifying text or number I might enter in that cell (e.g. Mits, M-600, 1041, 844, etc.) that would be ideal. Or possibly use data validation to provide drop-down list of choices (Example B16) - but not sure how it would work when that cell needs to show elapsed days if double batch were run.
    Attached Files Attached Files
    Last edited by r.coon; 03-22-2011 at 03:20 AM. Reason: add updated attachment

  4. #4
    Registered User
    Join Date
    03-29-2009
    Location
    Oregon, USA
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: MATCH previous occurrence of text in column, use OFFSET to get input variable

    I tried experimenting w/ the formula you offered zzmxy and I can't get it to do anything but return #VALUE! in B11 if I type anything into C11, B10 doesn't appear to do anything. What am I missing?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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