Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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&"*"&Sh eet2!$K$2:$K$1000,0)) -- Jim "kittybat" wrote in message ... |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. | |
#3
|
|||
|
|||
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&"*"&Sh eet2!$K$2:$K$1000,0)) -- Jim "kittybat" wrote in message ... |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. | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I compare data in two worksheets to find matching cells? | Excel Discussion (Misc queries) | |||
Find matching records in two worksheets | Excel Discussion (Misc queries) | |||
How do I find differences between two excel worksheets? | New Users to Excel | |||
How do I compare 2 worksheets, 1 old, 1 updated to find difference | Excel Discussion (Misc queries) | |||
Find a Function to use accross different worksheets | Excel Worksheet Functions |