Identify text nums in scrambled fashion

M

Max

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
 
T

T. Valko

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","")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top