Find Matching Records in Two Worksheets
I think my follow-up to my original question got buried in the sands of time
; ) so I am re-posting as a new question. I think the solution given below
should work, but I can't quite get it to go. I need the help of an expert to
"translate" the formula to match my spreadsheet. Please see below for the
original thread:
-------------------------------------------
I think this is the right solution, but I am having trouble getting it to
work. When I remove all the extraneous columns and match my spreadsheets to
the example (ServiceDate is column B, Procedure is column C, and $Billed is
column D) on each sheet, the formula pulls the first Claim# for every line,
whether it has a match or not. If I change the formula to match the location
of my data, the formula returns 0 on each line.
Perhaps if I give you the actual columns for the existing data, there will
be less room for error on my part.
On sheet1, the columns are ServiceDate - col F, Procedure# - col G, $Billed
- col K.
On sheet2, the columns are ServiceDate - col E, Procedure# - col H, $Billed
- col K.
Can you translate the formula to match these data locations? Obviously, I'm
doing it incorrectly.
Thank you!
Kittybat
"Bob Umlas" wrote:
Assuming the fields are in columns A:E, then you need this in Sheet1, cell
F2, for example (on the Smith,John line):
Enter this formula by holding Shift+Ctrl before pressing enter:
=INDEX(Sheet2!A:A,MATCH(B2&C2&E2,Sheet2!A1:A1000&S heet2!B1:B1000&Sheet2!C1:C1000,0))
Bob Umlas
Excel MVP
"kittybat" wrote in message
...
I work at an health insurance company and frequently need to reconcile
reports from our physicians with claim data. I need to find records on
two
different spreadsheets that match on three data points and insert a claim
number on matching records. Here's an example:
Spreadsheet 1, from our physician group, contains treatment data for one
patient.
PhysicianName ServiceDate Procedure# $billed $paid
deductible
Smith,John 12/01/03 99213 75.50 25.00
25.00
Doe,Mary 01/25/04 99215 125.00 90.00
0.00
Spreadsheet 2, from our server, contains claims data for one patient.
Claim# ServiceDate Procedure# $billed $paid
deductible
123456789 12/01/03 99213 75.50 25.00 25.00
123687432 01/27/04 99215 125.00 90.00 0.00
I need to find records on spreadsheet 1 that exactly match spreadsheet 2
on
the fields ServiceDate, Procedure#, and $billed. When a match is found, I
need the corresponding Claim# inserted after the record on spreadsheet 1.
So
on the example data above, the first row would be a match, because all
three
relevant fields are the same. the second row is not a match, because the
ServiceDate field does not match.
I figure that I'll need a combination of MATCH and INDEX, but I haven't
been
able to pin down exactly how to accomplish this. Any help you can give
will
be very much appreciated.
|