Working with cells that have multiple data points

[Clint88]

The generic question is:
Is there some way to implement an automatic approach to search a cell with multiple data points and highlight the cell if any of the points are above a certain number and also bold those numbers that are above the thresh hold?

I am working with nurses in a hospital to create a spreadsheet for them to track and evaluate surgical site infections. A good chunk of the data is exported out of our Electronic Medical Record (EMR) and the rest is manual input.

The report out of the EMR has a number of cells that contain multiple data points within them. They are tracking glucose levels during surgery, temp during surgery, and SpO2 during surgery. The data in those cells are formatted as such:

format: data (time) | data (time) | data (time)
example: 96.3 (16:00) | 96.3 (16:01) | 96.3 (16:02)

Some can contain over 200 point of data in a single cell.

Is there a way to be able to evaluate a cell for a "data" point that is above a certain number and if there is one, highlight the entire cell and bold just the data point(s) that is/are high?

I have a Power Query built to reorganize the table and do some other calculations. This is all still in the "being built" phase. I can use Power Query to split out the values, but they want to see all of the values in the single cell with the above mentioned formatting on any ones that are above the limit.

In the attached file, cols A-BL (black headers) are what comes out of the EMR export and cols BM-CF (green headers) would be the manual entry that the nurses do. The data that needs to be analyzed and formatted is cols AR-AZ (purple headers).

Any guidance would be greatly appreciated.

Thank you!

 

[Clint88]

BUMP

 

[vletm]

Clint88
Please do not shout! ... do not use only capital letters.
Clint88
>> If You didn't ... shout, then You should able to write as everybody could even guess ... what are You meaning? ... eg Bring Up My post
>> Any way, if You just wait then You should figure that You should able to give more details, which would make Your thread more interesting.
> Is there a way to be able to evaluate a cell for a "data" point that is above a certain number and if there is one, highlight the entire cell and bold just the data point(s) that is/are high?
Yes - have You given reference values somewhere?
>> Have You shown somewhere Your expected results per Your given sample data?
Ps. I won't use any ... Power Query.

 

[Clint88]

I apologize, but I didn't shout. BUMP is an acronym, so all caps is standard. BUMP=Bring Up My Post.

 

[Clint88]

I have reuploaded the sheet with an Expected Results tab.

 

[p45cal]

Just a proposition which I hope you might like; instead of highlighting parts of cells and highlighting the cell to indicate there are such highlights within the cell, what if you remove all values from the cell that are within range (think SPO2 in OR Room above 90) leaving only those out of range? So an empty cell would show everything was fine, but a non-empty cell would only list those out of range values (with their times). Those cells would highlight themselves by not being empty and they'd only contain the data that would've been highlighted.
See column AZ in the Output sheet of the table starting at row 26 of the attached. This is the only column I've paid attention to, otherwise the table is the same as your raw data.

 

[vletm]

Clint88
Something like You've waited with red fonts too after You'll press [ Do It ]-button.

I tested with one sample to show those multiple-data-points as below... with red-line
> select cell to show > press [ Graph ] >> another press will hide it

 

[Clint88]

@p45cal - Thank you for your response! Unfortunately, that solution would not work for the end users of this sheet. I appreciate you taking the time to answer me.

@vletm - This solution works! I was able to use your code and edit it and apply it as needed to the Power Query table that will be the final output. Thank you for your time in solving this problem for me. It was a life-saver!