Question description:
This user has given permission to use the problem statement for this
blog.
I have 2 cells containing a % value. The value for both is 9%.
I want to conditionally format these cells as duplicate values but excel only highlights them as duplicate values if the decimal point is exactly the same. How can i correct this?
Solved by S. C. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
24/05/2018 - 11:54
Hello
User
24/05/2018 - 11:54
hi
Excelchat Expert
24/05/2018 - 11:54
Welcome to excelchat.
Excelchat Expert
24/05/2018 - 11:55
I want to understand the problem clearly.
User
24/05/2018 - 11:55
ok
Excelchat Expert
24/05/2018 - 11:55
Can you please show me the exact values in those cells?
User
24/05/2018 - 11:56
the values in the cells are 8.9% and 9.1%
Excelchat Expert
24/05/2018 - 11:57
And you want those to treat as duplicate even though there are not exactly the same?
User
24/05/2018 - 11:57
yes
Excelchat Expert
24/05/2018 - 11:58
This seems a bit confusing, please tell me more about what you are trying to do and what you have.
User
24/05/2018 - 11:59
I have 2 columns of values in percentages
Excelchat Expert
24/05/2018 - 11:59
It would be better if you can show me the file too.
User
24/05/2018 - 11:59
now and again the same value appears in both columns - for example : 9%
User
24/05/2018 - 12:00
I want excel to highlight these values as duplicates but excel reads to the decimal place and won't highlight them as duplicates because the decimal point is different - eg: 8.9% and 9.1%
Excelchat Expert
24/05/2018 - 12:01
I understand.
User
24/05/2018 - 12:01
goog
User
24/05/2018 - 12:01
good
Excelchat Expert
24/05/2018 - 12:01
And I need to see all values to suggest a solution.
Excelchat Expert
24/05/2018 - 12:02
Can you please send me the file?
User
24/05/2018 - 12:02
can I type out a typical example for you?
Excelchat Expert
24/05/2018 - 12:03
I would need more than a few examples.
User
24/05/2018 - 12:04
I thought there would be quite an easy solution such as telling excel to ignore the decimal points?
Excelchat Expert
24/05/2018 - 12:05
Okay, in the given example, you said the numbers are 8.9 and 9.1 So ignoring decimals would give you 8 and 9 respectively.
Excelchat Expert
24/05/2018 - 12:06
INT function tells excel to ignore decimals. But if we use that in this case, you will get 8 and 9 which are not the same.
User
24/05/2018 - 12:06
Dosen't excel round up to the nearest whole number 8.9 = 9 and 9.1 = 9
Excelchat Expert
24/05/2018 - 12:06
And therefore won't be identified as duplicates.
User
24/05/2018 - 12:06
OK
User
24/05/2018 - 12:07
so is there no other way?
Excelchat Expert
24/05/2018 - 12:07
The automatic rounding in excel only shows you the rounded values but the exact values remain same.
Excelchat Expert
24/05/2018 - 12:07
Yeah there is, that is why I need to see some example.
Excelchat Expert
24/05/2018 - 12:08
I'm again saying it would be better if I can see the file itself.
Excelchat Expert
24/05/2018 - 12:08
making sense?
Excelchat Expert
24/05/2018 - 12:08
If you are sensitive about your data, I do understand that.
User
24/05/2018 - 12:09
yes but you could easily create my scenario without me sending you the file - its confidential
Excelchat Expert
24/05/2018 - 12:10
I understand your feeling and that you data is confidential. Although I can work without the original data, it would require more time and in some cases the solution wouldn't be ideal or efficient.
User
24/05/2018 - 12:12
Its probably more complicated than i thought it would be so I'm not sure it is worth finding a solution - I thought I may be missing something obvious
Excelchat Expert
24/05/2018 - 12:12
Please first try this formula in your cell
Excelchat Expert
24/05/2018 - 12:12
=ROUND(A1,0)]
Excelchat Expert
24/05/2018 - 12:13
=ROUND(A1,0) this will round numbers to nearest whole number.
Excelchat Expert
24/05/2018 - 12:13
And eliminate decimals.
User
24/05/2018 - 12:13
that sounds about right
Excelchat Expert
24/05/2018 - 12:13
Of course you have to change the A1 in the formula accordingly your file.
Excelchat Expert
24/05/2018 - 12:14
And after that you can try the conditional formatting.
Excelchat Expert
24/05/2018 - 12:14
Let me know whether that works or not.
User
24/05/2018 - 12:14
what do I put in, instead of A1?
Excelchat Expert
24/05/2018 - 12:15
The cell reference of the value you want to round to nearest whole number.
User
24/05/2018 - 12:15
I see
Excelchat Expert
24/05/2018 - 12:15
Okay. Let me know if you have anymore questions.
User
24/05/2018 - 12:16
that formula you have quoted is similar to other suggestions I have seen when trying to find a solution to this
User
24/05/2018 - 12:16
Thanks for your help
Excelchat Expert
24/05/2018 - 12:16
This resolves your issue?
User
24/05/2018 - 12:17
Its the best one I've come across - will try it later
Excelchat Expert
24/05/2018 - 12:17
Sounds good.
User
24/05/2018 - 12:17
does this service cost anything?
Excelchat Expert
24/05/2018 - 12:17
if you have any confusion, you can ask me.
Excelchat Expert
24/05/2018 - 12:18
Yeah, we do charge by session. The updated pricing is listed on the website.
User
24/05/2018 - 12:18
my first time here - is this one free?
Excelchat Expert
24/05/2018 - 12:19
I thing so, but if it were to cost you money then you already would have asked to enter your payment details.
User
24/05/2018 - 12:20
well thanks again
Excelchat Expert
24/05/2018 - 12:20
As an expert, my task is to solve user problem. We experts don't always have the latest pricing and others things.
Excelchat Expert
24/05/2018 - 12:21
Thank you for trying out got it pro excel. Have a great day ahead!
User
24/05/2018 - 12:21
you yoo.
User
24/05/2018 - 12:21
you too
Excelchat Expert
24/05/2018 - 12:24
The session will end in 10 minutes unless you opt to end it before. Thanks again for visiting us today.
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.