How can I find repeated numbers that are transposed?

B

brokerdee

I have a row with numbers like this:
123
456
231
794
344
645
How can I find the numbers that are repeated though their digits are
transposed?
 
P

PCLIVE

One possible way:

Use a helper column.
=LEFT(A1,1)+MID(A1,2,1)+RIGHT(A1,1)

Then in another column use:
=IF(AND(COUNTIF(B$1:B$6,B1)>1,COUNTIF(A$1:A$6,A1)=1),"Possibly
Transposed","OK")

HTH,
Paul
 
P

PCLIVE

The way this works.
In Column A, you have your values:
123
456
231
794
344
645

In Column B, you have the first formula. This formula should be copied down
as necessary for the number of rows in use.
=LEFT(A1,1)+MID(A1,2,1)+RIGHT(A1,1)

Then in Column C, you have this formula copied down as necessary.:
=IF(AND(COUNTIF(B$1:B$6,B1)>1,COUNTIF(A$1:A$6,A1)=1),"Possible
Transposed","OK")

The results should be "OK" if no possible transposed duplicate is found.
The results I came up with were as follows.


123 6 Possible Transposed
456 15 Possible Transposed
231 6 Possible Transposed
794 20 OK
344 11 OK
645 15 Possible Transposed


If you're still having a problem, please post details.
Regards,
Paul
 
Top