Extracting all programs from a table with multiple criteria

Hi,

I would like to accomplish two things in my sample workbook from a reference table (desired results are identified in workbook):

Firstly, I would like to take only the program name if the student has "completed" under his or her registration status in the given year. However, I want to make sure that it is taking the program name from the proper year and in proper order if the student completed more than one program by copying the formula over to the right or even a brand new formula can work.

Secondly, I want to show what program did the student return in if he did at all return after completing a program. If the student did not return in that year, I still want to be able to show which program the student returned after all annual completions no matter what year that student returned into the new program. Note that registration status can be anything for the program returned in.

Thank you for your help!

Please try
J2 to L2

=IFERROR(INDEX($Q:$Q,AGGREGATE(15,6,ROW($O$5:$O$13)/($O$5:$O$13=$A2)/($R$5:$R$13="Completed"),COLUMNS($J2:J2))),"")

M2
=IFERROR(INDEX($Q:$Q,AGGREGATE(15,6,ROW($O$5:$O$13)/($O$5:$O$13=$A2)/($R$5:$R$13="In Progress"),1)),"")

That is a pretty complex formula, thanks for the quick response .

However, it is not working with the data set I am using, as I think the AGGREGATE(15,6) part of the formula because 1) reference table is used in another sheet and 2) the reference table is over 5000 rows.

Note that I tried to copy the reference table to another sheet and adjusted the formula accordingly but I was given blanks only.

You need to reference right columns,

Please see attached,
I change reference to Sheet1! that cover 9999 rows


J2 to L2

Formula:

Please Login or Register  to view this content.

M2

Formula:

Please Login or Register  to view this content.

Hi there,

Great that worked. However, it is taking programs completed for any year, as there is no condition for Column B (Year). Is there way to add an additional condition?

For the program returned, the status can be any status, "In Progress", "Withdrawn by Self", etc...

J2 to L2 add year compare to B2

Formula:

Please Login or Register  to view this content.

M2 take any that not Complete

Formula:

Please Login or Register  to view this content.

Thank you! Worked like a charm.

Hi there,

I realize that applying the desired solution for M2 does not take the correct program in most cases. In a nutshell, it is the program that comes after the last program completed regardless of what year the program returned into is. I figure the program start date after the last program end date will have to be used in the formula. Please see attachment with desired results in M2 through M5, which is the programs returned into.

Hi there,

Thank you for the quick reply. I designate "Local" as the desired result because that is the program returned into after the last program completed in the year 2015-2016 (cell B2). It is basically the program they returned into right after they complete regardless of the year the next program is or in a lot of cases it is the same year. In this case of ID# 18390, the program after the last completed program in 2015-2016 happened to be "Local". In other words, ID's can complete a program and return in a program in the same year. As in the case of ID 18390, there were 3 programs completed in that year. If this ID only completed two programs and program "General" had a different status than "complete", then "General" would be the program returned into.

Does this answer your question ?

Shall I post a new thread you think?

Hi,

I just wanted to say the result you provided worked perfectly. Thank you once again!

If you have the time can you explain the AGGREGATE formula and how it applied in this scanerio?

No worries if you can't .