Healthcare project: group many data points together

[Steve_Barone]

Hi folks,

First post on here but I've been scouring the internet for about 2 weeks looking for information on how to do this but I haven't found anything that I can really say has helped me. I have two separate projects that will be beneficial to each other in the long run, but also to me as a learning experience. Look forward to collaborating with folks on here hopefully!

I have a very large dataset that I can certainly narrow down to do some testing but my problem is this: I need to be able to take that large amount of data and group it for one project, and filter it for another.

For the grouping: I have a list of exams performed on radiology patients that I need to group into a hierarchy for making business decisions. The data is strictly raw but I want to make it more robust. What I'm looking to do in this scenario is:

I have a list of exams which can be repeated because the exam name doesn't change, only the patients. For example I have say 400 exams that say "X-Ray chest 2 views" and 225 exams that say "X-ray ankle 3 views" in the same data set, all listed in the same column. I want to be able to do some type of complicated if-then formula but I can't find one that would suffice. I want to write something like "If column G has "X-Ray chest 2 views" then assign it a tag of "Body"" and "If column G has "X-ray ankle 3 views" then assign it a tag of MSK." There are about 240 different names it could be but I want to assign say 30 of them to be grouped into "Body" 70 of them be grouped into "MSK" etc.. I haven't found a way to do this in Excel outside of manual labor, which is time intensive. I was hoping to be able to write a formula or script and have it do the processing for me as I add data into it.

Scenario 2: I have report text all listed in Column N but I want to filter out unnecessary information. I want Excel to look in column N and search for numerical values between 5 and 20. Being that this column is loaded with report text, the column rows have lots of information in there but if the report doesn't include a number between 5 and 20 I don't want to see it, I want it to be filtered. 

Thanks, I am trying to be brief but descriptive, if additional information is needed please let me know!

[#2]

Please attach your sample file.

[#3]

Thanks for taking some time to help!

See attached. In column A that would be a unique identifier to separate each exam but to protect privacy I just made it a column of numbers.

Column B is the column I'm trying to work with, this is just a small snippet of the procedures but you can get the idea from looking at it.

Ultimately, I would want Excel to be able to let me write a formula that could be pretty big but it would say something like "If you see (x-ray chest 2 views; CT chest with contrast; CT chest without contrast; x-ray abdomen) in column B then in column F show the word "Body"; if you see (x-ray ankle left 3 views; x-ray foot right 2 views; x-ray knee right 2 views; MRI hip left with contrast) in column B then in column F show the word "MSK""

Does that make sense? I know if-then formulas can do this on a small scale but I want to be able to do it on a large scale with many different iterations.

Column D is a sample of the report data I want excel to sift through to find numerical values in the range of 6 to 20, but being able to have a wider range rather than having to do independent ones of saying "find 6 mm" then run it, then "find 7 mm" and run it. Not sure if that's possible.

Thanks!