MATCH-INDEX issue

There is a sorting problem that I ran into that I am having difficulty with (Attached-truncated).

The crux of the problem is that I have a running list of patient encounters specific to their patient ID (edited). I need to build an algorithm or figure out a way to pick up the BMI of the patient from the first visit that was at least a year in the future. If there was no visit, just N/A is fine. My thought it to nest a MATCH formula in an INDEX formula and use an IF statement. I would first sort the dates in order and then PatientID to keep them grouped. Unfortunately, it seems that the MATCH function requires me to have the dates ordered in ascending order and thus it keeps me from breaking it down by patient. The real file is 150k line items and thus I can not manually divide the list by patients.

If anyone knows a way to get around this..

Hi

Are you trying to find the date for the first appearance of an ID, or for all appearances of an ID? So for ID 32, would you only want to find 26/5/2009, (the first relevant date after 4/3/2008), or the relevant date for all instances of ID 32?

rylo

I am trying to find out if a BMI was recorded (and what it was) for the first subsequent visit that occurred a year out.

Every line item is an encounter that would have to be matched by patient ID and encounter + 365 for the next BMI.

Does this make sense?

BMI is a ratio that probably should not be worked with as a number. This is why I kept it as txt.

I am not sure how using a pivot table would help. Would you please explain?

HI

Ok, try this. Select columns A:C and sort on Patient ID (ascending) and Encounter Date (Descending). This should sort your data by ID, but you will have descending dates.

Then create a defined name with a name of edrng, and a refers to: =OFFSET(Sheet1!$A$1,MATCH(Sheet1!$B9,Sheet1!$B:$B,0)-1,0,COUNTIF(Sheet1!$B:$B,Sheet1!$B9),1)

D2: =MATCH(A2+365,edrng,-1)
E2: =INDEX(edrng,D2)

Copy these down.

Check that the returned dates match what you are trying to achieve.

rylo

I appreciate the help. This is an interesting way of getting around the issue of match freaking out when sequencing the data by ID after ordering by encounters. I will try it when I get home tonight.

Considering that it is almost impossible for more than 300 visits to occur for a patient, is it possible to use Sheet1!B2:B300 or something similar to simplify the operations? I am doing this for some 20k patients, 150k line items in the the real document.

Hi

simplify in what way? Only process 1 patient ID at a time? Or for a nominated patient?

What simplification are you trying to achieve?

rylo

The processing of the formula. I am worried that my computer will not do so well if the defined name is such a large dataset. I might be completely off. Just some past experiences doing sumifs without constricting the length of the array lead me to poor results.

Hi

So how would you want to subset your data for processing? And what do you want to do with the output from the subsetted data?

rylo

I am probably not explaining it properly. I previous portion of my analysis involved figuring out if patients that came in and were diagnosed with pre-diabetes had been diagnosed with DMII within a year in a subsequent visit. I used after ordering the dates and arranging the patient ID together:

=if(COUNTIFS(C2:C500,C2,H2:H500,"II",B2:B500,"<"&B2+356)>0,"Yes","No")

I did not use $C:$C because that would cause excel to crash. I knew that it did not make sense to analyze more than 500 subsequent line items since it is impossible to have that many visits after your initial one within a year.

Hi

Clear as mud! How about you try the formulas I've given you and see how they work in your environment. I don't have any problem with my PC and the example file. Took less than 1 sec to calculate. However, didn't scope up to the 150k rows you mentioned in #1.

If you do have performance issues, then either put up a new example file, or update your existing example file, that shows exactly what you would like to extract from your sample data, explain how it is obtained. Don't worry about trying to do it by formulas, just put in the results and give an explanation.

rylo

Why do you have $B9 in the definition? Seems arbitrary.

Please see attached. Doesn't look like it worked for me.

Hi

Have a look at 915213.xlsx

rylo

Rylo,

This works like a charm. Not sure what you did, but it will be interesting to dissect the logic when I get a chance.

Thank you