+ Reply to Thread
Results 1 to 9 of 9

vlookup issue with data downloaded from Internet

  1. #1
    Registered User
    Join Date
    02-09-2009
    Location
    Winnipeg, Canada
    MS-Off Ver
    Excel 2003
    Posts
    49

    vlookup issue with data downloaded from Internet

    Hi,
    I am trying to use vlookup function with web query data and some of the data has embeded characters I can not see and vlookup does not work (can not match even though numbers appear to be the same). I have attached a test file with the data and formula and a detailed explanation.
    Found this code:
    Sub TrimALL()
    'David McRitchie 2000-07-03 mod 2000-08-16 2005-09-29 join.htm
    '-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall
    ' - Optionally reenable improperly terminated Change Event macros
    Application.DisplayAlerts = True
    Application.EnableEvents = True 'should be part of Change Event macro
    If Application.Calculation = xlCalculationManual Then
    MsgBox "Calculation was OFF will be turned ON upon completion"
    End If
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim cell As Range
    'Also Treat CHR 0160, as a space (CHR 032)
    Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    'Trim in Excel removes extra internal spaces, VBA does not
    On Error Resume Next 'in case no text cells in selection
    For Each cell In Intersect(Selection, _
    Selection.SpecialCells(xlConstants, xlTextValues))
    cell.Value = Application.Trim(cell.Value)
    Next cell
    On Error GoTo 0
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    End Sub
    Saved it and then selected the input text on column J and run this Macro but still Vlookup does not work. Any idea how to fix this? If this macro works (maybe doing something wrong) how can I run it everytime this worbook is opened?
    Attached Files Attached Files
    Last edited by bmind; 06-12-2009 at 12:11 PM.

  2. #2
    Forum Contributor
    Join Date
    11-24-2008
    Location
    Delhi
    Posts
    104

    Re: vlookup issue with data downloaded from Internet

    Select the Range

    go to Data < Text to Column < press Finish < Ok

  3. #3
    Registered User
    Join Date
    02-09-2009
    Location
    Winnipeg, Canada
    MS-Off Ver
    Excel 2003
    Posts
    49

    Re: vlookup issue with data downloaded from Internet

    Quote Originally Posted by hardeep.kanwar View Post
    Select the Range

    go to Data < Text to Column < press Finish < Ok
    Thanks for the quick response,
    While this works on the test file I created it does not work on the original file. The second problem is that I can not afford to do this everytime I open the workbook as the data is updated many times during the day.... I need to automatically change the data when I open workbook so formula would work. I am not sure why this solution would not work on my notebook?!

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: vlookup issue with data downloaded from Internet

    bmind,

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

    Members are not allowed to post assistance until this is corrected.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: vlookup issue with data downloaded from Internet

    deleted op hadn't put code tags as requested
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  6. #6
    Registered User
    Join Date
    02-09-2009
    Location
    Winnipeg, Canada
    MS-Off Ver
    Excel 2003
    Posts
    49

    Re: vlookup issue with data downloaded from Internet

    Quote Originally Posted by NBVC View Post
    bmind,

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

    Members are not allowed to post assistance until this is corrected.
    Is this ok now? Sorry, did not know...will remember it for the future...

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: vlookup issue with data downloaded from Internet

    =VLOOKUP(--J5,$C$1:$G$195,2,0)
    will do it the -- forces the value in j5 to be a number which will happily be found in col c

  8. #8
    Registered User
    Join Date
    02-09-2009
    Location
    Winnipeg, Canada
    MS-Off Ver
    Excel 2003
    Posts
    49

    Re: vlookup issue with data downloaded from Internet

    Quote Originally Posted by martindwilson View Post
    =VLOOKUP(--J5,$C$1:$G$195,2,0)
    will do it the -- forces the value in j5 to be a number which will happily be found in col c
    Works!
    Thanks for quick response.

  9. #9
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: vlookup issue with data downloaded from Internet

    on second thoughts !!! you could just change the macro at line
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1