Search and mark

[geezup]

Hey all,

I have a excel sheet that I need to search row by row for specific keyword ( GDV5 ) and if it is found anywhere in the row it will write to Column B yes , if it is not found in the whole row it will write NO.

I hope that explains my point. I know how to look for a specific clumn for a data and then write a yes/no , but to search the whole row regardless of how many columns for that row. Again if found anywhere in the whole row , Yes/No in column B.

HELP{ !!! }

 

[DanteAmor]

Try

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:265.19px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td >Yes</td><td > </td><td >GDV5</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td >No</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td >Yes</td><td > </td><td > </td><td > </td><td >GDV5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td >No</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td >No</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td >No</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td >Yes</td><td >GDV5</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=IFERROR(IF(MATCH("GDV5",2:2,0),"Yes"),"No")</td></tr></table></td></tr></table>


Note: Change the number of times Excel iterates a formula
- Click the File tab, click Options, and then click the Formulas category. ...
- In the Calculation options section, select the Enable iterative calculation check box.
- Type the number 1 of iterations in the Maximum Iterations box.

 

[DanteAmor]

Or how about:

=IF(ISNA(MATCH("GDV5",2:2,0)),"No","Yes")

Note: Change the number of times Excel iterates a formula
- Click the File tab, click Options, and then click the Formulas category. ...
- In the Calculation options section, select the Enable iterative calculation check box.
- Type the number 1 of iterations in the Maximum Iterations box.

 

[geezup]

I am referring to a column in the row that has ( .localized/testuser-GDv5.1.vmwarevm ) , so GDV5 is the key word I want mentioned. So if it exists anywhere in the whole row write yes to clumn B

 

[DanteAmor]

Try this

=IF(ISNA(MATCH("*GDV5*",2:2,0)),"No","Yes")

 

[geezup]

[TABLE="width: 2118"]
<tbody>[TR]
[TD]Machine Name[/TD]
[TD]Contains Windows 7 VM[/TD]
[TD]Full name[/TD]
[TD]Email Address[/TD]
[TD]OS[/TD]
[TD]Serial Number[/TD]
[TD]Last Check-in[/TD]
[TD]VMWare - Virtual Machine info[/TD]
[TD]VMWare - Virtual Machine info[/TD]
[TD]VMWare - Virtual Machine info[/TD]
[/TR]
[TR]
[TD]MacBook Pro[/TD]
[TD][/TD]
[TD]User1[/TD]
[TD]User1@company.com[/TD]
[TD]Mac OS X 10.13.6[/TD]
[TD]Serial1[/TD]
[TD]2 minutes ago[/TD]
[TD] VMWare VM #1 File Name: /Users/User1/Virtual Machines.localized/User1-GDv5.1.vmwarevm/User1-GDv5.1.vmx displayName = User1-GDv5.1 Network Type: nat[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MacBook Pro[/TD]
[TD][/TD]
[TD]User2[/TD]
[TD]User2@company.com[/TD]
[TD]Mac OS X 10.13.6[/TD]
[TD]Serial2[/TD]
[TD]07/25/2019 at 12:26 PM[/TD]
[TD] VMWare VM #1 File Name: /Users/User2/Virtual Machines.localized/User2.vmwarevm/User2.vmx displayName = User2 Network Type:[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MacBook Pro[/TD]
[TD][/TD]
[TD]User3[/TD]
[TD]User3@company.com[/TD]
[TD]Mac OS X 10.14.4[/TD]
[TD]Serial3[/TD]
[TD]Today at 6:08 AM[/TD]
[TD] VMWare VM #1 File Name: /Users/User3/Virtual Machines.localized/Fresh_Install_macOS 10.14.vmwarevm/Fresh_Install_macOS 10.14.vmx displayName = Fresh_Install_macOS 10.14 Network Type: nat[/TD]
[TD]VMWare VM #2 File Name: /Users/User3/Virtual Machines.localized/macOS 10.14 2.vmwarevm/macOS 10.14 2.vmx displayName = macOS 10.14 Network Type: nat[/TD]
[TD]VMWare VM #3 File Name: /Users/User3/Virtual Machines.localized/User3 GDV5.1.vmwarevm/User3 GDV5.1.vmx displayName = User3 GDV5.1 Network Type: nat[/TD]
[/TR]
</tbody>[/TABLE]


Basically i want Column B to hold either yes or no if the row contains GDv5 anywhere within it.

Thanks

 

[DanteAmor]

Did you try the formula in post #5 ?

	A	B	C	D	E	F	G	H	I	J

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:76.04px;"><col style="width:80.79px;"><col style="width:76.04px;"><col style="width:76.04px;"><col style="width:76.04px;"><col style="width:76.04px;"><col style="width:76.04px;"><col style="width:230.97px;"><col style="width:230.97px;"><col style="width:230.97px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]Machine Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]Contains Windows 7 VM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]Full name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]Email Address[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]OS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]Serial Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]Last Check-in[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]VMWare - Virtual Machine info[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]VMWare - Virtual Machine info[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]VMWare - Virtual Machine info[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]MacBook Pro[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]Yes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]User1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]User1@company.com[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]Mac OS X 10.13.6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]Serial1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]2 minutes ago[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]VMWare VM #1 File Name: /Users/User1/Virtual Machines.localized/User1-GDv5.1.vmwarevm/User1-GDv5.1.vmx displayName = User1-GDv5.1 Network Type: nat[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]MacBook Pro[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]No[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]User2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]User2@company.com[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]Mac OS X 10.13.6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]Serial2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]07/25/2019 at 12:26 PM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]VMWare VM #1 File Name: /Users/User2/Virtual Machines.localized/User2.vmwarevm/User2.vmx displayName = User2 Network Type:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]MacBook Pro[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]Yes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]User3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]User3@company.com[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]Mac OS X 10.14.4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]Serial3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]Today at 6:08 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]VMWare VM #1 File Name: /Users/User3/Virtual Machines.localized/Fresh_Install_macOS 10.14.vmwarevm/Fresh_Install_macOS 10.14.vmx displayName = Fresh_Install_macOS 10.14 Network Type: nat[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]VMWare VM #2 File Name: /Users/User3/Virtual Machines.localized/macOS 10.14 2.vmwarevm/macOS 10.14 2.vmx displayName = macOS 10.14 Network Type: nat[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]VMWare VM #3 File Name: /Users/User3/Virtual Machines.localized/User3 GDV5.1.vmwarevm/User3 GDV5.1.vmx displayName = User3 GDV5.1 Network Type: nat[/TD]

</tbody>

Formeln der Tabelle

Zelle Formel B2 =IF(ISNA(MATCH("*GDV5*",2:2,0)),"No","Yes") B3 =IF(ISNA(MATCH("*GDV5*",3:3,0)),"No","Yes") B4 =IF(ISNA(MATCH("*GDV5*",4:4,0)),"No","Yes") <tbody> </tbody>

<tbody>
</tbody>