We could either attempt to use Excel formulas to achieve your goal or write some VBA macro code or combine the 2 approaches.
When formulas become quite complicated they can be more difficult to understand than the corresponding VBA.
Some results are impossible to achieve using formulas alone.
Sometimes the way we first think of doing something makes it more complicated than it needs to be,
Let's have a go.
You say you want to end up with separate tables, one for each day (from 1 to 31) containing Pairing numbers which correspond to that day in P_DATA and the value from column AP of Add_Dates in the last row for that pairing.
Let's take a look at the last bit - getting the value from column AP from the last row for pairing PA. You could use a single-cell array formula like:
=INDEX(Add_Dates!$AP$1:$AP$1000,MAX(IF(Add_Dates!$AS$1:$AS$1000=PA,ROW(Add_Dates!$AS$1:$AS$1000),0)))
where I have assumed that the data fits within the first 1000 rows of Add_Dates. It would be better if the columns of data had range names.
It could be simpler than that though. If the last row of the data in Add_Dates for pairing PA always contains the maximum value for that pairing in column AP then you could simply get the maximum value, MV, using array formula
=MAX(IF(Add_Dates!$AS$1:$AS$1000=PA,Add_Dates!$AP$1:$AP$1000,0))
which can be shortened, if you like, to
=MAX((Add_Dates!$AS$1:$AS$1000=PA)*Add_Dates!$AP$1:$AP$1000)
Let's also consider the requirement for separate tables, one for each day.
If you had a single table containing PA, MV and DayNo you could sort that table on DayNo and then on MV and you could filter the table on DayNo to get just the table for that day. DayNo could be a hidden column if you prefer.
That could be simpler to construct than a number of separate tables where you don't know how big to make them or how many entries they will contain.
So, we can now get the MV value from Add_Dates to put alongside pairing PA in this composite day table.
We also need to know which day table(s) pairing PA should appear in, ie which DayNo to put alongside PA and MV.
Not clear from your example. Does pairing PA appear in only one day table (ie with only one DayNo) or can it appear in multiple tables (ie with different DayNo values)?
If it only appears in one day table then I would deduce that there is only one number in P_DATA for that pairing.
So we should be able to find it by a Lookup function using the pairing number PA.
=VLOOKUP(PA,P_Data!$A$1:$J$1000,10,FALSE)
Hope this gives some pointers... Bill Manville
Excel MVP/TA
Oxford, England | 
