How to create an exception report with pivot table

[melinda]

Good Evening,
I have been trying to create a pivot table that shows the exception and matches for the attached data.
Tab 1 has modified data, Tab 2 download, and tab 2&3 is where the pivot table would be if I could figure it out.

Basically I am looking to compare the 1st two tabs,

For Each Account number general fund=gf.....ltdi trust fund=di
noting the differences and matches.

Data to be compared.........Beginning Balance to be added manual

Account name
Cusip
Description
Par
Principal
Interest Rate
Maturity Date


So basically compare the above data by account number with the above criteria, and give me the exceptions and a match on different tabs by account name/number. And room to grow and add further accounts as needed.

Thank you all so much for this help, I've spent a week trying and failing.
Mel

 

[GerryPerry]

Hi
I had a look at your excel but dont REALLY get the goal. May I suggest you fill in a couple of examples into the 2 destination sheets to illustrate ?
BR
Gerry

 

[melinda]

I have notes in the exceptions tab...THANK YOU!

 

[GerryPerry]

Hi Melinda
Attached is A solution. I had to put in a small table in which I inferred a code to description of the account as your 2 sources mixed these.
I produce a "combo" sheet on which is a button which runs a single macro which produces a neat "both sources" layout which I THINK does what you wished.
I think you have a data issue on "rate" regarding the decimal places on the 2 sources.
From the "combo" sheet you can VERY EASILY derive the match/ nomatch AND ERROR ( literally no record found ) and split into different sheets if required.
I deliberately left the code very repetitive and simple rather than put in clever loops. Its easier to read and runs in much less than a second anyway.

Good luck and good weekend

Gerry