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?

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?

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?

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?

Thank you, I had transposed numbers when writing the formula.

"PCLIVE" wrote:

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?