Unique Key find MAX Date w/out Going Over Another Date

[mphillips]

I work at a school. We have leads that we try to convert to a student, the funnel is lead, application, and student.

I'm trying to find the campaign where the student transferred from application to student, which is called the primary campaign. The systems is technically set up to do this automatically based on the below; however, I'm finding that a sales member is going in and adjusting that primary campaign.

I have attached an example and in column AA shows that I want the final answer to be. This is a massive spreadsheet of multiple leads and dates they were leads and dates they were applications.

Essentially these are the parameters: The application date must be less than the student start date and the closest application date to the start date. Column X "Was Application" needs to be a 1. Because there are times where there is a start date associated to it, but they didn't finish.

You can see that in column I, there is a 1 on the top row right next to the primary campaign source of Sales Generated. Based on the dates for this specific lead, it should be a "Webinar" not "Sales Generated".

[Lemice]

Hello mphillips,

If you have a huge amount of data, I really recommend finding a VBA solution since I believe VBA will be a much better approach.

Other than that, here is an attempt to purely using formula, with the help of 5 extra columns:

1. Column AB returns the Max Date that matches your criteria for that unique key (helper for AA, the result column)
2. Column AC tells if a row qualified for the criteria or not (helper for AB)
3. Column AD returns the number of unique keys found (helper for all column)
4. Column AE tells the first row containing the unique key in that row (helper for all)
5. Column AF tells the last row containing the unique key in that row (helper for all)

And finally, Column AA just simply compare the Application Date to the date in AB.

I did it like this since I believe you will have many unique keys, not just one.
The only downside of this is that:

1. Formula in AB is an Array Formula - it is slow when there are huge amount of data, and you have to Ctrl-Shift-Enter to enter it (you will see it wrapped inside a { } ) and
2. You will have to sort by unique keys - order won't matter

Here is your sample file with the said formula. Is this what you are looking for?

[mphillips]

That worked perfectly. Thank you very much!

[Lemice]

Thank you for the feed back. Glad that I could help.

Have a great day!