Search and mark

geezup

New Member
Joined
Jun 21, 2010
Messages
20
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{ !!! }
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Try this

=IF(ISNA(MATCH("*GDV5*",2:2,0)),"No","Yes")
 
Last edited:
Upvote 0
Machine NameContains Windows 7 VMFull nameEmail AddressOSSerial NumberLast Check-inVMWare - Virtual Machine infoVMWare - Virtual Machine infoVMWare - Virtual Machine info
MacBook ProUser1User1@company.comMac OS X 10.13.6Serial12 minutes ago 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
MacBook ProUser2User2@company.comMac OS X 10.13.6Serial207/25/2019 at 12:26 PM VMWare VM #1 File Name: /Users/User2/Virtual Machines.localized/User2.vmwarevm/User2.vmx displayName = User2 Network Type:
MacBook ProUser3User3@company.comMac OS X 10.14.4Serial3Today at 6:08 AM 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: natVMWare 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: natVMWare VM #3 File Name: /Users/User3/Virtual Machines.localized/User3 GDV5.1.vmwarevm/User3 GDV5.1.vmx displayName = User3 GDV5.1 Network Type: nat

<tbody>
</tbody>


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

Thanks :)
 
Last edited:
Upvote 0
Did you try the formula in post #5 ?

ABCDEFGHIJ
1Machine NameContains Windows 7 VMFull nameEmail AddressOSSerial NumberLast Check-inVMWare - Virtual Machine infoVMWare - Virtual Machine infoVMWare - Virtual Machine info
2MacBook ProYesUser1User1@company.comMac OS X 10.13.6Serial12 minutes agoVMWare VM #1 File Name: /Users/User1/Virtual Machines.localized/User1-GDv5.1.vmwarevm/User1-GDv5.1.vmx displayName = User1-GDv5.1 Network Type: nat
3MacBook ProNoUser2User2@company.comMac OS X 10.13.6Serial207/25/2019 at 12:26 PMVMWare VM #1 File Name: /Users/User2/Virtual Machines.localized/User2.vmwarevm/User2.vmx displayName = User2 Network Type:
4MacBook ProYesUser3User3@company.comMac OS X 10.14.4Serial3Today at 6:08 AMVMWare 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: natVMWare 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: natVMWare VM #3 File Name: /Users/User3/Virtual Machines.localized/User3 GDV5.1.vmwarevm/User3 GDV5.1.vmx displayName = User3 GDV5.1 Network Type: nat

<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>
</tbody>

Formeln der Tabelle
ZelleFormel
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>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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