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.

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)))

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 ?

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

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

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))

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))

Is your normal delimiter ; instead of ,  perhaps?  The formulas were copied directly from a working example sheet.....

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

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))

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

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

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.

Can you post an example workbook somewhere, and post a link here?  Explain what you want within the workbook.