How can I find repeated numbers that are transposed?
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
"brokerdee" wrote in message
...
Paul,
When I did the second formula, it only showed the word "OK" in the column.
Thank you!
"PCLIVE" wrote:
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
"brokerdee" wrote in message
...
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?
|