Excel VBA for VLOOKUP and table
Using Excel at the workplace
At my current workplace I have been known as one of the “go to college” when it comes to tips and tricks in Excel but also more advance topics like new formulas or macros. One of my colleagues in another department asked over lunch if I knew something that could help him with updating names in a column in Excel. The problem was that another program outside of Excel automatically updates the names but just once a day, and new data can be imported with several thousand rows hours before which therefore have no name for a long time.
I cannot go into detail regarding the problem or what kind of real data is being used since it is a secret. But I will use an example of how I solved the problem with fake generated data.
Let’s first start by observing the problem, as stated above, this is just a simple example that in some way tries to mimic the real data. The picture below shows three colums, Index, Number and Name. My collegue wanted something that could retrive the Name which correspinds to the assiociated Number, a task perfect for VLOOKUP!
My college said that the table that contains the Numbers and Names are in another excel sheet (which is updated manually). The table only contains two columns, the number and the associated name THe table is named allt_table, an example of the table can be viewed below:
How to activate the macro
How to use the macro is very straightforward. There is a button next to the data table and all the user has to do is to push the button and the macro will activate.
How the macro works
The first thing that the macro does is to check if the column that the VLOOKUP function will be performed on is in correct column place, which should be B, if the column header is not in the column B than an message box will appear and tell the user that there is something wrong with the structure of the columns.
The next thing that happens is that cell C2 is selected and the following formula is written in the cellvalue:“=IFERROR(VLOOKUP(RC[-1],allt_tabell,2,FALSE),”""“)”. RC stands for reference cell and RC[-1] therefore refers to the cell to the left of the selected cell. Remember, the selected cell was C2 so the formula does a regular VLOOKUP on the value in cell B2 where we look after the number in the table named allt_table. The VLOOKUP function is wrapped in a IFERROR function which creates an empty cell if there is not any name associated with the number (instead of just being #NA).
And the next thing is that the macro uses autofill to apply the above function to all cells below (there is no empty rows in the data), using autofill is the fastest way to apply the formula to all rows. Another approach would be to use a loop but that would take several minutes if the data has 300 000 rows like this example.
And the last thing that the macro does is that it copies the names and paste them as values so that the formula disappears and only the names remains.
Below is a gif that shows the whole procedure and how much time it takes, remember that the number of observations are 300 000.
This was an example of a very good combination of VBA and current functions in Excel. The real example used in the workplace is of course more advanced and have more steps that checks that everything is correct.
The code for the macro in this short example can be viewed below:
Take care!
Kind regards Per Granberg