Cell value flagged as <> when I can see it IS =???

Bebbspoke · Jun 15, 2018

Hi Folks,

Col K is populated by formula = $A1&$B1,$C1,$D1,$E1 and may be txt or numeric as processed internally elsewhere.

Col N is populated manually, txt or numeric

Col M =IF(AND($K1="",$N1=""),"",IF($K1<>$N1,"Error","OK"))

The object is to determine whether a manual entry is same as that as internally processed

Works fine for txt but matching numeric returns Error???

(Manual entry to K are recognised correctly)

Plz advise, ty.

Aladin Akyurek · Jun 17, 2018

Ok.

Cells that look empty are in fact not.

=COUNTA(AJ5)

yields 1 instead of 0.

How come?

Aladin Akyurek · Jun 17, 2018

This should do it:

=IF(LEN(Y5&Z5&AA5&AD5)=0,"-",IF(COUNT(1/LEN($Y5),1/LEN($Z5),1/LEN($AA5),1/LEN($AD5),1/LEN($AJ5))=1,IFERROR((Y5&Z5&AA5&AD5)+0,Y5&Z5&AA5&AD5),"Error"))

even in the presence of the problem I mentioned in my previous post.

jtakw · Jun 17, 2018

Don't know where all your data is coming from in Columns Y, Z, AA, AD, AJ
May be imported or from some formula, but All the cells that Look Empty are in fact Not empty, may be the result of a null string from a formula using ""
Aside from you wanting a Dash to indicate the Row has been "checked", that's the reason my formula in Post #10 and reposted by Aladin in Post #15 didn't work.

This modified formula should do it as you wanted. But we might want to investigate and may be fix the problem from the source (How those null strings got into the Y, Z, AA, AD, AJ cells), in case there may be Additional Calculations and/or Comparisons to be made regarding these Columns.

=IF(LEN($Y5&$Z5&$AA5&$AD5&$AJ5)=0,"-",IF(SUM(N($Y5=""),N($Z5=""),N($AA5=""),N($AD5=""),N($AJ5=""))<4,"Error",IFERROR(($Y5&$Z5&$AA5&$AD5&$AJ5)+0,$Y5&$Z5&$AA5&$AD5&$AJ5)))

Bebbspoke · Jun 17, 2018

Hi Guys,

Many thanks to you both for working on this - v. pleased to say that jtawk Post #23 has solved the prob... (the fact that it employd operators I'm unfamiliar with is a little concerning, but such is solely my personal problem)!

I've tested it on real data (where results were known & confirmed by other systems) and also tested for detection of data corruption i.e. multiple entry on single rows)... All OK & flags Errors as appropriate. Super.

You question the data source... the examples as given were imported by c & p "values" from a development spreadsheet; such is why they may appear "problematic...

plz, one last question... when "new" formula is strung down... it effectively removes the previous conditional formatting (font & fill colours)... is there function or key combo that will force the reapplication of "Conditional Rules"?...

I have to rely colourfill quite quite considerably as my work involves a gent with Asperger's syndrome where at times he may perceive patternation & devise actions from what to all intents & purposes & to common man appears to be a random binary string... I have fair reason to assume this guy does Laplace transforms in his head in real-time... instantaneously!.. which rather humbles one who ocassionally finds Excel syntax & Boolean somewhat frustrating...

Thanks again to you both & cheers,
Bebbspoke

Bebbspoke · Jun 17, 2018

Addenda (Aladin) - Post #22 seems to miss all the numerics? - txt is processed OK but no numerics.
such does not actually worry me as Post #22 has been proven, cheers.

jtakw · Jun 17, 2018

You're welcome, glad it's working for you.As far as your CF Color fill for the cells is concerned, I'm afraid you'll have to update your CF rules according to the new values in the cells...

Aladin Akyurek · Jun 17, 2018

Bebbspoke said:
Addenda (Aladin) - Post #22 seems to miss all the numerics? - txt is processed OK but no numerics.
such does not actually worry me as Post #22 has been proven, cheers.

I just missed including the last cell in the formula, which is easily restored...

=IF(LEN(Y5&Z5&AA5&AD5&AJ5)=0,"-",IF(COUNT(1/LEN(Y5),1/LEN(Z5),1/LEN(AA5),1/LEN(AD5),1/LEN(AJ5))=1,IFERROR((Y5&Z5&AA5&AD5&AJ5)+0,Y5&Z5&AA5&AD5&AJ5),"Error"))

It's still a short formula.

Bebbspoke · Jun 18, 2018

Aladin - pleased you resolved #22 - I've applied your #27 - and yes; it is a full resolution to the original proble....

? - how to force Excel to re-run formatting subsequest to a formula mod? - any ideas? Thanks, & thanks again for your perseverance. Cheers, Bebbspoke

Aladin Akyurek · Jun 18, 2018

Bebbspoke said:
Aladin - pleased you resolved #22 - I've applied your #27 - and yes; it is a full resolution to the original proble....

Thanks, appreciate the comment.

? - how to force Excel to re-run formatting subsequest to a formula mod? - any ideas? Thanks,

Not clear what do you mean here.

& thanks again for your perseverance. Cheers, Bebbspoke

Glad to help.

Bebbspoke · Jun 18, 2018

... "not sure what I mean".... if I have a worksheet fully formatted with conditional formats to highlight etc... i& all works as intended... if I then change a formula by copy paste / whatever ... the cell formatting may disappear ... & it is observed that some of the conditional formula then acquire "XFD" prefixes - this is what I seek to address, thanks.

Cell value flagged as <> when I can see it IS =???

Bebbspoke

Board Regular

Aladin Akyurek

MrExcel MVP

Excel Facts

Aladin Akyurek

MrExcel MVP

jtakw

Well-known Member

Bebbspoke

Board Regular

Bebbspoke

Board Regular

jtakw

Well-known Member

Aladin Akyurek

MrExcel MVP

Bebbspoke

Board Regular

Aladin Akyurek

MrExcel MVP

Bebbspoke

Board Regular

Similar threads

Forum statistics

Share this page

Cell value flagged as <> when I can see it IS =???

Board Regular

MrExcel MVP

Excel Facts

MrExcel MVP

Well-known Member

Board Regular

Board Regular

Well-known Member

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock