Lookup in VBA

[ccordner]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Class[/TD]
[TD]Vehicles[/TD]
[TD]Seats[/TD]
[/TR]
[TR]
[TD]101000[/TD]
[TD]101999[/TD]
[TD]101[/TD]
[TD]2[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]108000[/TD]
[TD]108999[/TD]
[TD]108[/TD]
[TD]3[/TD]
[TD]60[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Booked Unit[/TD]
[/TR]
[TR]
[TD]AB100[/TD]
[TD]AB199[/TD]
[TD]101[/TD]
[/TR]
[TR]
[TD]BV100[/TD]
[TD]BV199[/TD]
[TD]108[/TD]
[/TR]
</tbody>[/TABLE]
(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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Train ID[/TD]
[TD]Dep Time[/TD]
[TD]Destination[/TD]
[TD]Booked Unit[/TD]
[TD]Actual Unit[/TD]
[/TR]
[TR]
[TD]C92[/TD]
[TD]1010[/TD]
[TD]London[/TD]
[TD]101[/TD]
[TD]101123[/TD]
[/TR]
[TR]
[TD]C94[/TD]
[TD]1020[/TD]
[TD]London[/TD]
[TD]108[/TD]
[TD]101224[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[DanteAmor]

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]

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]

Youre welcome. Thanks for the feedback.