Conditional formatting not picking up numbers?!

[afc171]

Hello,

I am trying to conditional format data which is imported from an MS query. For some reason, I guess the formatting when imported doesn't seem to pick up the conditional formats. This only happens when the data is numbers, works fine with alphanumericals.
Any idea how I can get around this?

I created another sheet (DNO) where the products to lookup are as this might change in the future.

Thanks

Consumables Mastersheet V2.xlsm
	B
1	product
2	12004271
3	12004272
4	12004403
5	12004403
6	1351114
7	1351118
8	1351118
9	1351118
10	1450016
11	1610393
12	1610393
13	1610396
14	1610610
15	1610610
16	1610737
17	1610737
18	1610772
19	1610772
20	1610782
21	1610782
22	1704157
23	1704159
24	1704159
25	1705061
26	1705061
27	5678084
28	5678124
29	5678124
QR9.0.12BioRad

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:B	Expression	=ISNUMBER(MATCH(B1,DNO!$A:$A,0))	text	NO

 

[BenMcBen]

Have you checked that the numbers in your list and the numbers coming in ARE actually numbers, not text that look like numbers? (use ISNUMBER()) to check both on the sheet. The you could make any adjustments required - by either using value() or appending a null string to force it as text() accordingly - either:

=ISNUMBER(MATCH(VALUE(B1),$A:$A,0))
or
=ISNUMBER(MATCH(""&B1,$A:$A,0))

 

[Joe4]

The fact that all the numbers in the image are left-justified seems to suggest that they are actually text entries (numbers entered as text).
Numbers are right-justified, by default.

 

[Fluff]

Try using countifs rather than match

=COUNTIFS(DNO!$A:$A,B1)

 

[BenMcBen]

Try using countifs rather than match

=COUNTIFS(DNO!$A:$A,B1)

Just for my own edification - why?

 

[Fluff]

Countifs will treat a text number as a real number, whereas match doesn't

 

[afc171]

Try using countifs rather than match

=COUNTIFS(DNO!$A:$A,B1)

Thanks guys, this worked perfectly.

Someone else did say if I select the products and then do Text to Columns it works but would mean to do that everytime.

 

[Fluff]

Glad we could help & thanks for the feedback.