Reconciliation of 2 big databases

K

Kazdima

Board Regular
Joined
Oct 15, 2010
Messages
226
Hello everyone,
I would appreciate everyone’s opinion and help in designing formulas and macros for reconciliation of 2 big databases.
One data is accounting internal data employees paid Health benefits ( 5…8 of them); and another one is Invoices from Insurance company with actual benefits claims by employees.
The discrepancy happened due to wrongfully calculated benefits premiums processed by internal accounting system.
The employees number is around 5,000. Employees are identified by Employees numbers.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
Thank you very much in advance.
<o:p> </o:p>
Kazdima
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Hello, there!!
Could you please clarify (give screenshots of both tables) what kind of fields you want to merge. What do you have ? What do you expect to get as a result? It will help us to understand your case.
 
Upvote 0
Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p> </o:p>
Thank you for reply.<o:p></o:p>
<o:p> </o:p>
I do not need to merge 2 data.<o:p></o:p>
<o:p> </o:p>
Like I wrote before there are 2 files with the data of benefits for employees- actual in accounting system vs billed by vendor.<o:p></o:p>
<o:p> </o:p>
Three main identifiers: Employee ID (name) and amount and date.<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
There is an example from the accounting data. Approximately the same data will be from the vendor.<o:p></o:p>
<o:p> </o:p>
<TABLE class=MsoNormalTable style="MARGIN: auto auto auto 4.8pt; WIDTH: 470pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 5.4pt 0in 5.4pt" cellSpacing=0 cellPadding=0 width=627 border=0><TBODY><TR style="HEIGHT: 51pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 38.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 51pt; BACKGROUND-COLOR: transparent" vAlign=bottom width=51>Accounting Code<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 42.05pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 51pt; BACKGROUND-COLOR: transparent" vAlign=bottom width=56>Date<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 37.85pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 51pt; BACKGROUND-COLOR: transparent" vAlign=bottom width=50>
MEX_ID<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 50.05pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 51pt; BACKGROUND-COLOR: transparent" vAlign=bottom width=67>
Employment Type<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 39.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 51pt; BACKGROUND-COLOR: transparent" vAlign=bottom width=53>
Community<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 35.1pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 51pt; BACKGROUND-COLOR: transparent" vAlign=bottom width=47>
Person Code<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 55.05pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 51pt; BACKGROUND-COLOR: transparent" vAlign=bottom width=73>
Name<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 38.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 51pt; BACKGROUND-COLOR: transparent" vAlign=bottom width=51>
PC3170 (HCP)<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 56.1pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 51pt; BACKGROUND-COLOR: transparent" vAlign=bottom width=75>
PC3175 (EE HCP REFUND)<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 31.3pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 51pt; BACKGROUND-COLOR: transparent" vAlign=bottom width=42>
PC4090 (ER HCP)<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 45.6pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 51pt; BACKGROUND-COLOR: transparent" vAlign=bottom width=61>
SUM<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 1; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 38.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=51>
9532<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 42.05pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=56>
1/10/2010<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 37.85pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=50>
419582<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 50.05pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=67> NTA<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 39.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=53>Toronto<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 35.1pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=47>
12365<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 55.05pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=73>Robert, Gordon<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 38.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=51> (10.00)<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 56.1pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=75> - <o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 31.3pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=42> - <o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 45.6pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=61> (10.00)<o:p></o:p>
</TD></TR></TBODY></TABLE>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
I need to compare Actual amount deducted from each employee to the billed one by vendor. The result should include: <o:p></o:p>
1. Date.<o:p></o:p>
2. G/L account<o:p></o:p>
3. Name <o:p></o:p>
4. ID<o:p></o:p>
5. Amount<o:p></o:p>
6. Actual paid<o:p></o:p>
7. Amount Billed<o:p></o:p>
8. Difference of over/underpayment.<o:p></o:p>
<o:p> </o:p>
Thank you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Macro for Excel to write an email and send the excel attachment in email
Replies
1
Views
722
Michael M
Michael M
L
  • Question Question
Alternative to V Lookup?
Replies
1
Views
319
lauz125
L
G
  • Question Question
Copy details from the uploaded input file and paste in destination file
Replies
0
Views
297
gvreddyhr
G
srizki
  • Question Question
Conditional Left( extract formula)
Replies
4
Views
729
srizki
srizki
C
  • Question Question
Accounting Number Format icon
Replies
0
Views
545
chayab
C

Forum statistics

Threads
1,224,616
Messages
6,179,912
Members
452,949
Latest member
beartooth91

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top