• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

when I paste data a vlookup does not 'see' the numbers and returns #N/A

C

Carguy

New Member
I've imported raw data from another site and am using it as the basis of a vlookup.


However, the data in the cell I am looking at in the vlookup formula does not seem to be formatted correctly and all I get it the dreaded #N/A result.


If I click on the source data and 'refresh' it, I then have the value retuned in the vlookup formula.


My problem is; I have 500+ lines of text I am working with and I don't want to double click 500 cells. I have tried copy, paste, changing to number, everyhting I can think of but the only way to get a result is to double click the imported data.


To clarify:

* imported data is 5 colums wide, 500+ long

*Vlookup is looking at correct information on both sheets (look at number 123 on host sheet, look for number 123 on imported sheet and return value in column 5)

* The data is there on both sheets, I have checked

* When I click on my test line, double clicking the cell with number 123 on the imported sheet then returns the data from column 5 on my vlookup formula.


I'm sure there is an easy way but I can't figure it.
 
Carguy


Firstly, Welcome to the Chandoo.org Forums


Can you please check that you have calculations set to Automatic

Goto the Formula, Calculations Options and check that Automatic is Ticked


Alternatively, After pasting the new data press F9

That will force Excel to Recalculate and should avoid the need for you to Edit, Enter
 
Hi, Carguy!


Tried yet to open that workbook in Excel but in another computer neither connected to the same network nor to the internet? (I don't know if it has links to other sources). If you did that, it worked properly, it worked similary needing to "refresh the source" or it didn't work at all?


Could you describe too the import process? It's manual, you have a macro, it's time based, from where do you import the data, where do you store it, are there any external links in the same workbook?


Consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.


Regards!
 
Not able to test on another computer sorry.


The data is sourced from a Microsoft Sharepoint database, which is exported in a table format.

I cut/paste it back to text so its as clean as I can get it. No links, new spreadsheet.


Next step is bring in copy of existing spreadsheet from another workbook; move and copy. This data is good, vlookups work in the reverse on this data (if you know what i mean).


So I do a new vlookup, all works OK on all other data in that sheet. This time am looking at names (text) and return numerical values. All works. Appears to only be the numberical data source that is the problem.

I have noticed this on other sheets/workbooks and have finally been spurred into action.
 
Are the data ranges, Number of Rows always identical ?

If you paste 500 rows over an area which expects 400 rows the formulas may not be recognising the extra rows
 
Hui - the formula is not pasted, it is written for the values I want to return.

JB7 - I'm not interested in one of the share sites sorry so no file will be loaded. Makes it hard yes, but I value my privacy more.


=VLOOKUP(D2,'owssvr(1)'!$B$2:$F$489,5,0) is the formula I am using, where D2 is the value, owssvr is the imported data and the range is well, the range.


the vlookup works - it is the fact that I cannot get it to return a value until I double click (refresh if you will) the data in B2 of owssvr.
 
@Carguy,


Your privacy will be maintained if you read the Third & Fourth Green Sticky Post on the forum's home page. It will cost you nothing if you be polite in your posts.


I suggest you to look for extra spaces that human eye do't catch immediately. Press F2 in some of the cells that you are dead sure should give you some result and then see what happens.


When using voolkup, it makes a lot of difference when you type "Carguy" and try to find "Carguy " in the Lookup Table. Hope that helps.


Regards,

Faseeh
 
Good day Carguy


quote"JB7 - I'm not interested in one of the share sites sorry so no file will be loaded. Makes it hard yes, but I value my privacy more."end quote


A site such as Dropbox does not impede on your privacy and you may find it difficult to get help without uploading a file, if it contains sensitive data then strip it out.


I assume that by JB7 you are referring to SirJB7
 
@Faseeh, b(ut)ob(ut)hc


Hi!


Yes, I assumed that I was the destinatary of such not politically correct comment, but I didn't take the trouble to even answer that post as the OP hadn't read the green sticky post regarding file uploads or if he did he demonstrated having no interest on investing a little of his time (hence charging us with the consequent overload, fact which seems to don't be important) following the instructions of:

a) 3rd. post, paragraph "Important"

"When Posting data online always remember to anonymise the data, especially names or data if it is commercial or confidential in nature."

b) 4th. post, 6th. paragraph

"Randomize Numbers and Names if appropriate."

