HOW TO FIND THE DIFFERENCE BETWEEN VALUES IN SAME CELL?

[bharath]

HI,

I ATTACHED THE FILE
CELL A1= -1,-,1,-1 RESULT IN CELL B1 =" OK "
CELL A2= -1,-2,-1 RESULT IN CELL B2 = "NOT OK"

PFA

THANK YOU

 

[Khalid NGO]

Hi,
You can use the Text to Column option to split your data in separate columns using comma as Delimiter, or the similar with following formula, to be entered in G2, copy across and down:

=TRIM(MID(SUBSTITUTE($E2,",",REPT(" ",LEN($E2))),(LEN($E2)*(COLUMN(A1)-1))+1,LEN($E2)))

Then you can easily differentiate between these results with simple COUNTIF or AND function.

Regards,

 

[bharath]

hi,
as you said, i copied the formula in g2
but not getting required result
i attached file (with using formula)

Better to using formula instead of text to columns option.
may characters length increase..

 

[bosco_yip]

In G2, copied down :

=IF(ISERR(-SUBSTITUTE(SUBSTITUTE(E2,LEFT(E2,FIND(",",E2&",")-1),""),",","")),"OK","NOT OK")

Regards
Bosco

 

[Khalid NGO]

Hi,

See the attached with few changes in formula.

Regards,

 

[bharath]

superb,
thanks its working

 

[bharath]

In G2, copied down :

=IF(ISERR(-SUBSTITUTE(SUBSTITUTE(E2,LEFT(E2,FIND(",",E2&",")-1),""),",","")),"OK","NOT OK")

Regards
Bosco

working but, in g24 showing "ok"
need to show "not ok" (-8, -7, -7)

 

[bharath]

Hi,

See the attached with few changes in formula.

Regards,

thank you so much, if LEN(CELL) may increase. So better to use one formula in a cell only

 

[bosco_yip]

working but, in g24 showing "ok"
need to show "not ok" (-8, -7, -7)

Sorry in haven't fully checked the results, then try this instead :

=IF(COUNT(INDEX(-MID(SUBSTITUTE(E2,LEFT(E2,FIND(",",E2&",")-1),""),ROW($1:$99),1),0)),"NOT OK","OK")

Regards
Bosco

 

[shrivallabha]

Slightly different approach which works with your data.
=IF(LEN(TRIM(SUBSTITUTE(E2&",",LEFT(E2&",",FIND(",",E2&",",1)),"")))=0,"OK","NOT OK")

 

[Khalid NGO]

Amazing solutions Bosco and Shiri

 

[bharath]

SUPERB SIMPLIFICATION
THANK YOU