Filter range based of cell based on another range

Hello all,

I'm having a problem trying to find the best way to do this:

I have to sheets: Sheet1 with one column with around 4000 rows and sheet2 with one column with 219 rows.

On the column with the 4000 rows I want to filter it so that only appears the ones that contains the string on the 219 rows. Like this:

Sheet1

Col1
XYZ0034AAA1
XYZ0137AAA2
XYZ0384AAA1
etc...

Sheet2

col1
XYZ0034
XYZ0137
XYZ0384
etc...

Now, since the value its not the same I was using something like this formula in a new column in sheet1:

=COUNTIF(Sheet2!$A$2:$A$230;"*" & $A2 &"*")

Where the second A2toA230 is the range from Sheet2 and the second A2 is the criteria from sheet1 that with the wild cards before and after I tough it would give me 1 but it gives me all zero unless the content of the row is exactly the same . For example the row with XYZ0034 in sheet2 is also in sheet1 but in this one its got "AAA1" after it.

hard to me to explain. Noob at excel... but our Dev guy is on vacations so... I'm on google trying to find an answer...

Many thanks in advance,
Dieneces

if you are comfortable with macro try this macro "test"

Please Login or Register  to view this content.

Please Login or Register  to view this content.