Index and Match across multiple worksheets

[GTHORE]

It seems like this topic has been beaten to death, but I still can't incorporate prior recommendations into my current project.
An additional dilemma is that I am a healthcare provider and cannot post my excel file for review due to conflicting patient health information.

I am trying to display the date that a given patient had their surgery if they fulfill an if function (results are tabulated as 0 or 1). Their surgery date is on one worksheet, and the other data point is on another worksheet. They both have in common the same medical record number on each respective worksheet.

Questions:

1.) Am I am able to use the index and match function across different worksheets? My match function worked perfectly(it serves as the row_number for my index function), but I am showing a #REF error and have localized it to my index function.

2.) For the purposes of the index function, does the [column_number] have to be within the same worksheet? And do I continue to reference it at "8" instead of "Sheet3!H:H"

I would like to thank you in advance for any help that you may be able to provide.

[dilipandey]

Hi GTHORE,

welcome to the forum.
Suggest you to remove patient health information and enter some dummy data along with your expected results and upload the workbook. Thanks.

Regards,
DILIPandey
<click on below * if this helps>

[GTHORE]

DILIPandey,
Thanks for the reply. I removed all patient information and the unique number is simply a surrogate for patient health information. Unfortunately, due to size limitations, I could only include 8,000 patient in the raw data, and 1000 in the data set of interest. As a caveat, the raw data is not an exhaustive list and the unique numbers will not match in all situations.

The fields that I am unable to generate is cumulative B, E, I, L, P, S. These fields would return a date corresponding to the patient's surgery date. If these patients had a given type of surgery, it would report the first surgery date(as in B), and the second surgery would be in E. If these patients had more than two surgeries, I will return those values by hand.

As an introduction to the data; the cumulative tab is a database for patients treated at a hospital with given variables that I need to fill in. The "raw" tab contains the all of the patient information. There is a tab that I created that I designated the type of surgery of interest that they had.

The CPT corresponding to the surgery of interest are listed below:
URS: 52336, 52337, 52352, 52353
ESWL: 50590
PCNL: 50080, 50081, 50553, 50561, 50551, 50570, 50580, 50557, 50572, 50576
Open Pyelolithotomy: 50610, 50620, 50630, 50065, 50075, 50060, 50130
Laparoscopic Procedure: 50945, 49320, 50549
Nephrectomy: 50546, 50220, 50225, 50548, 50230, 50240

I would like to thank you in advance for your help with this.

[dilipandey]

HI GTHORE,

For first value, used below formula:-

Please Login or Register  to view this content.

For second value, used below formula which need to be entered with ctrl shift enter key combination.

Please Login or Register  to view this content.

See attachment:Dummy Data.xlsx

Regards,
DILIPandey
<click on below * if this helps>

[dilipandey]

Hi GTHORE, does my above post helped you ?

Regards,
DILIPandey
<click on below * if this helps>

[GTHORE]

Hi DILIPandey,
I went through your attachment as well as the code that you suggested and neither of them worked on my end.

I am just trying to show the date associated with these procedures. For example, on the file that you attached the value in cell I5 should read "5/22/2001 1:00:00 PM" as this corresponds to cell C2221. (Because I could only include 8,000 of the 17,000 data points in the raw data due to size limitations, not all unique numbers will match). The value in I5 shows "33" on my end.

Does this make sense?

Thanks for your help with this.

Greg

[dilipandey]

Okay Greg.. there in the data, we have following if you filter data for "72544004"

Unique Number Date of Service URS
72544004 5/22/2001 13:00 33
72544004 5/22/2001 13:00 44
72544004 5/22/2001 13:00 0
72544004 5/22/2001 13:00 0
72544004 5/22/2001 13:00 0
72544004 5/22/2001 13:00 0

now if you need "5/22/2001 1:00:00 PM", then this date/time is same for all the records, now which one you need ?

Regards,
DILIPandey
<click on below * if this helps>

[GTHORE]

DILIPandey,
Thank you for sticking with this problem, I really appreciate your help.

You are correct, this presents a problem. More fundamentally, there is a problem with my initial calculations. I generated a more comprehensive file that we can use, there are no gaps in the datapoints in this file. All formulas, however, have been removed. Dummy Data V1.1(Complete).xlsx

For the total number of each surgery (Column G, L, Q, R, S, T), these values were calculated by adding the sum of scores on (=COUNTIFS(Raw!F:F,Cumulative!B2,Raw!B:B,50590)) based on the corresponding CPT codes. When a surgery has more than one CPT(some surgeries have multiples codes(see my entry from 01-28-2013, 11:57 AM)) but shares the same date, this should only be counted as one. Additionally, only a single date needs to be returned(the date of the first ESWL would go in column 2), which corresponds to the set of CPT code for a given surgery.

Any thoughts?

Greg

[dilipandey]

Hi Greg, not able to find "(see my entry from 01-28-2013, 11:57 AM)" in your attachment. please check.

Regards,
DILIPandey
<click on below * if this helps>

[GTHORE]

The CPT corresponding to the surgery of interest are listed below:
URS: 52336, 52337, 52352, 52353
ESWL: 50590
PCNL: 50080, 50081, 50553, 50561, 50551, 50570, 50580, 50557, 50572, 50576
Open Pyelolithotomy: 50610, 50620, 50630, 50065, 50075, 50060, 50130
Laparoscopic Procedure: 50945, 49320, 50549
Nephrectomy: 50546, 50220, 50225, 50548, 50230, 50240

This just the list of CPT codes that apply to the various groups of surgery that we are investigating.

[dilipandey]

Is it there in the workbook ?


Regards,
DILIPandey
<click on below * if this helps>

[GTHORE]

No. I had to remove all of the formulas to reduce the file size. I am not at my office right now but I can post the formulas that I used for my initial calculations. It was a sum formula with multiple if values with the CPT codes as criteria. If you could just provide a template for how you would design the formula, I can do it myself to save you the work of doing that. It is somewhat time intensive.

[dilipandey]

I just need all the information on workbook which would be involved in the calculations.. may be a tabular form will do.


Regards,
DILIPandey
<click on below * if this helps>

[GTHORE]

DiliPandey,
Thanks for your help with this. I have included the formulas that I used for calculating the total number of procedures(new tab entitled formula). Like we mentioned, there is a flaw in my calculations in that if there were multiple CPT codes on a surgical date, these sum is given. There needs to be a limit such that you can only generate 1 value per surgery day. Any advice on this? Again, the end goal is to provide a date of surgery for each surgery date.

Thanks,
Greg