+ Reply to Thread
Results 1 to 32 of 32

Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

  1. #1
    Registered User
    Join Date
    04-19-2017
    Location
    London, England
    MS-Off Ver
    Office 2010
    Posts
    72

    Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Hello all,

    I have attached a sheet with a basic example of what I am trying to achieve. I have data on the "Data" sheet which is stored / historical data which I am looking to reference. I have new data for today`s date in sheet "NewData" which I want to compare with the existing historical data. I initially want to just copy the data in Data and paste it below, changing the date to the date in the NewData sheet. This is of course straight forward but once I have it below the historical data, I then want to lookup to see if the data appears in NewData or not. If the result is no, just return a zero but if the result is yes then I need to do a second lookup in sheet vlookup.

    My main area where I am struggling is the number of rows changes every day, so I cannot fix that as well as whether to fix a VLOOKUP function in a cell, copy it down, try to install a wait in the code while it updates and then delete the empty rows ?

    Any advice on how to tackle this in a streamlined way would be great as I can see this running very slowly if I try it on more than the 20 or so lines in the example attached !

    Many thanks

    JM
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Macro must be run in the desired results sheet.

    Please Login or Register  to view this content.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    04-19-2017
    Location
    London, England
    MS-Off Ver
    Office 2010
    Posts
    72

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Hello,

    Thanks for the suggestion. It almost works ! First off, I have to run the macro twice which I don't quite understand. The first time i run it, only the first row is populated and then following the macro again, it populates all of the rest but also adding in an extra row ?

    My other issue is that if I understand the code correctly, it is using the number of rows in the vlookup sheet to set one of the values. I need to go from the bottom of the date data on sheet Data, count up until the date changes and then use that value to determine the LR value in your suggested code. As I mentioned in my original post, there will be different numbers of lines (unrelated to what is in vlookup sheet) for each day. I have tried to insert an if active cell.offset(R-1,0) is not = activecell to come up with the value needed for the number of rows but this keeps losing its place as well as being clunky. - Is there a way to search from bottom of a column up the rows until the date value differs from the bottom one and record that as the number of rows for that day and therefore the thing to check against ?

    Thanks again for the help - it is very close to working exactly as i was hoping !

    JM

  4. #4
    Registered User
    Join Date
    04-19-2017
    Location
    London, England
    MS-Off Ver
    Office 2010
    Posts
    72

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    I have been trying further to figure this out and was wondering if there is a way to do this quicker without looping up through every cell ? My code is below to achieve this and it works but when i run this on hundreds of lines it will be slower. Any thoughts welcome !

    Thanks

    JM

    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Ok my friend

    Try this Macro,

    Please Login or Register  to view this content.



    The first thing is you need to avoid using loops at all costs.

    So if you want to find where the date changes looking from the bottom up you should use a formula, in a helper column.

    For example this will insert the row number whenever the date changes

    Insert in D2 and fill down
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Two macro instructions will do that for you

    Please Login or Register  to view this content.



    Insert this formula in E2

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    That will give to the row number of the last date change.

    You can do that using one macro instruction:-

    Please Login or Register  to view this content.
    Last edited by mehmetcik; 05-15-2017 at 05:18 PM.

  6. #6
    Registered User
    Join Date
    04-19-2017
    Location
    London, England
    MS-Off Ver
    Office 2010
    Posts
    72

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Many thanks for the additional code and very kind explanations with the ' items.

    My main challenge at present is that if the new data does not contain an item in row A for the previous day, I want to select that entire row, copy it, paste it to a new (exceptions) sheet and remove the line. Is that doable within the VBA process / one command or am I best to use a helper column to insert a modified version of that lookup to mark them, run a filter and paste them to the new (exceptions) sheet ? - Your code allows me to identify them but not sure the best way to split them out.

    I am just struggling with the right and efficient way to approach this "compare and contrast" type of scenario. - If I start with 15 new rows and I had 20 yesterday, how to have 2 sheets where one is the 15 new, matching yesterday and the other is the 5 exceptions.

    As a minor point, I have been using and trying to adapt the code that you supplied. Given there are numerous (and I have added more) specific values defined in the code, is there a way to show the custom values (your LR values) on the VBA editor ? I have been using
    Please Login or Register  to view this content.
    to keep a note of it but was wondering if there is a feature on the editor or similar to show them ?

    The help is very much appreciated.

    JM

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Is this how you wanted?
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    04-19-2017
    Location
    London, England
    MS-Off Ver
    Office 2010
    Posts
    72

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Hello,

    Thank you very much for what looks to be some very nifty code ! It works great on the (sample) data I initially supplied. When I extend the data a little, the macro returns wrong / strange data. I have attached a version of this to the post.

    If you look at the "larger result" sheet, there are various gaps. I would preferably just run the lookup on the previous day's data and return the lookup value or zero on that. If it is easier to run it on the whole column then I would prefer to add something to enable me to in a later stage of code, filter it out.

    I would like to just end up with everything from sheet "data" with the new inputs in sheet "newdata" underneath with the vlookup / zero if not present check applied.

    The new direction / suggestion is very much appreciated but I am not overly familiar with the UBound command and therefore struggling !

    Many thanks

    JM
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Confusing....

    Which sheet is the result that you want?

  10. #10
    Registered User
    Join Date
    04-19-2017
    Location
    London, England
    MS-Off Ver
    Office 2010
    Posts
    72

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Sorry - was just trying to show how both results come up ! - Looks like I have overcomplicated....

    I want the data to be presented like it is in the "desired result" sheet. - When the new data in sheet "new data" (for current/most recent date) matches with the data with the most recent date (only) in sheet "data", perform the vlookup, otherwise return "0".

    Many thanks

    JM

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Isn't my code working like so?

  12. #12
    Registered User
    Join Date
    04-19-2017
    Location
    London, England
    MS-Off Ver
    Office 2010
    Posts
    72

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Almost ! - How your code is working is displayed on the "Larger result" sheet.

    The problem comes when I add more than one date into the "Data" sheet to be checked with the new data on the "Newdata" sheet. - It checks back / runs a lookup all the way to the beginning of the data. I need it to just look at the most recent date (at the bottom / end of the data) and run the lookup on that.

    Thank you

    JM

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Can you just upload a workbook with data/new data/vlookup and result that you want?
    So that I can see if the logic I made is correct or not...

  14. #14
    Registered User
    Join Date
    04-19-2017
    Location
    London, England
    MS-Off Ver
    Office 2010
    Posts
    72

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Please see attached for a slightly extended Data sheet and the desired output.

    Many thanks

    JM
    Attached Files Attached Files

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    OK, now I think I got the logic right...I hope
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    04-19-2017
    Location
    London, England
    MS-Off Ver
    Office 2010
    Posts
    72

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Yes you have indeed !

    That is awesome, thank you very much. I just have a small follow on question to enhance my knowledge / understanding. If I need to lookup or make use of different columns with this code, are there any areas which I should leave as they are and not amend ? - I want to apply this code / logic to a data set which has 10-15 columns and instead of looking for the value in column B in the vlookup, I want to look for column F or other. Could you perhaps mark the values to amend as red or bold ? - I am sorry but UBound and use of a scripting dictionary is all new to me !

    Many thanks, this looks like it will work perfectly.

    JM

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    It is fully depends on the data layouts.

    Unlike Excel Range, Array should get the correct data range that is one of the most important part or it will end up with run-time error...
    Apart from vlookup sheet,
    Please Login or Register  to view this content.
    Above line is the range to work with means A1:Cx.
    So if the column differs, you will need to adjust it.

  18. #18
    Registered User
    Join Date
    04-19-2017
    Location
    London, England
    MS-Off Ver
    Office 2010
    Posts
    72

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Thank you for the reply. My real data is thousands of rows long by 20 columns wide.

    So for example, if I have the date information in column A, (as before) but the value to compare to the previous day is in column T. Once that value in column T is found or not, the VLOOKUP is still done on column B of the lookup sheet (as before).

    Is that an easy tweak to make or will it always be case by case ?

    Thanks so much for the mega fast code / solution.

    Best

    JM

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Do data/New data sheets have same layouts?

  20. #20
    Registered User
    Join Date
    04-19-2017
    Location
    London, England
    MS-Off Ver
    Office 2010
    Posts
    72

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    They currently do not have matching layouts. This code is meant to take the new data, see if it is in the old data or not and then pull in some reference data accordingly to enrich it. If it helps / makes things more simple to illustrate to me then I can insert a script ahead of this code to format it to match. There will of course be some columns which have zero values, which I was planning to fill in with the Vlookup query.

    Thanks

    JM

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    The point is that are there any complete blank row(s)/column(s) within the data range.

    If no blank row/column then

    Please Login or Register  to view this content.
    Array(Array("data", Array(1, 20, 20)), Array("newdata", Array(1, 10, 10)))
    First element is the sheet name and corresponding columns, 1 = Col.A for Date, 20 = col.T for info 1, another 20 is to make up the array 3 columns.

  22. #22
    Registered User
    Join Date
    04-19-2017
    Location
    London, England
    MS-Off Ver
    Office 2010
    Posts
    72

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Thanks for the explanation. Am I therefore right in thinking that if the main data has say 20 columns and the new data has 5 then I need to insert columns to make them line up and then run the code ?

    If this is the case and I insert the columns to match (and make anything blank a zero/check value), is all I need to amend the array you mention above ? I just tried it and got a VBA invalid ref error on
    Please Login or Register  to view this content.
    Perhaps this is down to me editing the code incorrectly / not editing enough !

    Much appreciated.

    JM

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Well, guess game will never end....

    If you upload a small sample workbook, data/newdata/vlookup/result, I can show you how.

  24. #24
    Registered User
    Join Date
    04-19-2017
    Location
    London, England
    MS-Off Ver
    Office 2010
    Posts
    72

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Thank you very much.

    Hopefully you can see what I am trying to achieve from the attached.

    Best

    JM
    Attached Files Attached Files

  25. #25
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    You should have uploaded this workbook in the first place.

    Where did the all data in other columns come from in result for those added info1 and the date from new data?

  26. #26
    Registered User
    Join Date
    04-19-2017
    Location
    London, England
    MS-Off Ver
    Office 2010
    Posts
    72

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Very sorry - I was just trying to keep the sheet and the question as small as possible. The plan was once I had a solution for the smaller data set to then edit the solution and expand it.

    The data in the other columns will be calculated against some static values in another sheet as well as some of the values retrieved in the vlookup in column O.

    I was then going to make use of some of the earlier code in this post to fill in the gaps.

    Many thanks for your help - your solution runs miles faster on the amended data, I just need to figure it out with more / different columns !

    Sorry for the confusion

    Best

    JM

  27. #27
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    See if this is how you wanted.
    Please Login or Register  to view this content.

  28. #28
    Registered User
    Join Date
    04-19-2017
    Location
    London, England
    MS-Off Ver
    Office 2010
    Posts
    72

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Almost..... For some reason on the last value of the new data which is imported returns a blank value in column O. I have tried running it a few times with different lengths of new data and I always get the mystery blank value !

    Otherwise this code is working exactly as I was hoping.

    As attached.

    Many thanks for your patience and help.

    JM
    Attached Files Attached Files

  29. #29
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Try change to
    Please Login or Register  to view this content.

  30. #30
    Registered User
    Join Date
    04-19-2017
    Location
    London, England
    MS-Off Ver
    Office 2010
    Posts
    72

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    That works exactly as I was aiming for, thanks so much for the solution !

    Just for my knowledge / understanding, in your code, I don't see you referring to the Vlookup sheet and therefore the range to lookup directly. The code works beautifully but I am trying to understand how it works so that I can build upon it / use it in further ways. This is no doubt my lack of VBA / UBound / Scripting Dictionary understanding !

    Best

    JM

  31. #31
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    Quote Originally Posted by millerjj22 View Post
    I don't see you referring to the Vlookup sheet and therefore the range to lookup directly.
    In the other sub
    Please Login or Register  to view this content.
    Quote Originally Posted by millerjj22 View Post
    The code works beautifully but I am trying to understand how it works so that I can build upon it / use it in further ways. This is no doubt my lack of VBA / UBound / Scripting Dictionary understanding !
    See vba help for Dictionary object for details and also goole about VBA Array.
    These are the keywords for the code.

  32. #32
    Registered User
    Join Date
    04-19-2017
    Location
    London, England
    MS-Off Ver
    Office 2010
    Posts
    72

    Re: Best way to apply (multiple) VLOOKUPs via VBA to this situation ?

    That is awesome. A true Forum guru !

    Thank you.

    JM

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Multiple VLOOKUPs?
    By motamedn in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-22-2016, 04:46 PM
  2. [SOLVED] Need formula involving multiple Vlookup and/or multiple if situation
    By WKMAHESH in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-19-2015, 04:22 PM
  3. VLOOKUPS in multiple closed workbooks with multiple tabs
    By exclusivelyexcel in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-23-2011, 03:33 PM
  4. Excel 2007 : Multiple Vlookups
    By inquizitiv in forum Excel General
    Replies: 15
    Last Post: 10-26-2011, 03:22 PM
  5. sum of multiple Vlookups...
    By p51mustang in forum Excel General
    Replies: 9
    Last Post: 07-25-2010, 03:49 AM
  6. multiple vlookups - what else?
    By lennyphil in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-13-2009, 08:52 PM
  7. Multiple If Then Vlookups.
    By VegasL in forum Excel General
    Replies: 4
    Last Post: 03-16-2008, 11:49 PM

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