Search for missing characters

K

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
Using Excel 2000</SPAN></SPAN>
Hi,</SPAN></SPAN>

In the cells C6:F19 each row is filled by 3 characters 1, X or by 2, I need a formula, which can search each row and give a missing character result in the column H Note: if non of 1, X and 2 is missing I want it return "Nothing"</SPAN></SPAN>

Please see the example below</SPAN></SPAN>


Book1
ABCDEFGHIJ
1
2
3
4
5P1P2P3P4Not Found
61121X
71111X2
8XXXX12
91X12Nothing
1022221X
11X2X21
122X11Nothing
13X1XX2
141XX12
15X2X1Nothing
161X12Nothing
171212X
181XX12
191XX12
20
21
22
Sheet1


Thank you in advance</SPAN></SPAN>

Regards,</SPAN>
Kishan</SPAN>
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Try

=IF(NOT(COUNTIF(C6:F6,1)),1,"")&IF(NOT(COUNTIF(C6:F6,2)),2,"")&IF(NOT(COUNTIF(C6:F6,"X")),"X","")

It doesn't return "Nothing" if none found, just remains blank.

Code: 
[TABLE="width: 522"]
<tbody>[TR]
[TD="class: xl64, width: 87"]P1[/TD]
[TD="class: xl64, width: 87"]P2[/TD]
[TD="class: xl64, width: 87"]P3[/TD]
[TD="class: xl64, width: 87"]P4[/TD]
[TD="class: xl63, width: 87"][/TD]
[TD="class: xl63, width: 87"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]X[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]2X[/TD]
[/TR]
[TR]
[TD="class: xl64"]X[/TD]
[TD="class: xl64"]X[/TD]
[TD="class: xl64"]X[/TD]
[TD="class: xl64"]X[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]12[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64"]X[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]1X[/TD]
[/TR]
[TR]
[TD="class: xl64"]X[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl64"]X[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl64"]X[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64"]X[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64"]X[/TD]
[TD="class: xl64"]X[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64"]X[/TD]
[TD="class: xl64"]X[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]2[/TD]
[/TR]
[TR]
[TD="class: xl64"]X[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl64"]X[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64"]X[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]X[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64"]X[/TD]
[TD="class: xl64"]X[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64"]X[/TD]
[TD="class: xl64"]X[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]2[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Hi,

This will give you what you want:


Book1
CDEFGH
5P1P2P3P4Not Found
61121X
71111X2
8XXXX12
91X12Nothing
102222X1
11X2X21
122X11Nothing
13X1XX2
141XX12
15X2X1Nothing
161X12Nothing
171212X
181XX12
191XX12
Sheet598
Cell Formulas
RangeFormula
H6=SUBSTITUTE(SUBSTITUTE(IF(COUNTIF(C6:F6,"X")," ","X")&IF(COUNTIF(C6:F6,1)," ",1)&IF(COUNTIF(C6:F6,2)," ",2),REPT(" ",3),"Nothing")," ","")


Formula copied down.
 
Upvote 0
gaz_chops said:
Try

=IF(NOT(COUNTIF(C6:F6,1)),1,"")&IF(NOT(COUNTIF(C6:F6,2)),2,"")&IF(NOT(COUNTIF(C6:F6,"X")),"X","")

It doesn't return "Nothing" if none found, just remains blank.

Code: 
[TABLE="width: 522"]
<TBODY>[TR]
[TD="class: xl64, width: 87"]P1
[/TD]
[TD="class: xl64, width: 87"]P2
[/TD]
[TD="class: xl64, width: 87"]P3
[/TD]
[TD="class: xl64, width: 87"]P4
[/TD]
[TD="class: xl63, width: 87"][/TD]
[TD="class: xl63, width: 87"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl64, align: right"]2
[/TD]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]X
[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]2X
[/TD]
[/TR]
[TR]
[TD="class: xl64"]X
[/TD]
[TD="class: xl64"]X
[/TD]
[TD="class: xl64"]X
[/TD]
[TD="class: xl64"]X
[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]12
[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl64"]X
[/TD]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl64, align: right"]2
[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]2
[/TD]
[TD="class: xl64, align: right"]2
[/TD]
[TD="class: xl64, align: right"]2
[/TD]
[TD="class: xl64, align: right"]2
[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]1X
[/TD]
[/TR]
[TR]
[TD="class: xl64"]X
[/TD]
[TD="class: xl64, align: right"]2
[/TD]
[TD="class: xl64"]X
[/TD]
[TD="class: xl64, align: right"]2
[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]1
[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]2
[/TD]
[TD="class: xl64"]X
[/TD]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64"]X
[/TD]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl64"]X
[/TD]
[TD="class: xl64"]X
[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]2
[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl64"]X
[/TD]
[TD="class: xl64"]X
[/TD]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]2
[/TD]
[/TR]
[TR]
[TD="class: xl64"]X
[/TD]
[TD="class: xl64, align: right"]2
[/TD]
[TD="class: xl64"]X
[/TD]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl64"]X
[/TD]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl64, align: right"]2
[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl64, align: right"]2
[/TD]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl64, align: right"]2
[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]X
[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl64"]X
[/TD]
[TD="class: xl64"]X
[/TD]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]2
[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl64"]X
[/TD]
[TD="class: xl64"]X
[/TD]
[TD="class: xl64, align: right"]1
[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]2
[/TD]
[/TR]
</TBODY>[/TABLE]
Click to expand...
gaz_chops, thank you but I wanted it must return "Nothing" in case non of 1X2 is missing</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 
Upvote 0
jtakw said:
Hi,

This will give you what you want:

C
D
E
F
G
H
5
P1
P2
P3
P4
Not Found
6
1
1
2
1
X
7
1
1
1
1
X2
8
X
X
X
X
12
9
1
X
1
2
Nothing
10
2
2
2
2
X1
11
X
2
X
2
1
12
2
X
1
1
Nothing
13
X
1
X
X
2
14
1
X
X
1
2
15
X
2
X
1
Nothing
16
1
X
1
2
Nothing
17
1
2
1
2
X
18
1
X
X
1
2
19
1
X
X
1
2

<TBODY>
</TBODY>
Sheet598​

Worksheet Formulas
Cell
Formula
H6
=SUBSTITUTE(SUBSTITUTE(IF(COUNTIF(C6:F6,"X")," ","X")&IF(COUNTIF(C6:F6,1)," ",1)&IF(COUNTIF(C6:F6,2)," ",2),REPT(" ",3),"Nothing")," ","")

<TBODY>
</TBODY>


<TBODY>
</TBODY>



Formula copied down.
Click to expand...
jtakw, this is amazing, it is giving the desire result as appeal </SPAN></SPAN>

Thank you so much for your kind help</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan :biggrin:</SPAN></SPAN>
 
Upvote 0
Too late it seems :)

=TRIM(SUBSTITUTE(IF(NOT(COUNTIF(C6:F6,1)),1," ")&IF(NOT(COUNTIF(C6:F6,2)),2," ")&IF(NOT(COUNTIF(C6:F6,"X")),"X"," ")," ","Nothing",3))
 
Upvote 0
Another option:

=IF(PRODUCT(COUNTIF(C6:F6,{1,2,"X"})),"Nothing",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("12X",C6,""),D6,""),E6,""),F6,""))
 
Upvote 0
gaz_chops said:
Too late it seems :)

=TRIM(SUBSTITUTE(IF(NOT(COUNTIF(C6:F6,1)),1," ")&IF(NOT(COUNTIF(C6:F6,2)),2," ")&IF(NOT(COUNTIF(C6:F6,"X")),"X"," ")," ","Nothing",3))
Click to expand...
gaz_chops, thanks better late then never it worked perfect</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan :)</SPAN></SPAN>
 
Upvote 0
Eric W said:
Another option:

=IF(PRODUCT(COUNTIF(C6:F6,{1,2,"X"})),"Nothing",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("12X",C6,""),D6,""),E6,""),F6,""))
Click to expand...
Eric, thanks nice formula works perfectly </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan :)</SPAN></SPAN>
 
Upvote 0
Kishan said:
jtakw, this is amazing, it is giving the desire result as appeal </SPAN></SPAN>

Thank you so much for your kind help</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan :biggrin:</SPAN></SPAN>
Click to expand...

You're welcome, thanks for the feedback.

Just an observation, for the Post # 6 formula, TRIM will ignore the Space if it's in the middle of the result, like in row 10
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Adding a custom message.
Replies
1
Views
432
Micron
M
J
  • Question
Outlook Hypperlink of fila in email body will not open
Replies
1
Views
440
josros60
J
M
  • Solved
Return a list of all matching headings for each row
Replies
2
Views
308
MichelleNeedsHelp
M
Kellie220
  • Question
VBA Code Creates Comments with Username and date/timestamp - Need permanent entries
2 3 4
Replies
34
Views
1K
DemetriusTheSuperMan
DemetriusTheSuperMan
E
  • Solved
Userform as search tool
Replies
5
Views
263
Akuini
Akuini

Forum statistics

Threads
1,215,077
Messages
6,122,995
Members
449,094
Latest member
masterms

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top