highlight and mark rows when condition is matched

I have an excel sheet with 800.000 rows.

Many cell's contain long lines of information and I would like to check if a cell matches a certain string
that it is not only marked (conditional formatting) but also in the column left of it a check mark is placed.

The check mark or other character that should be placed in the column next to it is used to filter out all marked lines.

March 6th, 2015 9:57am

Use a formula like this to create the check mark, formatted for WingDings font:

=IF(ISERROR(FIND("Text",A2)),"",CHAR(252))

and this formula for CF:

=NOT(ISERROR(FIND("Text",A2)))

Free Windows Admin Tool Kit Click here and download it now
March 6th, 2015 1:33pm

Basically I have a list of about 30 strings, if any of these strings are found in the list of 800.000 rows it should mark the cell/row green.

In the cell on the left of the cell where the string has found a mark should be placed. It doesn't matter which mark is placed, I just need to be able to enable a filter to see which rows do or do not match any of the 30 strings.

Can this be done with a formula ? if so, where do I put the formula ?

Otherwise I guess it should be done with VB,  how would the code look like ?

March 11th, 2015 6:05am

It really depends on whether you are looking for strings that exactly match any of the 30 strings:

Long list              List of 30

X                          X

or strings that contain any of the 30 strings as substrings

blahXBlah              X

or are substrings of the 30 strings

X                         blahXBlah

Free Windows Admin Tool Kit Click here and download it now
March 11th, 2015 8:31am

If any of the 30 strings match it should be triggered and marked

March 12th, 2015 2:44am

I am going to have to assume an exact match, and you list of 20 items is in G2:G31 of the same sheet. Again, format for WindDings

=IF(ISERROR(MATCH(A2,$G$2:$G$31,FALSE)),"",CHAR(252))

Free Windows Admin Tool Kit Click here and download it now
March 12th, 2015 7:35am

This seems  to be something I could use, although I am getting a formula error on both of the formula's.

=IF(ISERROR(MATCH(A2,$G$2:$G$31,FALSE)),"",CHAR(252))

=IF(ISERROR(FIND("Text",A2)),"",CHAR(252))

March 12th, 2015 10:23am

Is your normal delimiter ; instead of ,  perhaps?  The formulas were copied directly from a working example sheet.....
Free Windows Admin Tool Kit Click here and download it now
March 12th, 2015 11:04am

That's it, thanks.

The formula is now almost fine.

One more thing:

The table contains text and there doesn't have to be a 100% match when 2 fields are compared.

For example, if there is one word that is included in a whole line of text it should be a match.

I know you can make a match on for example the first 5 characters, but the text I need to find can be anywhere in the cell and not on a specific location.


  • Edited by Piet200 21 hours 37 minutes ago
March 13th, 2015 5:11am

Format for WindDings and array enter - enter using Ctrl-Shift-Enter

=IF(ISERROR(MATCH(9.99E+307;SEARCH(TRANSPOSE($G$2:$G$31);A2)));"";CHAR(252))

Free Windows Admin Tool Kit Click here and download it now
March 13th, 2015 8:41am

That's it, thanks.

The formula is now almost fine.

One more thing:

The table contains text and there doesn't have to be a 100% match when 2 fields are compared.

For example, if there is one word that is included in a whole line of text it should be a match.

I know you can make a match on for example the first 5 characters, but the text I need to find can be anywhere in the cell and not on a specific location.


  • Edited by Piet200 Friday, March 13, 2015 9:47 AM
March 13th, 2015 9:10am

That's it, thanks.

The formula is now almost fine.

One more thing:

The table contains text and there doesn't have to be a 100% match when 2 fields are compared.

For example, if there is one word that is included in a whole line of text it should be a match.

I know you can make a match on for example the first 5 characters, but the text I need to find can be anywhere in the cell and not on a specific location.


  • Edited by Piet200 Friday, March 13, 2015 9:47 AM
Free Windows Admin Tool Kit Click here and download it now
March 13th, 2015 9:10am

I get an 'Invalid Name' message when entering the formula.

Besides that, I would be perfect if there is no check mark placed, but the value which is in the G column which it matches.

If the first column could list the line which is matched in the 2nd column (according the values in the G column) I can directly use the whole sheet as input for my robocopy script.

March 16th, 2015 7:24am

Can you post an example workbook somewhere, and post a link here?  Explain what you want within the workbook.
Free Windows Admin Tool Kit Click here and download it now
March 16th, 2015 1:51pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics