Comparing Data Between Two Spreadsheets [Closed]

Report
Posts
1
Registration date
Friday October 17, 2014
Status
Member
Last seen
October 17, 2014
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
I have two spreadsheets that includes account number, name, and a charge amount for one (positive number) and a waiver amount (negative number) on the other. Both lists are very similar and may contain the same people, but one may have names the other does not.

Exampe:
List A
ID NAME Charge
1 Joe 50
2 Jane 50
2 Jane 50
3 Judy 50

List B
ID NAME Waiver
1 Joe -50
2 Jane -50

In this example, Joe was charged $50 and then it was waived. Jane was charged $50 twice, but one was waived, so she still owes $50. Judy was charged $50, but was never waived so she owes this amount.

How can get Excel to auto compare these for me and either highlight the differences or pop into a new spreadsheet/cell?

Example of different data:
ID NAME Charge
2 Jane 50
3 Judy 50

Can anyone help me? I'm thinking a Macro or an IF statement of some kind. Maybe even a VLookup?

Thank you,
Keltikroze
<<email address removed>>@aol.com

1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
793
open the file from

http://speedy.sh/DWrku/keltikroze-141019.xlsx



in sheet 1 unique names (you can use advance filter to get unique name of col B (B1 kto B5) are copied from A11 downb

sees formuls in B11 (which is invoked by control shift enter),C11 and D11
copy these formulas down


if you need a macro,record macro making these steps and edit macro

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!