Need help- Find Function with Multiple criteria

[Mallappa]

Hi everyone,

I need your help for Find Function with Multiple criterias. Ex-

ADC XE AD ADKO R Ad Know Op
ADC XE AD ADKO X Ad Know
ADC XE AD ADKOAd Know
KNE XE KN ACDA

If this is my data in Column A, I need to find the names which contains " R " and " X " in 16th digit. I can do it using find formula twice but is there a way to do it in on shot..?

The formula I'm using is =FIND(" R ",A1,16) it gives me numbers like 16, 17, 19, 20 etc but I'm interested only in 16 digit so I just filter for 16 and copy the data to seperate tab.

Waiting for you ans​

 

[Mallappa]

Re: Need help to Combine a formula

=IF(SEARCH("X ",$B3,16)=16,"True",IF(SEARCH("R ",$B3,16)=16,"True","False"))

This formula checks only first condition i.e. "X " it ignores "R ".

 

[Mallappa]

Re: Need help to Combine a formula

=IF(SEARCH("X ",$B3,16)=16,"True",IF(SEARCH("R ",$B3,16)=16,"True","False"))

This formula checks only first condition i.e. "X " it ignores second condition "R "

 

[texasalynn]

Re: Need help to Combine a formula

yes that is correct because they can't both be true. You can only have one of those, R or X, in the 16th placement

 

[Mallappa]

Re: Need help to Combine a formula

yes that is correct because they can't both be true. You can only have one of those, R or X, in the 16th placement

Ya but I meant it even ignores if there is "R " in the 16th place. Wen "X " is not it should check for other condition which is not happening..!

 

[Mallappa]

No, it is case sensitive.

As written the formula must find uppercase " R " or " X ".

Sheet1

	A	B

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 237px"><COL style="WIDTH: 72px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]ADC XE AD ADKO r Ad Know Op[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: left"]ADC XE AD ADKO R Ad Know Op[/TD]
[TD="align: right"]TRUE[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: left"]ADC XE AD ADKO x Ad Know[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: left"]ADC XE AD ADKO X Ad Know[/TD]
[TD="align: right"]TRUE[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: left"]ADC XE AD ADKOAd Know[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: left"]KNE XE KN ACDA[/TD]
[TD="align: right"]FALSE[/TD]

</TBODY>

This formula entered in B2 and copied down:

=OR(EXACT(MID(A2,15,3),{" R "," X "}))

If that's not what you want then we need to see more examples and tell us what results you expect.

[TABLE="width: 349"]
<TBODY>[TR]
[TD]Names</SPAN>[/TD]
[TD]Formula</SPAN>[/TD]
[/TR]
[TR]
[TD]AD AD ADKO I Acquired Content</SPAN>[/TD]
[TD]TRUE</SPAN>[/TD]
[/TR]
[TR]
[TD]SCS XE RE RE__ I Knowledge</SPAN>[/TD]
[TD]TRUE</SPAN>[/TD]
[/TR]
[TR]
[TD]SCS XE SC SECO I Knowledge</SPAN>[/TD]
[TD]TRUE</SPAN>[/TD]
[/TR]
[TR]
[TD]KNC XE IC CMIO I StrtIntCntPrj</SPAN>[/TD]
[TD]TRUE</SPAN>[/TD]
[/TR]
[TR]
[TD]SCM XE TH TECH I Knowledge</SPAN>[/TD]
[TD]TRUE</SPAN>[/TD]
[/TR]
[TR]
[TD]KNC XE KN DDOP I Digital Deply</SPAN>[/TD]
[TD]TRUE</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]

I'm gettting result as True for these names as well. So I'm not getting how it works..!

 

[T. Valko]

[TABLE="width: 349"]
<TBODY>[TR]
[TD]Names</SPAN>
[/TD]
[TD]Formula</SPAN>
[/TD]
[/TR]
[TR]
[TD]AD AD ADKO I Acquired Content</SPAN>
[/TD]
[TD]TRUE</SPAN>
[/TD]
[/TR]
[TR]
[TD]SCS XE RE RE__ I Knowledge</SPAN>
[/TD]
[TD]TRUE</SPAN>
[/TD]
[/TR]
[TR]
[TD]SCS XE SC SECO I Knowledge</SPAN>
[/TD]
[TD]TRUE</SPAN>
[/TD]
[/TR]
[TR]
[TD]KNC XE IC CMIO I StrtIntCntPrj</SPAN>
[/TD]
[TD]TRUE</SPAN>
[/TD]
[/TR]
[TR]
[TD]SCM XE TH TECH I Knowledge</SPAN>
[/TD]
[TD]TRUE</SPAN>
[/TD]
[/TR]
[TR]
[TD]KNC XE KN DDOP I Digital Deply</SPAN>
[/TD]
[TD]TRUE</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

