+ Reply to Thread
Results 1 to 13 of 13

Assign value in one row of cells based on next occurance of values in another row

  1. #1
    Registered User
    Join Date
    09-03-2013
    Location
    Elgin, IL
    MS-Off Ver
    Excel 2010
    Posts
    90

    Assign value in one row of cells based on next occurance of values in another row

    I am compiling a census and need a formula that will look up the data found in Column A and return/categorize the results into one of four values (either E, ES, EC or FF) in Column B.

    Column A either contains "E", "ES" or "C".

    I need the formula in Column B to return the following:

    If A16=blank, then B16=blank.

    If A16 is not blank but does not contain "E", then B16=blank.

    If A16=E, then look up in Column A beginning at A16 and see if "SP" occurs before the next occurrence of "E" in Column A, if yes, then keep looking down in Column A to see if "C" also occurs before the next "E", if yes, then B16 = FF.

    If A16=E, then look up in Column A beginning at A16 and see if "SP" occurs before the next occurrence of "E" in Column A, if yes, then keep looking down in Column A to see if "C" also occurs before the next "E", if no, then B16 = ES.

    If A16=E, then look up in Column A beginning at A16 and see if "SP" occurs before the next occurrence of "E" in Column A, if no, then keep looking down in Column A to see if "C" also occurs before the next "E", if yes, then B16 = EC.

    If A16=E, then look up in Column A beginning at A16 and see if "SP" occurs before the next occurrence of "E" in Column A, if no, then keep looking down in Column A to see if "C" also occurs before the next "E", if yes, then B16 = E.

    Attached is sample census file.

    My prayers to God in Jesus' name for the solution to this challenge of mine and a big THANK YOU in advance to anyone who has the wisdom they can impart to me here.
    Attached Files Attached Files

  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: Assign value in one row of cells based on next occurance of values in another row

    To keep this monster formula from being any bigger than necessary, we'll create a special "Named Formula" we can use as a shortcut all through the formula. This special named formula is designed to create a "range of cells" based two occurances of "E" in column A. You must do these steps carefully exactly...


    1) Click on B16 (this is important, the formula we are about to enter is a RELATIVE formula, it will change each time it is used based on the value in the cell to the left, so we have to anchor the original formula correctly if we want it to adjust itself correctly.)

    2) Press CTRL-F3 to open the Name Manager.

    3) Create a NAMED FORMULA like so:

    - NEW
    - Name: MyRange
    - RefersTo: =OFFSET('Census (2014)'!A16,,,ABS(ROW() - MATCH("E", 'Census (2014)'!$A17:$A$1048576, 0) - ROW()),)

    Copy that formula exactly as shown.

    - OK

    4) Now enter this starting formula in B16, then copy downward:

    =IF(A16<>"E", "", IF(ISNUMBER(MATCH("SP", MyRange, 0)), IF(ISNUMBER(MATCH("C", MyRange, 0)), "FF", "ES"), IF(ISNUMBER(MATCH("C", MyRange, 0)), "EC", "E")))
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    09-03-2013
    Location
    Elgin, IL
    MS-Off Ver
    Excel 2010
    Posts
    90

    Re: Assign value in one row of cells based on next occurance of values in another row

    Thank you, JBeaucaire; however, this does not return what I need. I followed your instructions and every result in Column B is either "E" or blank. But I need the results to either be E, ES, EC or FF, but none of the results are coming back ES, EC or FF - they are all coming back E.

    So when looking at the first 5 rows of data in Column A, we have E, then SP, then C, then C, then E again. The formula needs to be able to come back with a result in B16 as FF; however, the solution you provided results in E, which is not correct for that individual employee (E) who actually has coverage for themselves, their Spouse (SP) and two children (C).

    The result in Column B should only come back "E" if there are no SP or C occurrences after the E or before the next E.

    Note: the formula would also need to be able to calculate correctly for the very last E listed on the census which will have no E after it.

    Any other suggestions?

  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: Assign value in one row of cells based on next occurance of values in another row

    Did you look at the sample file above? It shows the solution working as requested.

    If your implementation is not working, post that attempt and let me check your work.

  5. #5
    Registered User
    Join Date
    09-03-2013
    Location
    Elgin, IL
    MS-Off Ver
    Excel 2010
    Posts
    90

    Re: Assign value in one row of cells based on next occurance of values in another row

    Almost perfect!! Thank you so much for your help. I had copied the formula in the "refers to" field incorrectly. Once I corrected it, I was able to receive correct results.

    ONE MORE THING NEEDED THOUGH. I went to the last employee who on the same census I initially posted should be E which comes back correct, but if I have a census that has the last employee who has SP or C after them, and they are the last employee on the census, there will not be another E occurrence, and the formula that I have right now does not provide the correct enrollment code in Column B for the last row in this scenario I'm describing.

    Is there some way to slightly modify the provided formula so the last member listed on the census will have the correct code result in Column B?

    So if you take the same census as before but add a SP in cell A101 and add a C in cell A102 after the last E, you can see what issue I'm running into. B100 value should be FF but it's returning E because there's no other E occurrence in Column A after the E in row 100, but there won't be another E instance for the very last employee. How can we have the formula account for this?

    Be Well -

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

    Re: Assign value in one row of cells based on next occurance of values in another row

    Update your sample workbook so I can see what you mean. I thought I covered all the variables presented.

  7. #7
    Registered User
    Join Date
    09-03-2013
    Location
    Elgin, IL
    MS-Off Ver
    Excel 2010
    Posts
    90

    Re: Assign value in one row of cells based on next occurance of values in another row

    See attached for updated sample census report. I've added SP and C after the last E and you'll see that "E" is the result coming back still in B100 but it should be FF.

    Be Well -

  8. #8
    Registered User
    Join Date
    09-03-2013
    Location
    Elgin, IL
    MS-Off Ver
    Excel 2010
    Posts
    90

    Re: Assign value in one row of cells based on next occurance of values in another row

    JBeaucaire - I just thought of a real easy solution to my last issue. I'll just type "E" in a cell below my normal search criteria range in that one column and that will resolve the last employee on the census issue.

    Thank you SO MUCH for sharing your wisdom with me. If you email me your mailing address, I'll put a $25 gift card in the mail to you...truly, your answer helps me IMMENSELY!.

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

    Re: Assign value in one row of cells based on next occurance of values in another row

    That's a great workaround. Here's a correction that will fix that error without you needing to use the workaround:

    1) Click on B16
    2) Update the MyRange formula to this:

    =OFFSET('Census (2014)'!A16,,,IFERROR(ABS(ROW() - MATCH("E", 'Census (2014)'!$A17:$A$1048576, 0) - ROW()), 10),)



    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  10. #10
    Registered User
    Join Date
    09-03-2013
    Location
    Elgin, IL
    MS-Off Ver
    Excel 2010
    Posts
    90

    Re: Assign value in one row of cells based on next occurance of values in another row

    Your last formula worked perfectly. Truly, I thank God for you!

    Be Well...

  11. #11
    Registered User
    Join Date
    09-03-2013
    Location
    Elgin, IL
    MS-Off Ver
    Excel 2010
    Posts
    90

    Re: Assign value in one row of cells based on next occurance of values in another row

    Hi Jerry,

    As it turns out, to complete the other formula I recently posted on this forum, I'm having to fill in all the blanks that are in one of my columns of information that I receive from the formula you provided to me. Instead of having your formula return a blank in Column B, are we able to modify the formula so the blank cells actually return the same value that is in Column B of the first row above it that contains an E in Column A? So for example, B68 and B69 would return FF since the value in A67 is E and the value in B67 is FF.

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

    Re: Assign value in one row of cells based on next occurance of values in another row

    In B16, then copied down:

    =IF(A16<>"E", B15, IF(ISNUMBER(MATCH("SP", MyRange, 0)), IF(ISNUMBER(MATCH("C", MyRange, 0)), "FF", "ES"), IF(ISNUMBER(MATCH("C", MyRange, 0)), "EC", "E")))

  13. #13
    Registered User
    Join Date
    09-03-2013
    Location
    Elgin, IL
    MS-Off Ver
    Excel 2010
    Posts
    90

    Re: Assign value in one row of cells based on next occurance of values in another row

    Wonderful - thank you Jerry. As it turns out, another excel forum member ended up providing a solution to work with the data I had without needing to fill in the blank cells by this formula you just developed, but I appreciate you sharing your wisdom with me again.

    Be Well,
    Brian
    Last edited by JBeaucaire; 10-11-2013 at 11:15 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Cell to assign values based upon goals
    By Granny in forum Excel General
    Replies: 12
    Last Post: 03-18-2013, 04:02 AM
  2. Assign values based on conditions in excel
    By Maniraj in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-16-2012, 12:31 AM
  3. Replies: 3
    Last Post: 03-28-2011, 10:53 AM
  4. Compare 2 values and then assign a 3rd based on comparison
    By Mordred in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-10-2010, 02:22 PM
  5. Assign range name based on cell values
    By dsrt16 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-25-2009, 03:07 PM

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