vba - pulling and comparing dates

[manwithaquestion]

Need some help with this logic..


For example:


On my first tab "Last visit", I have columns, "Customer Name", "Customer #" and "Scheduled date" On my second tab, "All visits" I have "Customer Name", "Customer #", and "Visit Dates". Now, this tab shows all of the visit dates (even the last one that is on "Last visit") while the first tab just shows their last visit date and their next scheduled date.
Goal: On my first tab, "Last Visit", I need some logic that compares the scheduled visit date with all of the visit dates.. for that specific customer ID. So on the next column after "Scheduled date" I want a new column "Last visit date" that compares the visit before the last visit date for that specific customer. So some customers have multiple visits, I need each cell for each customer (over 300 for this store) to read all of the times that they visited and only pull the max (most recent BUT NOT the one that is their last visit) date from the second tab "All visits"
The reason I want the second largest date of all their visits is because in the "all visits" tab, even their scheduled date is inputed in there.


Example: Tab 1:

<CODE>Billy Joe 12345 1/02/15 <-- Scheduled visit new column --> visit before last visit

Customer2 Id2 scheduled visit </CODE>

Tab 2:


<CODE>Billy Joe 12345 03/15/14

Billy Joe 12345 04/15/14

Billy Joe 12345 1/02/15

</CODE>So I would want the date "04/15/14" and not the other two..


EDIT: I have a partial equation that works for MOST customers... the problem is: if a customer visits more than once per month, it still pulls the 2nd highest date of all his/her visits.. how do I make it check to make sure that the visit before the last visit has to be less than their last visit (if that makes sense..)


Here's my equation:


{=LARGE(IF(('All visits'!B:B='Last visit'!B2),('All visits'!C:C)),2)}


B2 corresponds to the customer id (its matching their id on the other page, so when I drag it down it just continues to match the id's)...


An example of an issue:


<CODE>6/3/2015
6/18/2015
6/26/2015
6/9/2015

</CODE>Notice how there are multiple visits per month, so my equation will put 6/18/2015 in all of the spots even though their scheduled visits for that month would be "less" than their "next visit".


I need a check within my statement that makes sure that the scheduled date must be less than the value it pulls for that customer id in the other tab. I know this is a lot and maybe hard to understand but just ask some clarification questions and I can answer them. This is a recurring document to where when I click "refresh" it just pulls in all the data for today...

 

[manwithaquestion]

EDIT: the 2nd to last sentence should say "Notice how...their scheduled visits for that month would be less than their "last visit"