I'm gettting result as True for these names as well. So I'm not getting how it works..!

Here are the results I get:

Book1
	A	B
2	AD AD ADKO I Acquired Content	FALSE
3	SCS XE RE RE__ I Knowledge	FALSE
4	SCS XE SC SECO I Knowledge	FALSE
5	KNC XE IC CMIO I StrtIntCntPrj	FALSE
6	SCM XE TH TECH I Knowledge	FALSE
7	KNC XE KN DDOP I Digital Deply	FALSE
8	ADC XE AD ADKO R Ad Know Op	TRUE
9	ADC XE AD ADKO x Ad Know	FALSE
10	ADC XE AD ADKO X Ad Know	TRUE
11	ADC XE AD ADKOAd Know	FALSE
12	KNE XE KN ACDA	FALSE
Sheet1

 

[Mallappa]

Here are the results I get:

Sheet1

*	A	B

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 223px"><COL style="WIDTH: 72px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]AD AD ADKO I Acquired Content[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: left"]SCS XE RE RE__ I Knowledge[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: left"]SCS XE SC SECO I Knowledge[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: left"]KNC XE IC CMIO I StrtIntCntPrj[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: left"]SCM XE TH TECH I Knowledge[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: left"]KNC XE KN DDOP I Digital Deply[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: left"]ADC XE AD ADKO R Ad Know Op[/TD]
[TD="align: right"]TRUE[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: left"]ADC XE AD ADKO x Ad Know[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: left"]ADC XE AD ADKO X Ad Know[/TD]
[TD="align: right"]TRUE[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: left"]ADC XE AD ADKOAd Know[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: left"]KNE XE KN ACDA[/TD]
[TD="align: right"]FALSE[/TD]

</TBODY>

Hey it works great. I think earlier i would have messed up with formula. Thank you very very much. (But can we add one more condition to it like names wich contains SMR in it (any place) need to be determined. It would be great if that can be done as well. But even as if now it saves lot of time.

Thank you very much for you help.

This is the data for reference
ASL XE AT ASLRIFRSEUDevSMR
CFT XE CF FLXCFlexCompSMR

Thanks again..!

 

[T. Valko]

Hey it works great. I think earlier i would have messed up with formula. Thank you very very much. (But can we add one more condition to it like names wich contains SMR in it (any place) need to be determined. It would be great if that can be done as well. But even as if now it saves lot of time.

Thank you very much for you help.

This is the data for reference
ASL XE AT ASLRIFRSEUDevSMR
CFT XE CF FLXCFlexCompSMR

Thanks again..!

Is that an additional condition or is it a separate condition?

Does the case matter? Find exactly uppercase "SMR" or will any case be OK?

 

[Mallappa]

Is that an additional condition or is it a separate condition?

Does the case matter? Find exactly uppercase "SMR" or will any case be OK?

I need names which satisfies either of the conditions
1. 16th digit " X " or " R " (Wich is solved).
2. Or which contains SMR (Not case sensitive) in it.

U can say its just an additional condition. But I need it in just one formula as if either of the one condition satisfies I need result as true or something which can differentiante between true and false.

 

[T. Valko]

I need names which satisfies either of the conditions
1. 16th digit " X " or " R " (Wich is solved).
2. Or which contains SMR (Not case sensitive) in it.

U can say its just an additional condition. But I need it in just one formula as if either of the one condition satisfies I need result as true or something which can differentiante between true and false.

Try this...

Book1
	A	B
2	AD AD ADKO I Acquired Content	FALSE
3	SCS XE RE RE__ I Knowledge	FALSE
4	SCS XE SC SECO I Knowledge	FALSE
5	KNC XE IC CMIO I StrtIntCntPrj	FALSE
6	SCM XE TH TECH I Knowledge	FALSE
7	KNC XE KN DDOP I Digital Deply	FALSE
8	ADC XE AD ADKO R Ad Know Op	TRUE
9	ADC XE AD ADKO x Ad Know	FALSE
10	ADC XE AD ADKO X Ad Know	TRUE
11	ADC XE AD ADKOAd Know	FALSE
12	KNE XE KN ACDA	FALSE
13	ASL XE AT ASLRIFRSEUDevSMR	TRUE
14	CFT XE CF FLXCFlexsmrComp	TRUE
Sheet1

This formula entered in B2 and copied down:

=OR(EXACT(MID(A2,15,3),{" R "," X "}),COUNT(SEARCH("SMR",A2)))