Conditional Formatting on two columns to ignore *

[jessybg]

Hi I'd like to compare two columns of data and highlight where a value doesn't occurr in both columns. The problem I have is that one of the columns uses * around the text to ensure it catches all variations of the value.

I have the following formula which would work if the data was exactly the same.

=COUNTIF(lst2,value)=0

However, these *'s are meaning it never works.

Is there a way to make it so that if the values with the *'s are not found in the 1st column of data, conditional formatting is activated?

 

[Andrew Poulsom]

Welcome to MrExcel.

Do you mean you don't want to use the * as a wildcard?

=COUNTIF(lst2,SUBSTITUTE(value,"*","~*"))=0

 

[jessybg]

Hmm I don't think I have explained properly.

As you can see below we have two lists. The first list is the result of a sales report, and the second list is designed to analysis the amount of a type of service bought in one month so that we can analyse sales by category. The second list is followed by two columns, a COUNTIF and a SUMIF to calculate quantity and total sales. We have starred * the descriptions in List B to allow for variations in site, and products details.

However, we're finding that every month quite a few items in List A pop up that need adding to List B to balance the two out...which takes forever, trawling through. I hoped to use conditional formatting on List A to highlight any items which have not been caught by the values in List B so that this process is easier. Do you think this is possible?

Thank you J

 

[Andrew Poulsom]

Try the conditional formatting formula in A2 copied down:

=SUMPRODUCT(--(ISNUMBER(SEARCH(SUBSTITUTE(B$2:B$4,"*",""),A2))))=0

 

[jessybg]

Thank you, I haven't got it to work yet but will update with progress when I make some. J