Check External Cell References in Row for exact match excluding blanks/zeros

[Protoplasm]

Hi all,

I'm just writing as i'm struggling to work out how to check a row that cells match exactly whilst excluding any blanks.

The thing that complicates this is all values are pulled in as external references from various other worksheets (L-Q in below example).

I have done quite a bit of digging in previous threads to see if i could find something that works and found this formula:


=IF(COUNTA(B23:G23)-COUNTIF(B23:G23,LOOKUP(2,1/(B23:G23<>""),B23:G23))=0,"OK","Difference")

where the ranges involved cover the 5 arguments i'm looking to match whilst ignoring blanks, in column R in the above example. I've tested this and it seems to work fine for "true" blanks but not for blanks that are created by other formulas.

I've found that when the 0's pulled through from external references are forced to show as blank (i've tried doing this with IF functions, left the 0s visible in the above example for ease of explanation) these still dont show as matching, despite the matching formula being set up to ignore blanks, as if the formula isnt recognising them as blank.

For example the above table should be showing rows 4+5 to be OK but show as different, despite using an IF(x=0,"",x) type formula to force 0s to blank in practise.

Is there any way i can check the rows for identical matching values whilst excluding 0s or blanks forced by referencing external workbooks? Can the above formula be adapted to account for this?

Thanks in advance,

 

[sijpie]

Your example is a bit confusing
Your formula is about columns B:G, yet your image is about columns L:Q