+- JapCarForum (https://www.japcarforum.com)
+-- Forum: General Discussions (https://www.japcarforum.com/forumdisplay.php?fid=3)
+--- Forum: The JCF Lounge (https://www.japcarforum.com/forumdisplay.php?fid=17)
+--- Thread: Excel experts? (/showthread.php?tid=3148)
Excel experts? - parthiban - 16-01-2018
Are you basically saying you have:
1
2
3
1
3
4
2
3
1
4
and you want in another column:
1
2
3
4
???????
If so, just copy the whole column to the new location, highlight it, then click "Data" and "Remove duplicates"
Let me know if that doesn't work - also let me know if you don't have excel 2007 as I remember it may have been a bit different in 2003 but can't remember exactly.
Excel experts? - Saraj - 16-01-2018
parthiban Are you basically saying you have:
1
2
3
1
3
4
2
3
1
4
and you want in another column:
1
2
3
4
???????
If so, just copy the whole column to the new location, highlight it, then click "Data" and "Remove duplicates"
Let me know if that doesn't work - also let me know if you don't have excel 2007 as I remember it may have been a bit different in 2003 but can't remember exactly.
I am using 2007 here but also using 2003 at work which confuses things lol
Removing duplicates is not a problem, however its the duplicates which I need to highlight.
Basically the spreadsheet will show the **** ups by Nurses when they havnt inputted patient details on the system. I want to highlight how many times the same nurse has done it, and the numbers would represent they Operator ID Numbers.
So if Operator ID 1, had three mistakes over a period of a week, those mistakes could be anywhere in the list which could have upto 500 rows.
I want it to pick out all the 1's and copy it across to a another sheet, so I know that operator has made the mistake more then once.
If i could get it to work that it only picks out data if the same number appears equal to 3 or more times, then would be excellent as 3 times in a week is the threshold. Then I would be using the duplicate data to procress a mail merge to send them all out a letter.
Hope that makes sense, thanks for the assistance in advance
Excel experts? - parthiban - 16-01-2018
Sorry I'm having trouble understanding what's required (head's in weekend mode)
Ok, so you've got a list of data, and some people are entering the same data a number of times which you want to highlight. Now do you need all the data in this new spreadsheet, or just the operator numbers of those that have entered incorrect data more than three times?
Feel free to PM me if any info you provide is sensitive, I'm pretty sure I can help as I use excel a lot but I'm just being dumb and can't quite figure out what you're trying to do
Excel experts? - Saraj - 16-01-2018
parthiban Sorry I'm having trouble understanding what's required (head's in weekend mode)
Ok, so you've got a list of data, and some people are entering the same data a number of times which you want to highlight. Now do you need all the data in this new spreadsheet, or just the operator numbers of those that have entered incorrect data more than three times?
Feel free to PM me if any info you provide is sensitive, I'm pretty sure I can help as I use excel a lot but I'm just being dumb and can't quite figure out what you're trying to do
those who have entered the numbers incorrectly more then three times would be perfect. just that info on the new sheet, so then i can mail merge and send them letter reminding them that they need to input the relevant details and three mistakes in one week is unacceptable.
Excel experts? - Moley - 16-01-2018
Saraj three mistakes in one week is unacceptable.
I think you are a bit of a Bully to your work colleges:Bazza::Bazza::LOCMOD:
What happens when you make more than 3 mistakes in a week :Chair::Chair:
Excel experts? - parthiban - 16-01-2018
Saraj those who have entered the numbers incorrectly more then three times would be perfect. just that info on the new sheet, so then i can mail merge and send them letter reminding them that they need to input the relevant details and three mistakes in one week is unacceptable.
A very basic solution is to use the advanced filter to create a list of all the unique operator numbers, and then use the following formula:
=IF(COUNTIF(A1:A22,F5)>=3,1,0) where A1:A22 is the original list
This will give a 1 for all the op numbers that occur 3 or more times in your list, so you could filter all the "1"s and pick them off.
I'm sure there's an even slicker way to do this though but I can't think of it at the mo, sorry. Will let you know if it comes to me but what I've said above will do the required job quite simply.
Excel experts? - Saraj - 16-01-2018
Moley I think you are a bit of a Bully to your work colleges:Bazza::Bazza::LOCMOD:
What happens when you make more than 3 mistakes in a week :Chair::Chair:
more then 3 times they get the same letter, i found one nurse who did it 11 times in one week, inputted the data matters, as if the wrong data is inputted or put in wrong, then the wrong results go to the wrong patients, which we all know can have severe consenquences
parthiban A very basic solution is to use the advanced filter to create a list of all the unique operator numbers, and then use the following formula:
=IF(COUNTIF(A1:A22,F5)>=3,1,0) where A1:A22 is the original list
This will give a 1 for all the op numbers that occur 3 or more times in your list, so you could filter all the "1"s and pick them off.
I'm sure there's an even slicker way to do this though but I can't think of it at the mo, sorry. Will let you know if it comes to me but what I've said above will do the required job quite simply.
excellent, I will give this a go, as soon as my head stop aching from last night trying to work it out ! and will feedback.
I am doing it all manually at the moment, which I dont mind, however I have to show others how to use it, so if I can make it slicker, then it would be harder for them to break .:w00t:
Excel experts? - Moley - 16-01-2018
Only pulling your leg mate :tongue_smilie:
Hope it works out, and then you'll be the teacher's pet :laugh:
Excel experts? - Saraj - 16-01-2018
Moley Only pulling your leg mate :tongue_smilie:
Hope it works out, and then you'll be the teacher's pet :laugh:
yh lol, im still in probation, ive already changed three processes which had been there for years, cause i didint like it, this is another i dont, but that little bit more complicating to fix / improve.
They increased my hours from 15 to 20 within 1 month of joining, continue being teachers pet you never know there may increase it more which would be ideal
Excel experts? - FirebirdPhil - 16-01-2018
Yea you may well be asked to stay behind with teacher after hours:blink: