Complex Search

[thompa2]

Hello,

Not sure where to start with creating a data filter.

I have a spreadsheet with patient data, each patient having a unique number. Each patient has multiple cancer diagnoses, each one of those with a date. What I want to do is identify all patients who have a second cancer diagnosis within 6 months of their original diagnosis.

Example.JPG

Here's a screenshot of an example of my dataset. The red box represents a patient that had 2 cancer diagnoses within 6 months. These are the people I want to create an automated search for, and then export a list of their Patient IDs.

I'm sorry for noobing out on all of you, just not sure where to start. Thanks.

[mehmetcik]

Hi

Most of us will not even look at an image of your requirement.

Why would we want to waste our time retyping your data? Sometimes with errors? due to formatting?

But as this is a medical issue I will look at your data.

[mehmetcik]

Hi

Most of us will not even look at an image of your requirement.

Why would we want to waste our time retyping your data? Sometimes with errors? due to formatting?

But as this is a medical issue I will look at your data.


Ok your requirement is quite simple.

The process is to sort your data to ensure that all your Patiant IDs are in sequence.

In column E enter a formula such as the following for cell E2.

Please Login or Register  to view this content.

Copy and paste values into column E.

Now resort your Data using column E.

Select the part of column E that contains 1's

Expand that selection to the whole row.

Copy the selection into a new sheet.

Now delete column e.


So that is the process.

I will create a fully annotated macro for you, as this is for medical use,

so that you can understand it and can load it your self into your pc.

I will not send you a file, in this instance.

Please Login or Register  to view this content.

[thompa2]

Hello,

Thanks for your help. I think I should clarify. I'm not simply looking for a list of unique Patient ID#s. I already found that function in excel .

What I'm trying to do is more complicated.

This is a list of events. Each row represents a single event, which happens to be a cancer diagnosis. However, every patient has at least 2 events, some have more than 7.

I need a script that will evaluate each event, as a function of each patient, and return a list of patient IDs that only contains that subset of patients who had at least 2 events occurring within 6 months of each other.

Thank you again for helping out.

[mehmetcik]

This is a better model,

It selects all a patients records if it meets your requirements.

Please Login or Register  to view this content.

[thompa2]

Thanks!!
For some reason your script won't work on my version of Excel (2003) that I use at the hospital, but it works just fine on my version of Excel (2010) I have at home.

[mehmetcik]

Unfortunately I cannot help you identify the problem area.

If you could step through the macro and tell me where it fails then I can create a work around.

[thompa2]

When I step through, it fails on line 11: Run-Time Error 1004: Application-defined or object-defined error

Please Login or Register  to view this content.

Edit: It looks like the problem is the Sort fxn, it doesn't exist in Excel 2003, wasn't put in until Excel 2007.

[mehmetcik]

Try deleting this line completely.

And try running the macro again.

I recorded the sort part of the macro,

there are often redundant lines in the recorded macros.

If that does not work then record yourself doing the search

and insert that instead of my sort routine, after modifying the cell references.