Lookup in VBA

ccordner

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

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top