HealthCare Conundrum Refreshing Spreadsheet

Newsbee

New Member
Joined
Feb 17, 2013
Messages
8
Hello Everyone,

Long time reader. I love the site. I work at a hospital and have written a spreadsheet to bring up pending lab tests not done on our patients. The spreadsheet connects to our servers through an ODBC and loads the pending tests. It looks loosely like this

Accession Number Container Number Name Test Time Location OUR TIME
W3432 L232656452 Mr. Burns K 1000 CP !!!!

Obviously there is more than just Mr. Burns. I have written a program to update every five minutes. It erases all the data except the headers, loads the patients again, and then does some formatting (column width, etc).

I have 2 problems.

(1) The time (1000) is not representative of the time that we received the sample. I need to add a function that brings up the time that the spreadsheet currently has that populates under OUR TIME. I was thinking =Now(). But how would I have that for every sample in the list since every time the list updates the number changes?

(2) This is the real tricky part. Every time the connection refreshes, it deletes everything then starts over. The OUR TIME would constantly change to whatever time the spread sheet updated. Here is what I need, something like this:

The coding that fines the last line of samples and updates at the first blank line afterwards.
Next the code compares between the original samples that were there and the update to see if there are duplicates and if there are it deletes the duplicate with newest time and keeps the oldest because the oldest is the true time.

All this isn't bad but here is the real issue. We know the sample is done when it disappears AFTER an update. If we refresh WITHOUT deleting everything first, how will we know when the sample is done? I am thinking somehow writing a code that says that if the original set of samples doesn't find a match with the update then delete the original. This would work well if there was a code that would effectively say if columns A through L match (and the OUR TIME is in M) between rows then delete the row with the later time of row M, but if there is no match delete the original row.

I hope this makes sense. Please feel free to ask if you have any questions. If you every wanted to write a code that will truly effect peoples lives this is it. This will help us immensely in providing patient care and working to save lives. I know this is one talented forum and I cant wait to see what interesting solutions pop up.

Thank you all!
Newsbee
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Watch MrExcel Video

Forum statistics

Threads
1,129,733
Messages
5,638,048
Members
417,000
Latest member
JasonWilliam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top