Lookup in VBA
Results 1 to 4 of 4

Thread: Lookup in VBA

  1. #1
    Board Regular
    Join Date
    Apr 2010
    Posts
    336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Lookup in VBA

    I have a sheet with a table listing each train we have based on the unit number, e.g.:

    Start End Class Vehicles Seats
    101000 101999 101 2 40
    108000 108999 108 3 60

    I also have another table with the diagrams and which unit is booked to work each diagram, e.g. :

    Start End Booked Unit
    AB100 AB199 101
    BV100 BV199 108
    (The first two letters are always the same for the start and the end of each series).

    I also have a list of trains in a CSV exported from a program, which is a bit like this:

    Train ID Dep Time Destination Booked Unit Actual Unit
    C92 1010 London 101 101123
    C94 1020 London 108 101224

    I am cycling through the third table, to compare the planned unit to the train actually working the service. I then need to work out if it is longer, shorter, etc. This means for each line I have to take the data in the third and fourth columns and look up the information in the first and second tables.

    What is the best way of doing this? At the moment, for each row in table three, I'm having to loop through tables one and two, which seems inefficient. Would I be better creating a couple of dictionaries? Or something else? There are hundreds of rows in table three, so I'm trying to make it as efficient as possible.

    Thanks
    Chris

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,669
    Post Thanks / Like
    Mentioned
    64 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Lookup in VBA

    I could build an automatic process with a macro.
    In your example, the macro would read, from the third table, the Booked Unit 101. Look for it in table1 and table2.
    But what data from table1 and table2 should you get and where do you want to put them?

    You can prepare a couple of files, an excel with your tables 1 and 2, and a csv file with table 3, and upload them to the cloud.
    In the excel file you must explain what you need as a result. Or maybe, a third file with the expected result.

    You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Apr 2010
    Posts
    336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup in VBA

    Thanks - Like you suggested, I've written a macro that just searches through the tables rather than trying to make it more complicated than that. Appreciate the help.

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,669
    Post Thanks / Like
    Mentioned
    64 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Lookup in VBA

    Youre welcome. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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