Home

Healthcare project: group many data points together

%3CLINGO-SUB%20id%3D%22lingo-sub-735098%22%20slang%3D%22en-US%22%3EHealthcare%20project%3A%20group%20many%20data%20points%20together%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-735098%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20folks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20post%20on%20here%20but%20I've%20been%20scouring%20the%20internet%20for%20about%202%20weeks%20looking%20for%20information%20on%20how%20to%20do%20this%20but%20I%20haven't%20found%20anything%20that%20I%20can%20really%20say%20has%20helped%20me.%20I%20have%20two%20separate%20projects%20that%20will%20be%20beneficial%20to%20each%20other%20in%20the%20long%20run%2C%20but%20also%20to%20me%20as%20a%20learning%20experience.%20Look%20forward%20to%20collaborating%20with%20folks%20on%20here%20hopefully!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20very%20large%20dataset%20that%20I%20can%20certainly%20narrow%20down%20to%20do%20some%20testing%20but%20my%20problem%20is%20this%3A%20I%20need%20to%20be%20able%20to%20take%20that%20large%20amount%20of%20data%20and%20group%20it%20for%20one%20project%2C%20and%20filter%20it%20for%20another.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20grouping%3A%20I%20have%20a%20list%20of%20exams%20performed%20on%20radiology%20patients%20that%20I%20need%20to%20group%20into%20a%20hierarchy%20for%20making%20business%20decisions.%20The%20data%20is%20strictly%20raw%20but%20I%20want%20to%20make%20it%20more%20robust.%20What%20I'm%20looking%20to%20do%20in%20this%20scenario%20is%3A%3C%2FP%3E%3CP%3EI%20have%20a%20list%20of%20exams%20which%20can%20be%20repeated%20because%20the%20exam%20name%20doesn't%20change%2C%20only%20the%20patients.%20For%20example%20I%20have%20say%20400%20exams%20that%20say%20%22X-Ray%20chest%202%20views%22%20and%20225%20exams%20that%20say%20%22X-ray%20ankle%203%20views%22%20in%20the%20same%20data%20set%2C%20all%20listed%20in%20the%20same%20column.%20I%20want%20to%20be%20able%20to%20do%20some%20type%20of%20complicated%20if-then%20formula%20but%20I%20can't%20find%20one%20that%20would%20suffice.%20I%20want%20to%20write%20something%20like%20%22If%20column%20G%20has%20%22X-Ray%20chest%202%20views%22%20then%20assign%20it%20a%20tag%20of%20%22Body%22%22%20and%20%22If%20column%20G%20has%20%22X-ray%20ankle%203%20views%22%20then%20assign%20it%20a%20tag%20of%20MSK.%22%20There%20are%20about%20240%20different%20names%20it%20could%20be%20but%20I%20want%20to%20assign%20say%2030%20of%20them%20to%20be%20grouped%20into%20%22Body%22%2070%20of%20them%20be%20grouped%20into%20%22MSK%22%20etc..%20I%20haven't%20found%20a%20way%20to%20do%20this%20in%20Excel%20outside%20of%20manual%20labor%2C%20which%20is%20time%20intensive.%20I%20was%20hoping%20to%20be%20able%20to%20write%20a%20formula%20or%20script%20and%20have%20it%20do%20the%20processing%20for%20me%20as%20I%20add%20data%20into%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EScenario%202%3A%20I%20have%20report%20text%20all%20listed%20in%20Column%20N%20but%20I%20want%20to%20filter%20out%20unnecessary%20information.%20I%20want%20Excel%20to%20look%20in%20column%20N%20and%20search%20for%20numerical%20values%20between%205%20and%2020.%20Being%20that%20this%20column%20is%20loaded%20with%20report%20text%2C%20the%20column%20rows%20have%20lots%20of%20information%20in%20there%20but%20if%20the%20report%20doesn't%20include%20a%20number%20between%205%20and%2020%20I%20don't%20want%20to%20see%20it%2C%20I%20want%20it%20to%20be%20filtered.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20I%20am%20trying%20to%20be%20brief%20but%20descriptive%2C%20if%20additional%20information%20is%20needed%20please%20let%20me%20know!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-735098%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-735200%22%20slang%3D%22en-US%22%3ERe%3A%20Healthcare%20project%3A%20group%20many%20data%20points%20together%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-735200%22%20slang%3D%22en-US%22%3EPlease%20attach%20your%20sample%20file.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-736313%22%20slang%3D%22en-US%22%3ERe%3A%20Healthcare%20project%3A%20group%20many%20data%20points%20together%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-736313%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20taking%20some%20time%20to%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attached.%20In%20column%20A%20that%20would%20be%20a%20unique%20identifier%20to%20separate%20each%20exam%20but%20to%20protect%20privacy%20I%20just%20made%20it%20a%20column%20of%20numbers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20B%20is%20the%20column%20I'm%20trying%20to%20work%20with%2C%20this%20is%20just%20a%20small%20snippet%20of%20the%20procedures%20but%20you%20can%20get%20the%20idea%20from%20looking%20at%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUltimately%2C%20I%20would%20want%20Excel%20to%20be%20able%20to%20let%20me%20write%20a%20formula%20that%20could%20be%20pretty%20big%20but%20it%20would%20say%20something%20like%20%22If%20you%20see%20(x-ray%20chest%202%20views%3B%20CT%20chest%20with%20contrast%3B%20CT%20chest%20without%20contrast%3B%20x-ray%20abdomen)%20in%20column%20B%20then%20in%20column%20F%20show%20the%20word%20%22Body%22%3B%20if%20you%20see%20(x-ray%20ankle%20left%203%20views%3B%20x-ray%20foot%20right%202%20views%3B%20x-ray%20knee%20right%202%20views%3B%20MRI%20hip%20left%20with%20contrast)%20in%20column%20B%20then%20in%20column%20F%20show%20the%20word%20%22MSK%22%22%3C%2FP%3E%3CP%3EDoes%20that%20make%20sense%3F%20I%20know%20if-then%20formulas%20can%20do%20this%20on%20a%20small%20scale%20but%20I%20want%20to%20be%20able%20to%20do%20it%20on%20a%20large%20scale%20with%20many%20different%20iterations.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20D%20is%20a%20sample%20of%20the%20report%20data%20I%20want%20excel%20to%20sift%20through%20to%20find%20numerical%20values%20in%20the%20range%20of%206%20to%2020%2C%20but%20being%20able%20to%20have%20a%20wider%20range%20rather%20than%20having%20to%20do%20independent%20ones%20of%20saying%20%22find%206%20mm%22%20then%20run%20it%2C%20then%20%22find%207%20mm%22%20and%20run%20it.%20Not%20sure%20if%20that's%20possible.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Steve_Barone
New Contributor

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 Replies
Please attach your sample file.

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!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies