If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Identify text nums in scrambled fashion
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
Identify text nums in scrambled fashion
Brilliant, Biff. Works great
Many thanks |
#4
|
|||
|
|||
Identify text nums in scrambled fashion
You're welcome, Max.
Thanks for the feedback! -- Biff Microsoft Excel MVP "Max" wrote in message ... Brilliant, Biff. Works great Many thanks |
Thread Tools | |
Display Modes | |
|
|