Find Matching Records in Two Worksheets

[kittybat]

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&Sheet2!B1:B1000&Sheet2!C1:C1000,0))
> >
> > Bob Umlas
> > Excel MVP
> >
> >
> > "kittybat" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.

[Jim Rech]

I hope this works better. The essential thing missing was the absolute
signs ("$"). Also I added asterisks between items to reduce the likelihood
of the false match. Remember this formula has to be put on row 2 (like in
cell A2) and entered via Ctrl-Shift-Enter. And then copied down to the rows
below.

=INDEX(Sheet2!$A$2:$A$1000,MATCH(F2&"*"&G2&"*"&K2,Sheet2!$E$2:$E$1000&"*"&Sheet2!$H$2:$H$1000&"*"&Sheet2!$K$2:$K$1000,0))

--
Jim
"kittybat" <[email protected]> wrote in message
news:[email protected]...
|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&Sheet2!B1:B1000&Sheet2!C1:C1000,0))
| > >
| > > Bob Umlas
| > > Excel MVP
| > >
| > >
| > > "kittybat" <[email protected]> wrote in message
| > > news:[email protected]...
| > > > 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]

That's it!!! Thank you so much, your solution works beautifully. Now I've
just got to pick it apart and try to learn from it.......

"Jim Rech" wrote:

> I hope this works better. The essential thing missing was the absolute
> signs ("$"). Also I added asterisks between items to reduce the likelihood
> of the false match. Remember this formula has to be put on row 2 (like in
> cell A2) and entered via Ctrl-Shift-Enter. And then copied down to the rows
> below.
>
> =INDEX(Sheet2!$A$2:$A$1000,MATCH(F2&"*"&G2&"*"&K2,Sheet2!$E$2:$E$1000&"*"&Sheet2!$H$2:$H$1000&"*"&Sheet2!$K$2:$K$1000,0))
>
> --
> Jim
> "kittybat" <[email protected]> wrote in message
> news:[email protected]...
> |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&Sheet2!B1:B1000&Sheet2!C1:C1000,0))
> | > >
> | > > Bob Umlas
> | > > Excel MVP
> | > >
> | > >
> | > > "kittybat" <[email protected]> wrote in message
> | > > news:[email protected]...
> | > > > 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.
> |
>
>
>