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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
645
Micron
M
E
  • Question Question
VBA code clean up
Replies
9
Views
536
mumps
mumps
J
  • Question Question
Outlook Hypperlink of fila in email body will not open
Replies
1
Views
579
josros60
J
M
  • Solved
VBA to Insert Mismatched Row in separate worksheet
2 3
Replies
21
Views
751
Peter_SSs
Peter_SSs
T
  • Question Question
Mandatory fields with blank rows
Replies
7
Views
236
Cubist
Cubist

Forum statistics

Threads
1,223,889
Messages
6,175,226
Members
452,620
Latest member
dsubash

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