c) Post neither compliant with 4th. post, 9th. paragraph

"Please use language which conveys respect, appreciation and love."


So I decided to stick to the phrase that have been below my nick at Messenger for years and now at Skype:

"no digas nada a menos que lo que digas sea más importante que el silencio" -----> in my poor English: "don't say anyghing unless what you say is more important than silence".


It's an old proverb whose authorship I think it's still disputed between the Arabic Anonymous and the Chinese Anonymous... Anonymous, the most prolific writer, composer, singer, painter, all *er, might be a guy with multiple passports as Jason Bourne?


Regards!


PS: b(ut)ob(ut)hc, please provide me the correct translation, if you don't care, thank you.
 
Guys guys guys you need to chill out. Starting at the bottom if I have insulted SirJB7 by not putting the correct title, then I do apologise. Not having dealt with the Titled Gentry of this world my manners may be remiss. Now not having met the eminent SirJB7 I need to know what the title has been bestowed for? Sevices to humanity? Work with impoverished children of the world? Restoration and care of the Amazon? It may be secret mens business, in which case I respect your right to deny answering this question.


To address your post and to [insert title here] bobhc - insulting as it is to myself - the reason I do not and will not put a file onto some third party website is that the company I work for monitors our wensite use and folks have been tapped on the shoulder for utilising sites such as this. We have our own internal sites we use (we're a multi-national company if you wish to know) so using one of these makes lights go off deep down somewhere and not long afterwards you are in a room with the door closed. Do I want that? No thanks. Quite apart from the fact I have 3 major projects on the go and I don't want to sign up to some site, confirm my presence and then get junk mail from them.


Charging you with consequent overload is your interpretation only. Had I posted a file you would have seen a spreadsheet with a number (formatted as such) and a vlookup which was perfect in its construction. You would have marvelled at why this *perfect* string was returning a #N/A value when all looked good. Indeed, moving the cell references on the same string brings me back joyous, real data.

I can tell you that as a excel user of 8 hours per day, 5 days per week I'm not lacking in the is my string correct department? so no amount of viewing would have fixed it.


The issue is one of formatting of the imported data. It actually happens a bit with my work so I suspect there is somethig happening with our servers to corrupt the data. So far, you have all focussed on a vlookup string that you have automatically assumed is the problem, despite my claims that it is not. No one has looked at formatting of imported data and why I need to double click on the numbers in cell D2 to get them to return a value in my vlookup target cell.


Mr Miller C, I did try your suggestion but no good. Still returns #N/A, but I appreciate your lateral thinking there.


SirJB7 you said it yourself in your last post; your poor English may be causing you to interpret written text in a way not intended. I certainly did not write any post to be offensive (well maybe this one as you started it) and I wonder if your grasp of one of the most difficult languages in the world has skewed your ability to read a genuine post asking for help which has turned into a slanderfest.


Your phrase is totally insulting to me the more I read it, as you have made this assumption based on your views only and not looked at it rationally.


Are you a "Sir"? Not in my books buddy, not by a long way.
 
Hi ,


In case you are still looking for a solution , if you wish , you can mail me the file , with just the imported data in it. My ID is narayank1026 [at] gmail [dot] com.


Narayan
 
Carguy


With reference to your last post

There are those who are of the opinion that Australian’s are divest of manners and common curtsies, I have never subscribed to that as having meet them in the flesh so to say, however you have started to make me think that I was wrong.


This forum has been going some years now and has helped many hundreds, people that appreciated the help received and the free lessons in Excel, many but not all take the time to say thank you for the help they have received. All lot of posties have had problems trying to upload work sheets because of restrictions at work, but when pointed to sites such as Dropbox, have constructed a work sheet at home with data similar to the data at work, not much data just enough to show their problem, if they need to upload sensitive data they strip it out.


You say that some of the suggestions given are insulting to you and the more you read then the worse the insult gets. I can only assume that you are living in a small world devoid of regular contact with humans.


In your first paragraph after your sarcastic words about the Titled Gentry you go on to ask as to why SirJB7 was invested with this title and reel of some of the reasons he could have received the title, none of these are close, he holds this title for the unstinting work he has done for the Excel community and this forum in particular. A title bestowed by you peers carries more weight than one bestowed by a Monarch.


Also his understanding of the English language is far better than that of many who were born to it.


Quote"I certainly did not write any post to be offensive (well maybe this one as you started it)"end quote, the last time I heard that expression " you started it" was as I passed by an infants school just the place you would expect to hear childish comments, you say you work for a multi national company, you must be part of their local community project for under privileged children.


You have ask for help and members offered help, the same help all new posters are offered but you have spat your dummy out and gone off on one because you thought you were being insulted, because members did not use the time they give for free to sort out you problem your way. As good as people such as SirJB7 are they are still in the learning curve when it comes to second guessing what a spread sheet looks like with the absolute minimum of information given such as you have.


I am sure some members may still be willing to help you but I am equally sure that because of your tone many will not.


You are a disgrace to Australians and Australia.


You Ignorance, bad manners and lack of common curtsies are thankfully a rare combination rarely seen on this forum
 
@b(ut)ob(ut)hc

Hi, my old friendly dog!


Good afternoon.


I'd like to write down a couple of things in order to state clearly what I regret of.


I sincerely apologize for having dared to choose a nick so offensive and injurious, but I should adduce that I was so young (and you know that young people does improper things) when I did it in the middle of the 90's, and always used it and still keep it alive. I beg to be judged compassionately for such blameworthy act. As a proof of my goodwill I promise that in my next life I'd consider seriously to pick one like OutcastedSlave or TwoPennyNaughty, should they be more adequate?


The other thing I regret of -and more than previous- is to be writing this post, but as your countryman Jason Statham in The Transporter who had rules that never should be broken until he did, I don't consider courteous and polite to leave your comments without an answer. Bob, I actually appreciate very much your disposition and I thank you infinitely for your attitude, besides of fully matching concepts. This owed acknowledge led me to break another Maxim (it's correct? like precept), for which I don't know it origin not it author (and if I did I want a lawyer!), and it says:


