combine/compare two reports with different formating

Hello,

I'm currently working on a report that seems to be very difficult. There are 2 reports I have from two separate systems that SHOULD have the same data, however one of our systems is clearly not up to date.

The BP report is the main report that has the correct information. The SA report is from one of our systems that needs to be updated.
I have pulled the information from both systems, however the reports are formatted differently and it's very hard to even look at the reports manually to tell where the errors are let alone creating a macro to point them out.

I've taken a small section of the data and created an example workbook. The BP report needs to be modified, then the data from the SA report needs to be pulled in.
I've tried to create Pivot table for some of this but could not figure it out. The pivot table could work with one policy, but since we have over 50 Payee's/Prod's the Pivot table seems to be more work than its worth.

One issue is that the BP report places the first Payee/PROD on the second line of the policy and the second Payee/PROD on the first line. Also there isn't a clear way to determine the New or Renew percentage except the high percentage is always the New and the lower Percentage is always the Renew(Or at least that's how it should be). Generally the New % will have the older Split Eff Date and the Renew will have a later date.
It's possible the BP report will only have one % that is the renewed percentage. This can be placed in the New % for simplicity, it will be easy enough to spot these manually.

Not every Policy in BP will be in SA and visa versa, not everyone policy in SA will be in BP
There are 35282 Rows on the Real SA Report and 3458 rows on the Real BP Report.

Since there isn't a true key to tie everything together I concatenated the Plan name/Description and the Policy Number to create a Key.

Once I get the Data formatted like the Combined--GOAL Sheet I will be able to create rules for highlighting everything that we need, Just not sure how to get there.


Hopefully someone will have some better insight. After trying Pivot tables i have no idea how to manipulate the data accordingly.
If you have any questions or require more information feel free to request.