Match Items from Overlapping Lists

Align items from lists which have overlapping populations.

by Matterwise
Modeloff 2019 (Excel competition) Global Top50, Process Expert, Qualified Risk Manager, Chartered Accountant.Follow

2 reviews339 views|1 comment|Bookmark Add to cart - $5.00

Description
Say you have one list of 200 items (stock units, account balances, bird names etc) with some information about them, a second list of 190 items with additional information, and you want to line up the two lists so that items with the same names/codes are on the same row.If it is just that the second list is missing 10 items then you can use Vlookup, although it is tedious if there are lots of columns in each list.But if the second list is missing 30 items that the first list has, while it has 20 additional items that the first list does not have, then it is not possible to just use formulae.This tool provides a solution for that case.And you can also use it for the case where only one of the lists is missing items that appear in the other list.After running the matching, items which appear in only one list have blank half-rows in the other list.You choose the columns to use for matching, so if E.g. the personnel number is the same, it does not matter that the full name is spelled differently in the two lists.You also choose which rows have headers - often only one, but you may have more.You may have lists of E.g. sales by person, generated for each day/week/month.Due to joiners and leavers, the different lists do not all have the same population.If you are working through the lists for several periods at once, then you can put the output of the first run into the input sheet and run again to create a comprehensive list.If you add the data for each time period at the end of that period, then just keep the data in a copy of this file, and add the new data when it becomes available.The licence enables you to make as many copies as you want (so long as only you run the macro).You can also compare successive columns for one pair of lists, which can be useful for account reconciliations.E.g. first match by account code, then match unmatched rows by amount, then match still unmatched rows by date.Before purchasing, do check that you have permissions to run Excel macros on your machine.If you don't know this already, use the free tool at: https://www.eloquens.com/tool/YP3XfZJj/finance/excel-vba-tutorials/how-to-check-if-you-can-run-macros-on-your-computer

This Best Practice includes
1 Excel macro file, 1 PDF of how to enable macros, 1 Excel file of test data in case you want to replicate video demos