Solved

How can I filter an Excel sheet based on a column's inactivity

Posted on 2016-08-02
29
64 Views
Last Modified: 2016-08-15
HI experts,

I have an Excel spreadsheet with various columns. One of those is a column called "Comments." I would like to be able to filter rows based on the properties of the Comments.

I am looking to be able to filter the Comments based on a certain amount of days that it has been inactive, i.e. nothing has been written in it or deleted from it. If the number has to be fixed such as 7, then OK. I would rather be able to choose the number of days on different searches.

Thanks.
0
Comment
Question by:Bert2005
  • 14
  • 10
  • 3
  • +2
29 Comments
 
LVL 7

Expert Comment

by:tomfarrar
ID: 41739876
0
 
LVL 1

Author Comment

by:Bert2005
ID: 41740000
Hi tomfarrar, (or should I call you Tom)?

I don't think so. Let me explain what I am trying to do. I should have linked from another question. But, I wanted to start new.

So, basically, I am a doctor, and I want my referral specialist to use a spreadsheet to keep track of the referrals, i.e.

Patient | Phone | Consultant | Phone# | Etc. | Comments |

What happens it she will start the referral, say send a patient to a cardiologist. Send records and notes and await a referral date and time. Well, time will go by and she is supposed to call and check to see what the status is every seven days. BUT, OF COURSE, I PAY HER $20.00 AN HOUR to not do it.

I would be cool to be able to set the filter to seven days and then show every patient that has not been updated. Because had she gone in and checked within seven days or on day seven, then she would have added: Still waiting for approval, will call us with date and time within a week, need more records, etc.

Otherwise, out of say 75 referrals, two months will go by, the parent will call and say what is the deal with my referral to cardiology, she will call and find out they never even got the records or referral.

It would also be cool if I could click on 7 days or longer, and see if any updates have been made to the comments section. I think I will start here. And, then see if you have any questions. Or if you have a better way to do it. Not sure if I can upload an Excel sheet. I can always upload it to Box.com, if that makes it easier.

Thanks for your help.
0
 
LVL 7

Expert Comment

by:tomfarrar
ID: 41740018
Hi Doc - It would be to upload a sample file, and you can by using the attach file hyperlink at the lower left hand side of the comment box you use to post here at EE.
0
 
LVL 1

Author Comment

by:Bert2005
ID: 41740035
OK, will do. I have an IT person (MCITP) not a kid down the street who plays Ninetendo, lol, logged in remotely. As soon as he gets off, I will send the  file. I will email him now and see if he can email it to me.

Please call me Bert. :)
0
 
LVL 1

Author Comment

by:Bert2005
ID: 41740062
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41740144
Some dummy data is what is needed really. What form are the comments in? Is it just Text, not cell comments?

If it's a comment made in the cell itself then you can simply filter for blanks
0
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 200 total points
ID: 41740267
Why not just add another column after the Comments for "Update Date". Then you can use standard filters on that column.

Thanks
Rob H
0
 
LVL 17

Expert Comment

by:xtermie
ID: 41740550
Well as I see it if the info is already on the spreadsheet, then you can you use a simple formula to calculate days passed.
For example if you need to just calculate the days from column Bx to today, add a column next to comments to do that (I named it days passed in the example):
O2        =TODAY()-B2, format to a number and add conditional formatting to get pink after X days
(in my example attached I have it PINK after 7 days)

If you need to check other variables, like Status = pending we could add an if statement in the calculation
P2          =IF(A2="pending";TODAY()-B2;0)

Check the sample I've attached and it may help you out if I understood what you were after :)
Referrals_example.xlsx
1
 
LVL 7

Assisted Solution

by:tomfarrar
tomfarrar earned 300 total points
ID: 41741189
Hi Doctor - I tend to agree with the Experts who posted above about the date, but that is if we understand what you are asking.  I have included your spreadsheet with a few questions about what each field means (agreeing with Roy Cox above, but also understanding the sensitivity of data).  Also I have included a PDF diagram so as to better understand where the data comes from and at what point in the process.  Just some things to think about....
Visio-DOCTOR.pdf
Referrals.xlsx
0
 
LVL 1

Author Comment

by:Bert2005
ID: 41741623
Thanks everyone,

