Data Collation For Monthly Reporting Problem

[VinylKnights]

I work for the NHS and every month one of my tasks is to go through a pile of what is called 'thermometer surveys' there will be one filled in per day for any member of staff who administers care to a patient. Recorded on the sheet are the NHS numbers of patients. Now when I compile my (similar to) Exel report each month there is no way for me during entering the NHS numbers to see if this NHS number (patient) has already been administered care on a different day or by a different member of staff. So I am currently laboriously have 17 A4 pages as a template that I photocopy the masters of having the NHS number format first 3 figures (of 10) then I hand write each entry down and if I come across a duplicate, backtrack through my paperwork to collate that patients care record. This takes me a huge amount of time, I usually take it home to do at the weekend, but I am sure there must be a program or something I could use like scanning them then they can read and as I'm typing a number on my laptop the program will tell me if that number has already been entered that month..?

[woofgang]

If you are employed to do this by the NHS then a) you shouldn’t be taking data home to work on and b) you should be talking to your manager about this issue.....and you should NOT be putting NHS data into any machine except an NHS one.

[New Judge]

Quite right. You should not be taking NHS data home.

However, back to the problem. The best tool for this is MS-Access. You can import Excel (and Excel-type) data into a table and run a query to find duplicate entries. Dead easy.

If you don’t want to do that (or don't have Access and/or know how to use it), in Excel (proper) you can highlight duplicate values in a range by using the Conditional Format function:

Highlight the range of cells you want to test, Click “Conditional Formatting” then “Highlight Cells Rules” the “Duplicate Values”. All duplicates in your range will then be highlighted.

[VinylKnights]

Thanks anyway but I've done this months now don't think there is a way that would save any time those options probably are as time consuming as writing it all out but thanks for your time. PS if I don't work from home the work don't get done blame your government for cutting back admin staff but wanting the same work done

[woofgang]

And it will carry on being done while people are willing to do it out of hours....and make no mistake, when the poo hits the fan, it won’t be your managers who get into trouble for driving a truck through NHS data safety requirements, it will be YOU!

[VinylKnights]

I wish things were different, but I do enjoy my job and although I do not enjoy working late every night and at weekends, until things change I see no other way. Gone off-topic there, sorry

[New Judge]

I can't imagine it would be as quick to do things your way.

If it was me, I would sort the data in order of NHS number. Then carry out the process I described. All your duplicates will be highlighted together e.g.:

101
102
103
103
104
110
112
112
113 etc

and it would be simple to spot the duplicates. What do you have to do with them when you've identified them? You could use a Pivot Table to group them together. It all depends what you want to do. But to go through them manually is just a terrible waste of time. Nobody should be doing that in this day and age.

[Maydup]

Well call me naive but I am shocked that the data is at risk to this degree. Promise me at least that you store it in the cloud behind a secure password and not on your laptop or a dirty memory stick.

[VinylKnights]

The data isn't at risk I work on my work laptop signed into the NHS intranet

[New Judge]

And of course, as we all learned last week, that intranet is 100% secure!

Anyway, never mind that. What are you going to do to avoid ploughing manually through sheets of paper every month?

[237SJ]

It is common practice to enter and retrieve data through a company intranet or a company modem. There is no risk with that.

[237SJ]

New Judge - I think you are confusing the internet and the intranet. There is a difference.

[New Judge]

What happened last week then? Was patients' data (the loss of which prevented some of them being treated promptly) not on the NHS's Intranet? If not, where was it? And if it was not on the "secure" intranet, why not?

[RobNorth]

The mind boggles - this is one of the simplest tasks for a spreadsheet - it's what they do! If the data was acquired efficiently it should be a one keystroke operation completed in seconds, with reports/graphs printed or distributed automatically. At least it was for me 27 years ago......but that was in the Private sector.

[VinylKnights]

Intranet NOT internet! It would be easy if the info were on a spreadsheet but its on several hundred report sheets filled in by ward staff

[twix123]

What a dreadful waste of time and effort. The ward staff should be entering the data directly into system, not on sheets of paper which you then photocopy, and presumably subsequently securely destroy!

Whether you use either the 'Internet' or a local 'Intranet' makes no difference to a person determined to break into a system. At the turn of the millennium I worked with an 'ethical hacker' who was paid a very handsome salary to test, and improve, the security of our organisations worldwide systems. He usually managed to access secured data using methods not normally available to a general computer user, but he was determined that he could usually find a weak spot somewhere in most systems, even though they weren't connected to our system. The only secure system would be a free standing PC, with no connection to either the internet or an intranet (although this same person could also access data on laptops that were password protected, where he wasn't informed of the password!).

[Old_Geezer]

If the data isn't on a single spreadsheet or a single database then the system used should be reconsidered. Spotting duplicate numbers through seperate individual records seems a nightmare and a lack of foresite when the system was "designed". Meanwhile a search inside files for the NHS number might give a clue. Windows now seem able to do that itself from explorer.

[Old_Geezer]

Foresight :-(

[Peter Pedant]

blimey people give you answers to questions you didnt ask
you can take it home so long as it is properly secured is the advice we had ( having worked in the NHS) - they didnt have enough machines to give out - so we were allowed to give our own to the IT department to load their encrypting software.

also as far as I recollect - the office of the doodah ( information commissioner) has ruled that NHS number alone is not personal data as you would have to go to a hospital to find out whose it was and where it was - which isnt really the definition of personal.....

in Excel
you would use Find and Replace - in this case find-only
or sort A-Z or on number
or VLOOKUP......

well you did ask
Bill Gates (whom I am not a great fan of) put alot of his data base functions from his suite that no one used into Excel which everyone used. well done bill he says thro grated teeth