Question description:
This user has given permission to use the problem statement for this
blog.
I have a question regarding a function on excel, can you pleas help me with that? my question is how to remove duplicates separately for each row. I have different name of drugs for each patient. the problem is some of these cases has a same dug more than one. when i do the remove duplicate function for the column it does it for the whole column at once as i want it separately for case.
Thanks
Solved by O. J. in 33 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
29/09/2017 - 03:45
Hello there. I understand that you need help in removing duplicates, right?
User
29/09/2017 - 03:46
yes
Excelchat Expert
29/09/2017 - 03:46
Are you able to upload a sample document so I can fully analyze your file and give you the best solution?
User
29/09/2017 - 03:47
sure
User
29/09/2017 - 03:47
ill do it now
User
29/09/2017 - 03:49
can you see the sample now?
Excelchat Expert
29/09/2017 - 03:50
Yes, it's the one with #NULL!?
User
29/09/2017 - 03:50
i want to remove the duplicates from the column N
User
29/09/2017 - 03:50
the last one
Excelchat Expert
29/09/2017 - 03:50
Okay, can you give me an example of duplicates?
User
29/09/2017 - 03:50
like benzodiazepines
User
29/09/2017 - 03:51
which is repeated 2tice
Excelchat Expert
29/09/2017 - 03:51
Can you reference the cells where they show up?
Excelchat Expert
29/09/2017 - 03:51
N15 and which one?
User
29/09/2017 - 03:51
yes
User
29/09/2017 - 03:51
n15
User
29/09/2017 - 03:51
and n3
Excelchat Expert
29/09/2017 - 03:51
I see.
Excelchat Expert
29/09/2017 - 03:52
Here's what we can do, since you don't want to delete them all at the same time, what we can do is tag them if they are duplicates.
User
29/09/2017 - 03:52
i want to remove these duplicates from all my data which a around 10000 cases
Excelchat Expert
29/09/2017 - 03:53
If you'll look at column O, you'll see numbers.
Excelchat Expert
29/09/2017 - 03:53
Do you see them?
User
29/09/2017 - 03:53
yes
Excelchat Expert
29/09/2017 - 03:53
Okay, let me explain what this means.
Excelchat Expert
29/09/2017 - 03:53
EVERYTHING that isn't 0 or 1 means they are duplicates.
Excelchat Expert
29/09/2017 - 03:53
For instance, you will see the first Benzodiazepines
Excelchat Expert
29/09/2017 - 03:53
Has a value of 1
User
29/09/2017 - 03:53
yes i see
Excelchat Expert
29/09/2017 - 03:54
But then the next one, has a value of 2.
Excelchat Expert
29/09/2017 - 03:54
What you can do then is to filter everything that is greater than 1.
Excelchat Expert
29/09/2017 - 03:54
I did the filtering and got these results.
Excelchat Expert
29/09/2017 - 03:55
If you'll look at the document preview, here's all the entries that are duplicates.
User
29/09/2017 - 03:55
i see
Excelchat Expert
29/09/2017 - 03:55
Now you can do anything you want to do to them.
Excelchat Expert
29/09/2017 - 03:55
You can either delete the entire row
Excelchat Expert
29/09/2017 - 03:55
Or just those specific cells.
Excelchat Expert
29/09/2017 - 03:55
It is really up to you.
Excelchat Expert
29/09/2017 - 03:55
So just copy the formula in column O and apply it to your file up to row 1000
Excelchat Expert
29/09/2017 - 03:56
Or whatever the maximum number of records you have.
User
29/09/2017 - 03:56
but the problemm is i have 1000 od benzodiazepines, is there anyway that this function can automatically remove duplicate from each case?
Excelchat Expert
29/09/2017 - 03:56
Then you should easily be able to filter everything that is greater than 1.
Excelchat Expert
29/09/2017 - 03:57
The remove duplicate function in Excel would that but that's not the effect you want?
User
29/09/2017 - 03:57
no its not
User
29/09/2017 - 03:57
it assume the whole column as one and removes all of my cases
User
29/09/2017 - 03:57
as most of the drugs are same
User
29/09/2017 - 03:57
i have like 10 different drug
Excelchat Expert
29/09/2017 - 03:57
I'm not sure I understand.
User
29/09/2017 - 03:58
which each patients use couple of thems
Excelchat Expert
29/09/2017 - 03:58
Can you elaborate on what you meant by it assume the whole column as one and removes all of my cases
User
29/09/2017 - 03:59
i want the excel that do the duplicate function separately for each of my case. like having a different border and it only removes the similar from the box
Excelchat Expert
29/09/2017 - 03:59
You will have to give me an accurate example because I'm not able to visualize your request.
User
29/09/2017 - 03:59
not from the whole column
Excelchat Expert
29/09/2017 - 04:00
I don't see borders or each case in your example.
User
29/09/2017 - 04:00
thats the problem, let me draw a border then i can explain it to you, can I text you in 5 mins
User
29/09/2017 - 04:00
?
Excelchat Expert
29/09/2017 - 04:01
I'm afraid we dont' have that much time. :(
Excelchat Expert
29/09/2017 - 04:01
I would suggest ending the session for now and prepare the information.
Excelchat Expert
29/09/2017 - 04:01
Make sure you have all the information relevant so we can create a custom solution for you.
User
29/09/2017 - 04:01
look i column
Excelchat Expert
29/09/2017 - 04:01
Once you are able to show us what exactly you need, then that's when we'll be able to assist you.
User
29/09/2017 - 04:02
i want the excel to the job that i m going to do
Excelchat Expert
29/09/2017 - 04:02
I'm looking at the I column.
Excelchat Expert
29/09/2017 - 04:02
A gentle reminder that since you've extended the session: Please ensure that you end the session properly later or else I'll be stuck here waiting for the timer to expire and I'll be unable to help others.
User
29/09/2017 - 04:03
i removed the prescription and benzodazepines
User
29/09/2017 - 04:03
thanks a lot
Excelchat Expert
29/09/2017 - 04:03
I can see that you deleted N15
Excelchat Expert
29/09/2017 - 04:03
What else did you do?
User
29/09/2017 - 04:03
i removed to duplicates
User
29/09/2017 - 04:03
manually
User
29/09/2017 - 04:04
2
Excelchat Expert
29/09/2017 - 04:04
How will Excel know that something is part of a group?
Excelchat Expert
29/09/2017 - 04:05
Is it thru column A?
User
29/09/2017 - 04:05
thats the problem, i want to separate the column for each of my cases
User
29/09/2017 - 04:05
yes
Excelchat Expert
29/09/2017 - 04:05
If that's the case then you can do it if you have column A filled.
Excelchat Expert
29/09/2017 - 04:05
Like this:
User
29/09/2017 - 04:05
how?
Excelchat Expert
29/09/2017 - 04:08
Using the formula in column O and P, you'll be able to detect the duplicates in each Case.
User
29/09/2017 - 04:08
how? can you explain it please?
Excelchat Expert
29/09/2017 - 04:08
If you'll look line 41, it should've been a duplicate earlier, but since it is a different case, it was not counted as a duplicate.
Excelchat Expert
29/09/2017 - 04:09
Caffeine & Theobromine already existed above for case 15-11510 but since row 41 has a different case number, it was not counted as a duplicate.
Excelchat Expert
29/09/2017 - 04:09
With this, you can just filter COlumn P for values >1
User
29/09/2017 - 04:10
so it just checks the duplicate for each case?
Excelchat Expert
29/09/2017 - 04:10
Yes, exactly!
User
29/09/2017 - 04:10
how did you transfer the text to the numbers? sorry i didn't get it?
Excelchat Expert
29/09/2017 - 04:11
I used a concatenate.
Excelchat Expert
29/09/2017 - 04:11
A3&N3
Excelchat Expert
29/09/2017 - 04:11
The & symbol combines the values of cells.
User
29/09/2017 - 04:12
im a bit lost, did realize how you did it
Excelchat Expert
29/09/2017 - 04:12
Okay, here are the steps.
Excelchat Expert
29/09/2017 - 04:12
First, column A must be filled.
Excelchat Expert
29/09/2017 - 04:12
Second, apply this formula in column O
Excelchat Expert
29/09/2017 - 04:12
=if(N3="","",A3&N3)
Excelchat Expert
29/09/2017 - 04:12
Then drag downwards to apply to everything
Excelchat Expert
29/09/2017 - 04:13
Then apply this formula in column P:
Excelchat Expert
29/09/2017 - 04:13
=if(O3="",0, countif($O$3:O3,O3))
Excelchat Expert
29/09/2017 - 04:13
And drag downwards too.
Excelchat Expert
29/09/2017 - 04:13
Column P will give you numbers, those number mean if they are duplicates in the same case number.
Excelchat Expert
29/09/2017 - 04:13
You are just concerned about anything that is >1
Excelchat Expert
29/09/2017 - 04:13
So filter column P and uncheck all 0s and 1s.
User
29/09/2017 - 04:16
so thats it? anything else that i need to do?
Excelchat Expert
29/09/2017 - 04:16
That's all you need to do.
Excelchat Expert
29/09/2017 - 04:16
https://docs.google.com/spreadsheets/d/1cB9q4IyOghUXKWD0SGxxj-bPi8J8LKtOHhgf69PNTGc/edit#gid=0
Excelchat Expert
29/09/2017 - 04:16
You can download the sheet by going to the link above, clicking FILE > DOWNLOAD AS > Microsoft Excel (.xlsx)
User
29/09/2017 - 04:16
thats great, that was a huge helming, don't know how to thanks
Excelchat Expert
29/09/2017 - 04:16
Make sure you download the file so you can reference it.
Excelchat Expert
29/09/2017 - 04:17
In case you encounter problems applying it, don't hesitate to contact us again so we can help you.
Excelchat Expert
29/09/2017 - 04:17
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating if you think I deserve it. :)
Excelchat Expert
29/09/2017 - 04:17
Please do not forget to click the End Session otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
User
29/09/2017 - 04:17
that was a 10 star for me
User
29/09/2017 - 04:18
appreciate it a lot
Excelchat Expert
29/09/2017 - 04:18
I'd appreciate that. :)
User
29/09/2017 - 04:18
have a nice day
Excelchat Expert
29/09/2017 - 04:18
Thank you for contacting Got It Pro. Have an awesome day!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
