Match Items from Overlapping Lists
Originally published: 12/02/2021 15:05
Last version published: 05/07/2021 10:58
Publication number: ELQ-93067-5
View all versions & Certificate
certified

Match Items from Overlapping Lists

Align items from lists which have overlapping populations.

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 spelt 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

Acquire business license for $5.00

Add to cart

Add to bookmarks

Discuss

Further information

To take two lists (with any number of columns) that have each have an index column containing a code that is common to the two sets of data.
Output is the same two lists, both sorted by the index column, but with gaps in one side or the other where there is no matching item in the other list.

Typically, up to 5,000 lines on each side (with 15 columns in each) takes less than two minutes.
Up to 50,000 lines on each side will complete, but may take an hour (so set going before finishing for the day?)
Beyond that, a SQL database is a more appropriate tool - do contact us for analysis, specification, or coding.

There are extra Guidance Notes in the downloadable file.

N.B. All videos have sound!

You are using Excel on a PC, not on a Mac.
Excel 2010 onwards, although if you are running Excel2010 it is no longer supported, and potentially vulnerable to hacking, so you should really be on Excel 2013 or later.
It probably works on earlier versions too, but as nobody should be using those, we have no ability to test it on them.

You use a Mac.

Reviews

  • Rate this Downloadable Best Practice

    Write a review

  • AnonLG(last updated: 07/07/2021 13:30)
    Smart Reconciliation Tool
    Great little tool to facilitate reconciliations - will be useful for multiple datasets.
  • Elena Sage(last updated: 13/03/2021 17:10)

Discussion feed for Match Items From Overlapping Lists

The user community and author are here to help. Go ahead!


5.0 / 5 (2 votes)

please wait...