Hello,
I have a horizontal rows of 5 numbers and I want to find whether there are any duplicate numbers in the rows and if all 10 numbers have appeared before in another row regardless of which position they are in.
For example:
1 2 3 4 5
2 3 4 1 5
I would want it to show that this is a duplicate.
And example:
1 2 3 4 5
2 0 9 4 8
I would want it to show that numbers 2 and 4 are duplicates.
Many thanks,
Cheers!
Highlight them all, go to conditional formatting, highlight cells, duplicate values.
From there you can customise your end result with colours, font types etc.
hope this helps
Thanks for you reply!
But is there a way to show that it's a duplicate in another cell? I don't want it all highlighted.
Also, I want it to show not just 1 number duplicate but a minimum of 2 numbers in the row duplicated regardless of which column they are in.
Could you please provide a sample workbook? It's hard to visualise exactly what you want.
1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired results are also shown (mock up the results manually).
3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).
4. Try to avoid using merged cells as they cause lots of problems.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Suppose your data in "A2" to "E3"
In "G2"Formula:
Please Login or Register to view this content.
copy paste across.
I think this requires you.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
I don't think it does, AVK. The OP seems to be asking for the cells to be highlighted. Let's wait for the sample workbook I've requested - it may be conditional formatting that is required.
Thanks for the advice!
I have attached the sample workbook~
When I say 2+ same numbers in a row I mean horizontally not one after the other.
Last edited by charmaine143; 07-20-2018 at 05:33 AM.
Your sample data is incorrect - you have highlighted 41 instead of 36 - but it shows us what you are after - thank you.
Yes, thanks for your correction.
Why does 7 not count as a duplicate?
=COUNTIF($B$6:$B$15,B6)>1 used for conditional formatting.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Because I only want the duplicates horizontally by rows.
So 7 would only be a duplicate if for example #22 was also in row 6 making row 5 and 6 both duplicates.
Sorry if I explained it poorly.
You've lost me!!! I don't see why 10 & 36 are duplicates and 7 isn't...
Because it needs to be 2 or more numbers the same horizontally to be duplicate.
OK. Understand now...
can anyone help me please?
Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks