Trying to clean up 3 digit number duplicates

[Vronzulli]

Here is my dilema. I have three digit numbers starting with 000 to 999 and I would like to eliminate from my list any 3 digit numbers that are duplicates. Let me explain further, if I have the number 123, 321 and 213 (or any other combination) since the three digits are the same I would like to flag them or eliminate them without having to do it manually. I've tried several functions and formulas without success and I don't want to program it in VB since I think that would be an overkill. I look forward to any answers. Thanks

 

[vletm]

Vronzulli
Have You tested:
1) Conditional formatting
2) Advance filter > Unique records only

 

[John Jairo V]

Hi, @Vronzulli!

If you have the numbers in text format in column A, you could try in B column this formula:
=SUM(SMALL(--MID(A1,{1;2;3},1),{1;2;3})*10^{2;1;0})

Next, select all numbers, and:

1. If you want to flag them, Go to Home Tab, Conditional Formatting, Highlight Cell Rules, Duplicate Values.
2. If you want to remove duplicates, Go to Data Tab, Remove Duplicates, and just leave mark in Column B.

Check file with the formula applied. That's all! Blessings!

 

[Vronzulli]

I am going to try it and let you know ! Thanks

 

[Vronzulli]

John, I saw your spreadsheet example, which by the way, Thank you, but that is not what I am trying to do. Here is a better explanation. I have 1000 3 digit numbers starting with 000 to 999. I need to compare numbers on say cell A1 to a number on cell A2 and if the numbers (digits) are the same, regardless of the order of each digit, then I need to flag it. So for example, if cell A1 has the number 123 and cell A2 has the number 312, that number would be flagged or eliminated since all the digits are the same but in different order position. so if I have the number 123 on cell A1, I need to eliminate or flag all other cells that contain those three numbers like 132, 231, 213, 312, 321 since all of those combinations are emcompassed on the number 123. Hope that explains it better. here is another example, say I have the number 717, I would flag or eliminate the following two numbers 771, 177 since they are the same DIGITS. I don't need to eliminate the digit but rather the whole number which contains the same digits. Thanks for all your help.

 

[John Jairo V]

Hi again, Vronzulli!

Upload a sample excel file with your data, and what do you want to achieve. I think the file upload is that you want... Blessings!

 

[vletm]

Vronzulli
You wrote: I don't want to program it in VB since I think that would be an overkill.
...
but
if someone else would do it?
and
how would You think something like 'an overkill'?

 

[Vronzulli]

OK, here is the file. Explanation on sheet and number sample highlighted.

 

[Vronzulli]

What I meant by an Overkill is that I would have to set up VB, variables, etc. for something I need to do once. Once the filter is applied I would not have to do that ever again.

 

[John Jairo V]

Hi, again!

I leave two options in attach file. The dupplicates are flagged in red (conditional formatting). Blessings!

 

[vletm]

Vronzulli
... as I wrote ... if someone else would do it?
If You'll have same layout, then what would You need to do?

I did Sheet1-version before Your file (different layouts).
In both cases, You'll get uniques and right side those 'doubles'.

 

[Vronzulli]

That's a way to solve it ! Thanks, that would do it, now I can eliminate the ones I don't need as they are next to each other ! AWESOME. Thanks agaian