Sorry, I have been away. I will look at these now. I see now how it would be difficult to do without data, but also, as Tom states, the data has to remain HIPAA compliant. I wish I knew a little about Excel. But, then I wouldn't be asking.

Thanks. Bear with me.
0
 
LVL 1

Author Comment

by:Bert2005
ID: 41741627
To answer Roy's question (this without looking at the example sheets, yet), it is text. You can't just filter on blanks, because it will continue to be updated:

08/02/16 Send progress notes and referral to Dr. Smith.
08/09/16 Have not heard back from Dr. Smith's office. I called and left message with receptionist
08/10/16 Received call from receptionist. Still waiting for Dr. Smith's approval.
0
 
LVL 7

Expert Comment

by:tomfarrar
ID: 41742793
Let's circle back to the original question:  If you are asking to know the last time the Comments column was updated (not worried about the other columns), I think a solution that tags a date of last update could be what you are looking for. Then another columns that would compare that date to the current date.   In the example data you showed 08/10/16 would be the important date for filtering.  For instance, if no other changes were made, this row would show up as 7 days old on 08/17/16.

If this is what you are looking for, I think the original link I provided, in conjunction with other Expert responses comparing date to current date,  would get you down the road to a solution.  Keep the conversation going.
0
 
LVL 1

Author Comment

by:Bert2005
ID: 41745074
Hi again,

Thanks again for all the comments. I always finish my questions, and I always try to grade appropriately. I have been away for a couple of days. I will post back in a day or so.

I really apologize. Everyone is helpful, and I feel like I have neglected you.
0
 
LVL 1

Author Comment

by:Bert2005
ID: 41746541
Referrals_Temp.xlsx

OK, I think it is best to get the sheet set up correctly with the proper add-in info working, before actually obtaining what I need to do. First, Tomfarrar knows more than I do about how to make the referral sheet, lol. So to hopefully answer some questions and ask some questions, I will comment by column. Unfortunately, there are a lot of dates. Referrals are one of the biggest headaches in medicine because 1) there are many to keep track of, 2) each consultant does it differently -- they tell us the date or they call the parent -- which we hate, because we are out of the loop, 3) some can be six to twelve months down the road and 4) the referral specialist is never as OCD and anal as the physician and just doesn't stay on top of things.