"never argue with certain kind of people, onlookers may not be able to tell the difference"


or this similar but of proved unknown source:


"never argue with certain kind of people, they will only bring you down to their level and beat you with experience".


So, having passed by to say thanks, I'll stick to my original quote which in fact prevents me from breaking these two.


My best regards!


PS: Maybe the first is Prussian? Who knows... I Kant assure it.
 
Good evening my friend SirJB7


I may be considered a bit old fashioned by some but I do believe in manners and common curtsies.

Both which possess an unusual properties in that both are free but the more they are used the more valuable they become, and the user grows in stature with his fellows.

Unlike arrogance and bad manners, both of which have their own unique properties in so much as the more they are used the more the user becomes insignificant in the eyes of his fellows.


"never argue with certain kind of people, they will only bring you down to their level and beat you with experience".


I do use this as a signature but I use the word Idiot in place of "certain kind of people" :)
 
Great to see the Forum Police are active here. You two are pretty impressive with your chest puffing and patting each other on the back. As good as I have seen on other forums. One of you may make Lord the way you are going.


One of the reasons I don't lurk in forums like these is the issue of having to deal with people who have a higher sense of self importance. Me, I'm just down to earth tell , it like it is and could not quote any verse or text as above. Whilst there will be people who are impressed with your quotes they go straight over my head into the don't care basket.


NARAYANK991 I do appreciate your offer and I was going to email you the files as it has me stumped. Now I will deal with it myself and find a solution. This is no reflection on you at all and had these two other clowns not needed to affirm their authority on the rest of this pretty good site I would have it in the mail by now.


Bob you are a particularly nasty little man who feels the need to go one better. You've upped the argument by bring race and nationality into the argument. Really?


Now I have wasted 15 minutes of my time with you I will go back to meaningful and constructive work.
 
There are a couple of things you could try to avoid the #NA errors:

append an empty string to the value being looked up:

=VLOOKUP(A1 & "",DataRange,2,FALSE)


If that did not resolve the error, try to append an empty string to the DataRange also

=VLOOKUP(A1 & "",DataRange & "",2,FALSE)

enter with Ctrl + Shift + Enter


Hope that helps.


Cheers,

Sajan.
 
The opposite to my previous example (which turns the cell into text) is to turn the cell into a value: Value(A1)


I have this problem often where I work, numbers recorded as text in excel. One of these always work for me, hope it does for you!
 
You must log in or register to reply here.
Back
Top