Match Items from Overlapping Lists


Before and After 1   Before and After 2   Watch video showing example usage

(click image to enlarge)

(click image to enlarge)

(click image to play - has sound)

Say you have one long list of items (stock units, account balances, bird names etc) with some information about them, and a second list with some/most of the same items and additional information about them.

You want to line up the two lists so that items with the same names/codes are on the same row.

This may be because you are reconciling two lists that should have the same population, or it may be that there are good reasons why the populations of the two lists do not match.

If it is just that one list is missing some items that are in the other list then you can use VLOOKUP, although that is tedious if there are lots of columns in each list.

But if both lists have items that are missing from the other then it is not possible to use only formulae.

The macro in the Excel file accessible here provides a solution - it lines up the items from the two lists that have the same index/name/code (and you can then add further lists to the result of the first two)

It does not matter whether one list is a subset of the other, or they just overlap.

After running the matching process, items which appear in only one list are aligned with blank half-rows in the other list.

After starting the process, you specify which column from each set of data to use for matching, so if E.g. personnel number is the same, it does not matter that E.g. full name is spelt differently in the two lists.

You also specify which rows at the top are headers to be omitted from the sorting- often only one, but if more, just select them during the process.

Simple instructions: Copy the two sets of data to separate sets of columns on the sheet Input_Data and click on the big grey button in the top left corner which will give you prompts to select which input data to use for what.

Further instructions are on the sheet "Notes" within the file, and there are more videos at Eloquens.

For further questions, please contact: queries . miol [at] matterwise.com
If you have any comments, please contact: feeedback . miol [at] matterwise.com