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.

jtakw · Jun 15, 2018

Hi,

There may be a couple of problems here.
First, the formula you're using in Column K: =$A1&$B1,$C1,$D1,$E1 is Not a valid formula, unless you mean something else.
Second, if you're Concatenating in K ($A1&B1, where A1 is 10, and B1 is 15) for example, even if A1 and B1 are Real Numbers, the result of A1&B1 is 1015 in TEXT, so it will Never be the same as 1015 as a Real Number.
If you're comparing numbers, you'll need to convert the result of A1&B1 to a Real Number.

Bebbspoke · Jun 16, 2018

Hi jtakw; - ty for msg; yes, I am concatenating (missing &'s was typo)...

but there will only ever be single entry per row of concat cols
so case of A1=10 & B1 =15 as numeric giving concat =1015 as txt does not occur.

I do not actually require the concat of A thru E...
I wish to collate the contents of 5 cols into a single col such data may be txt or numeric, but there will only ever be single entry per row... would be nice to have the single "result" col in number format for further conditional formatting...

Please - any thoughts?

Cheers, Bebbspoke

Aladin Akyurek · Jun 16, 2018

Is this what you are after?

=IF(ISNUMBER(TEXTJOIN("",TRUE,$A1:$E$1)+0),TEXTJOIN("",TRUE,$A1:$E$1)+0,TEXTJOIN("",TRUE,$A1:$E$1))

Bebbspoke · Jun 16, 2018

[FONT=&quot]Hi Aladin, ty for your suggestion!

I'll try to get my head around your formula...
The example I gave had 5 (sequential A thru E ) data cols to be resolved, in practice the data cols are non-sequential so I'll have to experiment...

... I may try the following which (to me) would appear rational, to fill single col X with contents of multiple cols which may be txt or numeric... (I wish the "results" col X to be in Number Format for later processes)

So assuming data is in A, B, C, F and J

=IF(A<>"",A,IF(B<>"",B,IF(C<>"",C,IF(F<>"",F,IF(J<>"",J,"")))))

… such will place contents of a filled data cell into the result col.

having stressed that there should only ever be one of the data cols populated per data row... to check for data corruption (i.e. multiple data col population on a single row)…

So it would be prudent to first check for corruption by

=IF(AND(A<>"",B<>"",C<>"",F<>"",J<>""),"Error",IF(A<>"",A,IF(B<>"",B,IF(C<>"",C,IF(F<>"",F,IF(J<>"",J,"")))))

I believe this will fully achieve my requirements: - to populate the result col with appropriate data regardless of format in such a way that the resultant col may be formatted as numeric for subsequent processing and to include a check for data corruption.

I would appreciate any comment on the above, thank you.
Cheers, Bebbspoke

[/FONT]

Aladin Akyurek · Jun 16, 2018

Would you post an example or two along with the desired results?

Bebbspoke · Jun 16, 2018

Hi Aladin et al,

Herewith complete resolution to my data collation & formatting problem (actual formula as tested on spreadsheet): -

=IF(AND($Y5<>"",$Z5<>"",$AA5<>"",$AD5<>"",$AJ5<>""),"Error",IF($Y5<>"",$Y5,IF($Z5<>"",$Z5,IF($AA5<>"",$AA5,IF($AD5<>"",$AD5,IF($AJ5<>"",$AJ5,""))))))

This interrogates all relevant data cols, checks for corruption and procures collation col such as may be in numeric format for subsequent processing.

Thanks to all for your interest,
Cheers, Bebbspoke.

Aladin Akyurek · Jun 16, 2018

What must happen when say just Z5 and AD5 are not empty?

Bebbspoke · Jun 16, 2018

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

jtakw · Jun 16, 2018

So, if I understand correctly...

There can ONLY be 1 cell with a value, could either be TEXT or NUMBER (but the number may NOT be a real number, meaning still Text).
If MORE than 1 cell has value, return "Error", otherwise, extract the value of the 1 cell, if TEXT, leave as TEXT, if Number (possible Text number), convert to Real Number.

If this is correct, try and see if this formula works for you:

Book1

J

1

Sheet89

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

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

Bebbspoke

Board Regular

Excel Facts

jtakw

Well-known Member

Bebbspoke

Board Regular

Aladin Akyurek

MrExcel MVP

Bebbspoke

Board Regular

Aladin Akyurek

MrExcel MVP

Bebbspoke

Board Regular

Aladin Akyurek

MrExcel MVP

Bebbspoke

Board Regular

jtakw

Well-known Member

Similar threads

Forum statistics

Share this page

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

Board Regular

Excel Facts

Well-known Member

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Well-known Member

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