We help IT Professionals succeed at work.

trouble comparing two number columns in Excel

Medium Priority
143 Views
Last Modified: 2019-07-12
I have a 2 columns of data from oracle, moved to Excel, "Id_Num, Rand_Key"

I have another column that originated on Unix, sent as *.txt, loaded to Excel, "Id_Num_from_Unix"

I'm trying to to VLOOKUP on the Id_Num_from_Unix to the Oracle.Id_Num, but it's not working.

If I compare the two fields, they are different, e.g., =IF(D24=G24,"ok", "Diff !"). But if I subtract the two numbers, I get 0. Visually they're the same.

If I hand type a number, it matches the column from Unix ... so that means the data originating from Oracle is at issue - - - but shouldn't I be able to convert the data type in Excel so things are equivalent? The lengths of the fields are both 6.

I've tried making both columns text, number, general. Can't seem to get the comparison to work.
Excel comparison not working
Comment
Watch Question

Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Can you upload a sample sheet that has the data as it was imported?

I suspect one of the columns has something other than just the number in it, or is being treated as text rather than a number, etc.

I noticed the green corner marker on the left column, if you hover over that what does it tell you it's indicating?


»bp
Gadsden ConsultingIT Specialist

Author

Commented:
Bill Prew,

yes, the green marker - I changed to number and now the data matches for that example. But the overall VLOOKUP still doesn't work ...

comparison after fixing data typeshape---peck-test-for-external-revi.xlsx
Test your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016
Commented:
Right it looks like the values in column A are being treated as text rather than numbers, probably due to the way you are loading them in from their source.  You will need to adjust that so they come in as numbers.

I used the green corner marker on all the values in column A and "converted to number" and notice your VLOOKUP() works now.

shape---peck-test-for-external-revi.xlsx


»bp
Gadsden ConsultingIT Specialist

Author

Commented:
Whoa!!!!! you did it .... thanks!!!!

but the data type WAS number, but not really, since the little warning icon was showing up . . . so yup, it works! awesome, thx.

Will see about the data coming in from Oracle, but now I now ...

<thumbs up> x 2
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Great, glad that was helpful.


»bp
CERTIFIED EXPERT

Commented:
A text that looks like a number is not a number, but you can calculate with it because Excel helps and convert to a number.
But it does not work when you try to match, a number can not match a text.

Convert the unix number to text in the formula like this
=VLOOKUP(TEXT(J2,0),A:B,2,0)

The reverse method, when you want to compare the id text to the unix number, is to multiply by 1 in the formula to convert the text to a number, like this
=VLOOKUP(A2*1,J:J,1,0)
Gadsden ConsultingIT Specialist

Author

Commented:
Ejgil,

great tips, thank you!

Mainly I was confused because I thought the field was number, since I did CTRL-1 and made it a number ... but for whatever reason it was not.

Also, instead of taking the data from my Oracle tool (PL*SQL Developer) to excel directly, I manually copied the data and pasted to excel - and good to go ...

Explore More ContentExplore courses, solutions, and other research materials related to this topic.