Identify text nums in scrambled fashion
Try this...
Assuming there are no empty cells within the range A1:A3.
In the formula, Nums refers to the range A$1:A$3.
Entered in D1 and copied down as needed:
=IF(FREQUENCY(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(Nums,MID(C1,1,1),"",1),MID(C1,2,1),"", 1),MID(C1,3,1),"",1),MID(C1,4,1),"",1)),0),"x","")
--
Biff
Microsoft Excel MVP
"Max" wrote in message
...
All nums are text nums
In A1:A3 would be 3 "winning" 4-digit text numbers, eg:
0044
1234
4233
In C1 down will be input 4 digit text nums such as:
0440
4120
2343
3324
etc
If the text nums in C1 down happen to contain the same 4 digits as in any
of the 3 winners in A1:A3, then to indicate an "x" in adjacent col D
(otherwise just leave it blank)
For the sample data, the results in col D would be:
0440 - x
4120
2343 - x
3324 - x
I'm game for any formula, udf or vba solution which can do the above
Insights welcomed. Thanks
|