combine/compare two reports with different formating

[kleptilian]

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.

[rcm]

try the macro

[kleptilian]

This is what I get when I run it. The first row starts out right, however the BP PRD 1 and BP PRD 2 should be reversed RAB should be number 2, most of the PRD's are off along with the %'s.
The policy numbers are correct but the Split Eff Date's are off.
This line gets added at the bottom Supplemental Medical Reimbursement. It's from the SA sheet however it should not be on the report because it is not in the BP report.


You have made so much more progress than i could have, I REALLY appreciate your help.
I hope you are willing to work with me to get this working, I feel you have the skills to accomplish this.
THANK YOU!

[rcm]

OK. Lets walked it item by item;

A. The first row starts out right, however the BP PRD 1 and BP PRD 2 should be reversed RAB should be number 2


RAB is in the 2 entry and therefore it goes in as the second product.

Question: do you need in the reverse order in which they were read?

B. most of the PRD's are off along with the %'s.

let me check on that


C. The policy numbers are correct but the Split Eff Date's are off.

let me check.

D. This line gets added at the bottom Supplemental Medical Reimbursement. It's from the SA sheet however it should not be on the report because it is not in the BP report.

I took care of that.




Note: when reading SA, the product try to side the SA products with the BP corresponding products (if there are any)

[kleptilian]

A. The first row starts out right, however the BP PRD 1 and BP PRD 2 should be reversed RAB should be number 2


RAB is in the 2 entry and therefore it goes in as the second product.

Question: do you need in the reverse order in which they were read?

Only the BP needs to be in Reverse order. The SA report is correct in regards to the information that it is pulling from the database, and since this is what we are trying to fix SA PRD 1 should stay as SA PRD 1.
The BP report for some reason shows the Primary PRD on the bottom. The primary/PRD1 on BP can be identified by the fact they receive a higher percentage than PRD 2.

B. most of the PRD's are off along with the %'s.

let me check on that
10-4


C. The policy numbers are correct but the Split Eff Date's are off.

let me check.
10-4

D. This line gets added at the bottom Supplemental Medical Reimbursement. It's from the SA sheet however it should not be on the report because it is not in the BP report.

I took care of that.


Note: when reading SA, the product try to side the SA products with the BP corresponding products (if there are any)
I'm not sure what you mean in the note.

[rcm]

Note: I tried to match SA products to BP products but it is clear now that SA product order should be as it comes.

[kleptilian]

Note: I tried to match SA products to BP products but it is clear now that SA product order should be as it comes.

You're a Genius!!!

Everything looks correct from my first look. Now I'll need to run this on my real reports. As I'm sure you know running it on the real report I might run into variables I didn't foresee, especially with 35,000 rows of data.

It will take me some time to go over it in detail. It won't be until tomorrow till I have the time to go over it.

Thanks again for all your help getting me this far!

On a side note what type of functions are you using in the VBA? I'd like to be able to learn how to write this myself, but I'm not sure where to start.

[rcm]

The main function is the "Range.find" which is a fast way to find a key in a row

Will check my email tomorrow...

[kleptilian]

It didn't end up working out. For some reason it stopped copying over the data from SA at "BlueCare 4214369".

All the data that does get copied over looks to be correct.

[rcm]

I found the glitch! it happens that column c in the line before the one you metioned is blank and that was how the loop stopped!. I fixed it testing the a column instead...

[kleptilian]

When I run it I get a

Run-time error '13':
Type mismatch

When I click debug it shows the line

While sh2.Cells(r, 1) <> ""

It fills out everything for the BP report quickly, then when it fills out the SA report it starts filling at "BlueOptions 546214369" runs for 1282 rows then that's when it throws the error.

[rcm]

I left some testing code on (I took it out)

I also changed the testing key to the cust number

[kleptilian]

We almost got it working. It still threw the runtime error at me. After running some testing I figured that sometimes the macro will run through completely and other times it doesn't. I tried sorting the SA and BP by policy number and it worked, then I re-ran it to further test and it threw the runtime again. I'm not sure what would be causing this. Would it be easier if the legend for BP and SA started with the policy number?


After more testing.
BP sorted by policy number, and SA sorted by Description... All BP report pulls in correctly. The SA report pulls in correctly by stops after row 1316 but does get row 1770 and I get a Runtime error.

BP and SA sorted by Policy number... All BP pulls in correctly. The SA report pulls in correctly by stops after row 1316 but does get row 1770 and I get a Runtime error.

BP sorted by Description, and SA sorted by Policy number. Appears to pull the same information as the above but its hard to determine where the SA reports stops from the Runtime as the sorting is different.

I did notice that the SA report has policy numbers that start with numbers and letters. The macro stops pulling information when the runtime occurs. The last item to be copied over is the last policy number that starts with a number.




I tried sorting BP by policy number and SA by description and it completed this time, however it doesn't get everything from SA pulled in. IE it doesn't not pull in the information for "Tallahassee EEs Only - Capital00054"

[rcm]

could you show me the complete "Tallahassee EEs Only - Capital00054" record? or is it the one in the sample...

[kleptilian]

It's the first on on the SA report in the example.

Its possible we may not be able to figure this out. Since the Runtime occurs with my full report (which isn't something I can send you) it may not be possible for you to fix the SA part.


Let me know if you have an idea of whats going on whether or not you think it's fixable from your end.
Your code is above my head so I'm not sure how far off we are.


If you think you can't finish it without the actual report let me know.

[rcm]

Please Login or Register  to view this content.

[kleptilian]

I added some rows that are not solely numbers for policy number and it threw the runtime. This will give you something to try and troubleshoot.

Hope this helps!

[rcm]

I change the policy number to aplhanumeric.

I compared the results with your 'Combined..... in the COMPARISON sheet

I ran smoothly.

I have a question. Would this run be frequent or is it one time?

[kleptilian]

I compared the results with your 'Combined..... in the COMPARISON sheet
What do you mean?

This will be ran at least on a monthly basis.

I forgot to add the new line into the combined

[rcm]

I forgot to include the revised version of the macro

COMPARISON I compared the try sheet and the Combined----Goal sheet

[kleptilian]

The new revision does work much better, but still throws a runtime. The debug takes me to ky = sh2.Cells(r, 3) & sh2.Cells(r, 2).

I can't determine where in the report that i throws the runtime.

[rcm]

do you know which line and could you send that line?

[kleptilian]

I couldn't find the line.


I did notice that on the bottom two lines that I added it is showing the Primary PRD as BP PRD 2. The PRD with a higher percentage should be placed first.

[rcm]

OK thatīs a new requirement. Placement would also be depending on the % not only on the reading order then

[rcm]

Furthermore, error 13 means type mismatch so what kind of data is in the key (policy number and policy name)?

[kleptilian]

OK thatīs a new requirement. Placement would also be depending on the % not only on the reading order then

Yes that's correct. I didn't foresee this variable but after looking through the report there are enough instances of this scenario to cause an issue when reviewing everything.

Furthermore, error 13 means type mismatch so what kind of data is in the key (policy number and policy name)?

Yes the policy number and plan name/description are in the key.

[kleptilian]

I think the SA part might be too difficult. That is the larger report and I am unable to determine what is causing the error. I can write macros to do index matches to pull the data in.

If you could help with the BP report as that is the more difficult one.

Thanks again for your help.