Lookup in VBA

ccordner

Active Member
Joined
Apr 28, 2010
Messages
338
I have a sheet with a table listing each train we have based on the unit number, e.g.:

StartEndClassVehiclesSeats
101000101999101240
108000108999108360

<tbody>
</tbody>

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

StartEndBooked Unit
AB100AB199101
BV100BV199108

<tbody>
</tbody>
(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 IDDep TimeDestinationBooked UnitActual Unit
C921010London101101123
C941020London108101224

<tbody>
</tbody>

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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,220
Office Version
2007
Platform
Windows
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.
 

ccordner

Active Member
Joined
Apr 28, 2010
Messages
338
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.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,220
Office Version
2007
Platform
Windows
Youre welcome. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,778
Messages
5,488,821
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top