Retrieving another text string based on multiple criteria (formula requires update)

Hi all,

I was requested to post another issue I am having as a new thread. It is all explained in the sample excel document, which basically shows a flaw in the formula I am trying to apply to extract another text string. The attachment shows the desired result and currently the formula is returning a blank value (iferror return "", which I do not get why).

I understand your concept, it returns a blank due to the year changing. However, the date year from column E to column H is not the actual year that I use. I use column B to define a full year. But even so, it should return the next program returned into regardless of what year it is. Is there a formula that I can add to the end that would just take the next program (column P) at the next date regardless of the year? Perhaps to inside the "" of the result of IFERROR?

Hi, apologies for not getting back to you before.

I've no doubt, whatsoever, that you can do what you want to do, but I confess I'm struggling to follow the requirements.

My limited understanding at this point in time is that you will have the "old" formula in one column (notwithstanding fact you must change /100 to, say, /10000000, else it won't work), and you would like to have a separate calculation, in a separate column, for "Program Change" ... however, the narrative in your prior post (& file) implies the logic for this new calculation is somewhat different to that used for the "Completed" check -- the latter logic dictates that for a Program to be a (potentially) valid result the Program must not be "completed", or if it "completed" it must be associated with an end date in a year following the max date held in E:J

In simpleton / narrative terms, can you outline the logic for this new calculation - and what, if any, significance "completed" status has?

I appreciate the above is a bore, however folk here are going to oblivious to your model, and so what may seems obvious / intuitive to yourself may not be to others trying to help.

Hi,

No need to apologize. I should apologize for not making myself clear. I took your understanding and applied as a brand new thread, which probably confused many users.

Shall I post a new thread? I modified the approach to totally ignore the completed approach, as that is done successfully thanks to you.

Here is the attachment that has explained the example I included differently. Again, shall I post this as a new thread?

It's not immediately obvious to me, given sample data, as to what happens should Program Changes spill into subsequent years, however - first stab below:

Formula:

Please Login or Register  to view this content.

The above finds first non-Program Change entry that occurs after (or on same day) that the last Program Change record ends - where it (Program Change) occurs in year stipulated in B (or beyond), relative to ID#.

Note: in your latest file I believe A3 should read 23974 as opposed to 23794, latter will not return a valid result as no data.

Yes you are correct about A3. Should of read 23974.

I did a slight modification to the reference table in the excel document and added some criteria that I desire. The last stab you took worked flawlessly, however two issues:

First: The formula returns a blank if the program changed into also says program change. An ID can change program two or more times in a row.

Second: It is my fault that I did not fully explain what I actually desire, so I have attempted to clearly input my desired results in the attachment.

Sorry for the delay in responding.

Thanks as always!

Suspect I may have missed another nuance or two, but given expected results:

Formula:

Please Login or Register  to view this content.

the 2nd calculation uses the results from first matrix, where possible, and finds first entry that follows last program change entry identified in first table
{designed to cater for possibility that there could be > 4 program changes, i.e. last entry in Q should be the 5th program change}

HI there,

Perfect, works great. One last issue. Sometimes, the "program change" has the latest date. Right now the formula returns a #NUM! in these cases.

Is there a way to return nothing if this occurs?

And if possible I want to replace the (blank) with nothing "", as the (blank) notation I use is suppose to be "" (nothing in cell).

Thanks again.

See below

Formula:

Please Login or Register  to view this content.

If it's related, yes. Please be as clear as possible in terms of requisite logic.

Sorry i found an issue. I edited my last post. Please see if you do not mind.

Right... given the data is unsorted etc , and fact that I am now interpreting request as:

"list any / all 'FROM' Program Changes, in start-date order, where they belong to given ID and fall within stipulated period"
"list subsequent 'TO' entries, where final TO entry could fall into a subsequent period, and where Registration Status c=is irrelevant..."

Formula:

Please Login or Register  to view this content.

Hello,

Please see attached, which is hopefully clear and concise.

Perfecto! Thanks!

Originally Posted by McKneezy

I have one last issue. I do not know if you can help me solve it? May I request? It is similar to example just uses a different registration status....Please see attached, which is hopefully clear and concise.

I will do my best but this is getting a little convoluted... using your attachment

Q1:

Why is E3 populated?
Based on source data this value is outside of period of interest (2011-2); whilst last "TO" value may fall into subsequent period the "FROM" values do not, or at least didn't...

Q2:

Why is H2 "Enhanced" {starts 02/01/2018} rather than "Local" {starts 01/07/2016}
Both the above entries fall after the last valid / associated Withdraw date {30/06/2016}, so I would expect to use first dated entry (Local)

I've no doubt this is outlined in below comment, unfortunately this requires a deeper understanding than I can muster...

Originally Posted by McKneezy

It is possible that an ID may have returned in the next year and changed programs. This program should still be recorded in the year earlier and should be recorded as the last program. (this scenario occurs with ID # 18390).

Answer Q1: You are correct. Please see updated attachment. I added one more row to show that this same ID never returned after withdrawal.

Answer Q2: I have changed H2 to a blank cell.

Sorry for this. I hope now it is clear.

The first table is easy enough

Formula:

Please Login or Register  to view this content.

However, I'm still struggling with the logic of the "TO" table given latest example...

H2 is now blank but F6 is not... I'm afraid I can't discern the logic as to why in one case (H2) the subsequent year is excluded but not the other (F6).

Further to the above, F4 which was previously "(did not return)" is now blank, implying some sort of change in logic.

I sincerely apologize for the confusion.

Allow to me not only to explain the logic but recreate the concept all together, as I even confused myself with the last document. Please see attached.

The goal here is to extract ID's that either changed programs, remained in the same program, or never returned after any of the 4 withdrawals.

If the ID returns in a later year, that year should be in brackets next to "program name" or "remained" as shown in attachment.

IMPORTANT NOTE -- I changed the withdraw names, as the formula provided uses LEFT which will not work when I use the actual withdrawal names.

I have not heard back. Is it ok I post as a new thread?

Sorry, yes, that's fine -- I have not been able to spend much time on here recently. I looked at the revised file last week but was still struggling with some of the (apparent) inconsistencies so it might help to have fresh eyes, as I'm clearly not "getting it". That being said, I believe my previous post included the formula for the first / left table.