Find occurance of text in a column

1. Find occurance of text in a column

I have 2 workbooks. In workbook (1), I have a column with a few thousand store names. In workbook (2), I have a column with a few hundred store names. The store names are not identical but have the following unique words/numbers: ABC123 etc. I want to identify if a store in workbook (2) exists in workbook (1). I have attached a sample file.

2. Re: Find occurance of text in a column

Select B2

Conditional Formatting
New Rule
Use a formula to determine...

=NOT(ISNA(VLOOKUP(A1,B\$1:B\$8,1,0)))

Format as green
Use Format painter (paintbrush icon) to copy to other cells

3. Re: Find occurance of text in a column

You would need to use Conditional Formatting to do that.

Select column A, go CF, New Rule, and choose Use a formula to determine...

Enter formula:

=SEARCH(C\$2,A2)>=1

Select formatting in click Ok.

4. Re: Find occurance of text in a column

In CF rules

Formula:
`Please Login or Register  to view this content.`

How can you use Conditional Formating.

--In Excel 2007 and 2010, Conditional Formatting is in the Styles group on the Home tab. In Excel 2003, Conditional Formatting is on the Format menu. See here how you can work using CF.

5. Re: Find occurance of text in a column

Select b2:b8
and use below formula in condition format and change format as per your requirement
=ISNUMBER(SEARCH(" "&C\$2&" "," "&B2&" "))*1

6. Re: Find occurance of text in a column

I looked at this a little differently. Seeing that there are a few hundred names to be considered, I assumed that there would be maybe a few hundred unique numbers or words to be compared with.

For workbook 2 I used this formula to draw the names from workbook 1 that matched the keywords or numbers in workbook 2 with the results entered in column H
Formula:
`Please Login or Register  to view this content.`

I then used this formula in conditional formatting to highlight the names that matched the keywords or numbers in workbook 2
Formula:
`Please Login or Register  to view this content.`

7. Re: Find occurance of text in a column

Thanks very much to all for your response. There are a few hundred unique numbers or words and newdoverman's solution works perfectly.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1