A. This should be either pending or complete
B. This should be the date the referral specialist for my office (RS) enters the initial information from the consult. The consult is ordered by me from the patient's record and is sent to the RS's inbox. That is the official documentation, but it ends up having 80 or 90 pending consults. It is not user friendly and other staff cannot access the data. So, even though it is double entry, I need her to enter it in the above format, so it is accessible, easy to follow, and she can't miss a step
C. Last name of patient
D. First name of patient
E. Best phone number to reach patient
F. The name of the consultant, which I hope will begin to self populate. It would be nice if it then automatically populated the phone and fax number of that doctor
G. Date records sent. Usually will be the same date as B. I have it where it can drop down to Sent or Non sent. I am trying to change that to date calendar, but I can't figure out how
H. This should be the name of the person at the referral office and the date the RS confirmed they received the records. This should be the following day to give the consultant time to get the records from their fax machine. THIS IS ONE OF THE TWO BIGGEST REASONS THE FORM HAD TO BE CREATED. My RS simply would not call to confirm they received the records. So, weeks go by, we call, and they say they never received the referral. She tends to think that once she sends the referral: progress notes, records, insurance information; that it is started. The other thing is we call sometimes, and they say, "Oh we don't see anyone under 12 yo." Well, that could have been confirmed the next day.
I. Phone number of consultant
J. Fax number of consultant
K. Date of referral
L. Time of referral (I don't know what happened, but when you enter the date and double-click in the cell, both the date and time shows up. The date goes away when you click in another cell or save. Certainly being able to do one column for date and time would be easiest
M. This is always tough. I want a date (again) when the patient was notified. This is another hugely important column
N. The comments is the entire reason this question is being asked. This has to be a text column. The RS needs to be able to each day quickly see what referral has not been completed in the past seven days. It is also difficult, because some referrals take a year, so you can't call the consultant every week. This is something the RS just doesn't do or care about. So, a referral can go five weeks just sitting there. The parent or patient calls wanting to know the status and there is no info. Unfortunately, not only is this for the RS, it is one more thing I have to check so I can to her and say, why is their no activity on this?

At some point, maybe I have to just say I need an Excel specialist to do this for me. Of course, even then, you would have to go back and forth explaining why this is needed or important, but I guess that is what a professional would do.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 7

Expert Comment

by:tomfarrar
ID: 41747085
I am sure you will get some good feedback on the additional information provided.  I will try to look at it tonight myself.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41747177
I realise this discussion has been going on for some time now and looking at using Excel.

Have you considered Excel may not be the best platform for the task and looked at using other options???

I am not a database specialist but I suspect this would be fairly standard for a Customer Database with in-built tables for customer (aka patient) details and consultant details. Each patient record could then have a sub section for history notes, contact types etc.

The reporting side would then be an extract of the data.

I used to work in an office where we used a piece of software that kept all the customers' detail and all contacts with the customer were registered. It could be linked to MS Word templates for sending out letters; when a letter was sent a copy/link was stored against the customer's record. Unfortunately, I can't remember the name of software; apologies.
1
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 200 total points
ID: 41747203
I found something online, I think it was called GoldMine. It was 20 years ago that I was using this so I might be wrong, I thought it was a Microsoft product at the time.
0
 
LVL 7

Expert Comment

by:tomfarrar
ID: 41748159
Yes. the conversation has gone on a bit, but this is perhaps not a simple answer.  And though I hope the solution to be fast, the correct answer and approach may take a bit of time.  See comments on attached.
EE.docx
0
 
LVL 1

Author Comment

by:Bert2005
ID: 41754831
Once again, this is a very difficult subject for me, and my work has been overwhelming. I will get through these comments, and I will comment over the weekend.

I have around 600 questions on EE since 2005, and I have never abandoned a question. It is embarrassing to see that.

Thanks again. Your work and help is very appreciated.
0
 
LVL 7

Accepted Solution

by:
tomfarrar earned 300 total points
ID: 41755075
So, Bert2005, all of us are probably busy at our other jobs, and this has gone on longer than we all hoped.  My thoughts are:

- It is important to know if you expect to see more than one comment for each referral.  I have been under the assumption that if a second comment are made on the status of a referral, the second comment replaces the first (and has a new date that replaces the previous comment's date).  This question is really true of other fields (columns) in your spreadsheet.  In summary, will there only be one row for each referral, or can there be more than one row with each comment and its date being in a separate row?

-  To clarify what I have mentioned in the first item above it would be of great benefit if a sample spreadsheet is provided with data showing what a  the spreadsheet might look like as of today.  The critical data (names, addresses, phone numbers, etc.) could be changed to protect the privacy of the information.  Not sure how far this would need to be taken to comply with HIPPA, but a sample would help immensely.

- Based on answers to the above, the Excel (or other options) can better be explored in my opinion.

My thoughts....
0
 
LVL 1

Author Comment

by:Bert2005
ID: 41755138
@Rob,

Just to get my question on track, I do like your suggestion. If I could find the right software, that may be a good option.

The irony is if I could just get my staff to do what they are supposed to do, this could be done on paper, lol. But, alas, we are a paperless office. This "program/database" is to accomplish three things. One is to allow-force the referral specialist to do her job correctly, the second is to allow everyone in the office to view the referrals quickly, and thirdly, allow me to monitor her work.

And, there isn't much to monitor. The referrals, getting the records together, doing the insurance work, prior authorizations, etc. is top notch, and I wouldn't have a clue as to how to do it. I just have to monitor that it is being done correctly.

For the most part, she does a great job and has to multitask with checking patients in and out and answering phones, etc. She has plenty of time to do her work though. Some times it is the patient's fault. I tell all patients they should hear back within seven days (hence the seven day rule), and, if they don't to call. Some fall through the cracks mostly due to the consultant's office. But, at times, I will be in the room, and the patient will ask how is that dermatology referral coming? I haven't heard anything in three months -- which is part of the patient's fault.

But, worse, is when we get a call that where the patient/parent says, I have called and/or left messages three times and have not heard back. It's nice (and many times happens, that it is clearly documented "Patient called on July 20th. Informed him that we are still waiting from dermatology to approve the referral. Offered to refer to another office. Patient aware." Nice to have that.

Lastly, what drives me crazy is when the referral specialist neglects to call the consultant's office the following day with, "Did you receive our referral." There is nothing worse than finding out three weeks later, they never got it. They probably did, but it means nothing. And, once again, had you called in seven days, you would have found that out.

I don't write this novel to vent. Just to give a little more information that may help.

Yes, Rob, you are correct. Our electronic medical records works great with Access. Then it was upgraded to SQL Server, and it worked far better and became HIPAA compliant. Sometimes, you just need a better mouse trap.
0
 
LVL 1

Author Comment

by:Bert2005
ID: 41755141
@Tom,

Your are most likely correct that everyone is busy at their own jobs. I hope you didn't take my comment to mean hey everyone, please comment right away, and I will get back to you when I can. I am highly embarrassed that I have taken so long to get back, and also realize that it has made it more difficult.

Your comment is a good one and one that has made me really look at this a little bit differently. Yes, the comments would have to be separate comments. I just tried out typing quite a bit of information in the comments section. I don't know how to set up wraparound text, although I am sure I could figure it out. But, even when using the enter key, it seems to take up another row.

It seems as though the function of this sheet would have two purposes, one of which would work and is already in place, and the other the more difficult of accomplishing what I am trying to do here. In other words as to having all the data at your fingertips would be helpful, but being able to use some macro or formula so that it shows only those rows which need to be addressed based on a certain criteria seems much more difficult. Just the realization you brought up on the comments section needing to take up so much space makes it nearly impossible.

As EE reminds its users all the time, sometimes the correct answer is "No, it can't be done." I am not quite ready to give up yet. The only sticking point is filtering by the comments section.

Thanks.

Bert
0
 
LVL 7

Assisted Solution

by:tomfarrar
tomfarrar earned 300 total points
ID: 41755619
Wrap text is found at Home>Alignment>Wrap Text (see attached picture).

To enter another line in the same cell you can also use Alt/Enter.

Though this does not answer the bigger question, it may help in the interim.  I will be looking at the rest of your comments later today, and no, I did not mean anything by all of us "working at another job".  My point was only that I was sorry things had been going on so long.
WrapText.pdf
0
 
LVL 7

Expert Comment

by:tomfarrar
ID: 41755743
Here is something to think about.  See spreadsheet and instructions.
track-changes-by-adding-a-time-stamp.xls
0
 
LVL 1

Author Comment

by:Bert2005
ID: 41755794
Nice tips on the wraparound. And, I looked at the next idea. It is helpful, but I agree with you that it isn't perfect. I mean it just looks that after a while it may become cumbersome. I did notice that when you do the wraparound, and then click on the next row, it allows the row above to be the same size height as the cell you made.
0
 
LVL 1

Author Closing Comment

by:Bert2005
ID: 41755810
I think I will close it here for now. I do not see that I am making any progress for others to build on. Ironically, the best solution was the one that pointed out that the cell would have to get added to making the worksheet unwieldy.

I think also if "I" were designing an Excel sheet for someone who knew as much or more than I (could tweak it so it worked better), then it would be worth moving forward. Maybe after working with it some more, I can open it as a new question with a pointer. Thank you everyone.
0
 
LVL 1

Author Comment

by:Bert2005
ID: 41755811
@xtermie,

Thanks for your comment. When I opened the Excel spreadsheet, it didn't seem to have the pink cells. Maybe I was doing something wrong.
0
 
LVL 7

Expert Comment

by:tomfarrar
ID: 41757188
Doc - One more thought the next time you post a question:

If there are going to be many comments for one referral, you may want to consider separating the comments from the initial referral record.  This is a one-to-many relationship .  is common in many database applications.  The could carry an id that ties them back to the original reference.  

This may seem to complicate your original spreadsheet, but in the end, if done right, could make the process work much better.  The new process could be handled in an Access DB, or possible with Excel's PowerPivot.  Just keep the thought..  You may end up with a tool that could be used by other doctors.  Which, by the way, what are other doctors doing?  

I'm sure I will run across you again sometime.  Keep asking the questions.  - Tom
0
 
LVL 1

Author Comment

by:Bert2005
ID: 41757298
I actually kinda thought of that. I guess not at the level you talk about. I was thinking about other sheets (tabs) or whatever.

I don't know what other doctors do. Some may have a referral specialist that is very OCD and doesn't miss stuff. Or creates her own way of doing things. Some may have Electronic Medical Records that is much more versatile.

Some may not care as much about it as I do.

Or all three, lol.  Thanks very much for your help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now