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
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.
Highlighted

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
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
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies