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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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
P1
P2
P3
P4
Not Found
X
X2
X
X
X
X
X
Nothing
X1
X
X
X
Nothing
X
X
X
X
X
X
X
Nothing
X
Nothing
X
X
X
X
X

<TBODY>
[TD="align: center"]5
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8
[/TD]

[TD="align: right"][/TD]
[TD="align: right"]12
[/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"]1
[/TD]

[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11
[/TD]

[TD="align: right"]2
[/TD]

[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]

[TD="align: center"]12
[/TD]
[TD="align: right"]2
[/TD]

[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13
[/TD]

[TD="align: right"]1
[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]14
[/TD]
[TD="align: right"]1
[/TD]

[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]15
[/TD]

[TD="align: right"]2
[/TD]

[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16
[/TD]
[TD="align: right"]1
[/TD]

[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18
[/TD]
[TD="align: right"]1
[/TD]

[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]19
[/TD]
[TD="align: right"]1
[/TD]

[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]

</TBODY>
Sheet598​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<TBODY>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]H6
[/TH]
[TD="align: left"]=SUBSTITUTE(SUBSTITUTE(IF(COUNTIF(C6:F6,"X")," ","X")&IF(COUNTIF(C6:F6,1)," ",1)&IF(COUNTIF(C6:F6,2)," ",2),REPT(" ",3),"Nothing")," ","")
[/TD]
[/TR]
</TBODY>[/TABLE]

[/TD]
[/TR]
</TBODY>[/TABLE]



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 :grin:</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 :grin:</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 Question
Adding a custom message.
Replies
1
Views
671
Micron
M
J
  • Solved
Using Index/Match (or Something Else) to Return Row(s) of Data
Replies
4
Views
167
jbennett01
J
E
  • Question Question
VBA code clean up
Replies
9
Views
593
mumps
mumps
J
  • Question Question
Outlook Hypperlink of fila in email body will not open
Replies
1
Views
584
josros60
J
M
  • Solved
VBA to Insert Mismatched Row in separate worksheet
2 3
Replies
21
Views
806
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

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