Find matching records in two worksheets

[kittybat]

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.

[Bob Umlas]

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&Sheet2!B1:B1000&Sheet2!C1:C
1000,0))

Bob Umlas
Excel MVP


"kittybat" <kittybat@discussions.microsoft.com> wrote in message
news:18946C7B-947D-458A-BDCF-C9DC27860842@microsoft.com...
> 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.
>
>
>
>

[kittybat]

Thanks for the quick reply! 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!


"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&Sheet2!B1:B1000&Sheet2!C1:C1000,0))
>
> Bob Umlas
> Excel MVP
>
>
> "kittybat" <kittybat@discussions.microsoft.com> wrote in message
> news:18946C7B-947D-458A-BDCF-C9DC27860842@microsoft.com...
> > 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.
> >
> >
> >
> >
>
>
>

[kittybat]

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&Sheet2!B1:B1000&Sheet2!C1:C1000,0))
> >
> > Bob Umlas
> > Excel MVP
> >
> >
> > "kittybat" <kittybat@discussions.microsoft.com> wrote in message
> > news:18946C7B-947D-458A-BDCF-C9DC27860842@microsoft.com...
> > > 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.
> > >
> > >
> > >
> > >
> >
> >
> >