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 16, 2018

Bebbspoke said:
Aladin - thank you for your question; - the result should flag "Error"... only ONE of the data cols should be populated in a data row... multiple cell pop. in a single data row is NOT ALLOWED.... please, have I screwed up on the logic?
If = YES - please be so kind as to supply the fix, thank you!
Cheers, Bebbspoke

Since your data is non-contiguous, the suggestion of post #4 won't work.

You can either modify the formula you posted in #7 as follows:

=IF(COUNTA(Y5:AA5,AD5,AJ5)=1,IF($Y5<>"",$Y5,IF($Z5<>"",$Z5,IF($AA5<>"",$AA5,IF($AD5<>"",$AD5,IF($AJ5<>"",$AJ5,""))))),"error")

or opt for the suggestion of post #10 .

Bebbspoke · Jun 17, 2018

Hi Aladin & jtakw, thank you both for your efforts & highligting my flawed logic in my post #7
I have tried both #10 & #11 solns, but with no success as neither appear to accommodate for cases where there is no data.

jtakw: - I find your methodology is beyond my comprehension... but please, persevere with me if you will...

Aladin: - I can see that my use of the AND was stupid as you implied by #8 - the COUNTA is the correct operator...

but surely the term IF(COUNTA(Y5:AA5,AD5,AJ5)=0,"", should be incorporated?

I have tried various method here (no joy as yet!)...

Cheers, Bebbspoke

Aladin Akyurek · Jun 17, 2018

What do you want to return when no data?

Bebbspoke · Jun 17, 2018

Hi Aladin - ty for getting back...& your perseverance!

For rows with no data either a blank "" or a dash "-" (not too concerned which but I find it sometimes helpful if a "process" actually flags (e.g. by the dash) that it appears to have been processed).

Cheers, Bebbspoke

Aladin Akyurek · Jun 17, 2018

Bebbspoke said:
Hi Aladin - ty for getting back...& your perseverance!

For rows with no data either a blank "" or a dash "-" (not too concerned which but I find it sometimes helpful if a "process" actually flags (e.g. by the dash) that it appears to have been processed).

Cheers, Bebbspoke

Book1

B

Y

Z

AA

AD

AJ

5

-

6

3

7

8

X

9

10

Error

3

5

11

Error

2

X

12

Error

-

Sheet1

B2, copied down...

=IF(COUNTA($Y5:$AA5,$AD5,$AJ5)>1,"Error",IFERROR(($Y5&$Z5&$AA5&$AD5&$AJ5)+0,$Y5&$Z5&$AA5&$AD5&$AJ5))

appears to meet your requirement.

If not, care to indicate the outcome per record you wish to obtain?

Bebbspoke · Jun 17, 2018

Hi Aladin, thank you again for your time, patience & tenacity!

Your post #15 does not appear to resolve the issue: - plz observe; -

https://www.dropbox.com/s/5ud3pr0og8a9kd3/Collation Req_180617.xlsx?dl=0

which has populated data cols, req as labelled... & a "few we made earlier"... (sorry = maybes solely a "UK only" joke!)

Cheers, Bebbspoke

Aladin Akyurek · Jun 17, 2018

Would you repost that with desired values in AR?

Bebbspoke · Jun 17, 2018

Hi Aladin ty for contiuned help,

New upload to Dropbox link below... shows optimail req. (I've only populated the "-" up to r150), & I'v thrown a few "Errors" in (up to r50 ONLY) where formula should detect multiple row entry & flag "Error" accordingly.

https://www.dropbox.com/s/kx4x02j6z658tqp/Collation Req_180617#2.xlsx?dl=0

Many thanks,
Bebbspoke

Aladin Akyurek · Jun 17, 2018

Do you have the TEXTJOIN function on your system?

Bebbspoke · Jun 17, 2018

Hi - I do not know what TEXTJOIN is - I have Office 2016 "home"... Developer is enabled on a Win 10 machine...

do not see why the requirement but I believe you must have a rationle...

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

Bebbspoke

Board Regular

Aladin Akyurek

MrExcel MVP

Excel Facts

Bebbspoke

Board Regular

Aladin Akyurek

MrExcel MVP

Bebbspoke

Board Regular

Aladin Akyurek

MrExcel MVP

Bebbspoke

Board Regular

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

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

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