Table Transfer Tool

An Excel Add-in for transferring data from one table to another, matching multiple columns at once, with the option to only transfer data into blank cells in the destination table (and not overwrite existing data).

It is available on Github at https://github.com/ceybers/excel-table-transfer.

What does it do?

You select which columns in each table uniquely identify the rows, then you select all the columns you want transfered between the two tables. The tool then transfers all the data across into the destination table while not overwriting any other data.

Why did you make this tool?

At work we spend a lot of time transferring data between spreadsheets. Once the number of rows reachs the 5 digit range, and you're working with over a hundred columns, conventional tools like VLOOKUP are no longer practical. Even with smaller tables, you might only want to pull data into empty cells in the destination table, and you certainly don't want to overwrite existing data with an error if it wasn't found in the source table. And having to repeat the process because you need to transfer several columns does not scale at all.

How does it work?

It starts off by selecting the source table and the destination table.

Once you've selected a pair of tables, it will try to guess which columns contain your unique keys before allowing you to manually choose. It will then check the quality of both columns so that you can easily confirm that all the values are unique and that there are no duplicates or blanks.

Next it will compare the two columns to see which keys are present in both tables, and which keys are only present in one of the tables. Keys that are only present in the source table but not the destination are considered additions, and keys present in the destination but not found in the source are considered orphans.

Once we have two tables with a key column in each table joining them, we can choose which columns we want to match between the two and transfer data across. By default the tool will try and match columns with the exact same name. The user can then of course choose pairs of columns to map together.

Once the user has confirmed what they want, the tool then transfers the new data across and highlights the changes.

Going Forward

Some features I'd like to add would be different highlighting based on what kind of transfer it was (new value into an empty cell, new value replacing an old value, completely new row, etc.), as well as a summary dialog box showing how long it took and which rows and columns were affected.

I also want to add a History feature that would keep a record of all the transfers a table was used in, so you can repeat the operation with a single click. This would also need to handle things such as filenames changing or worksheet names changing while the contents are still the same.

Lastly, if we keep a Change Data Capture record for all the cells we change, we'd be able to implement an Undo function.

Closing Thoughts

The TODO list for this project has grown and scope-creeped to such an extent that it overwhelming to the point I'd need to start over.

I wrote the original 2021 after procrastinating on the idea since about 2019. I tried to rewrite it in 2022 while on leave from work but I ran out of time. Since then I've spent an inordinate amount of time learning how to squeeze the maximum utility out of VBA, and if I were to continue on the project, I'd definitely need to rewrite everything from scratch.