Tip:
Highlight text to annotate it
X
Join 2 Sheets Join 2 sheets enables you to join two worksheets
together based on common values in a specified column. In this example, we will be comparing
the supplier master file with the employee master file to identify any common bank account
numbers to identify fraud. The first worksheet is Supplier Master File,
within the Supplier Master File workbook, and the matching columns are Bank Account
and sort code (you can use up to 3 matching fields). The second is the employees worksheet
within the Employee Master File Workbook, and the matching columns are Bank Account
Number and Sort Code. In this example, we are only interested in cases where there are
matches, so we will select "rows that match" for both sheets. We can also add a summary
of the join. TopCAATs has identified matches for further
investigation, and added a summary comment in cell A1 with information about the join.
Another example of where Join 2 Sheets can be used is where you wish to combine information
from 2 worksheets into one. For example, we have a year-end stock listing, and a listing
of all sales made after the year-end, and we wish to join them based on the part number.
The first sheet is Closing Stock and the matching column is Part Number. The second sheet is
Sales Records Summary, and the matching column is also Part Number. In this case, we will
add all items from the stock sheet, and only matches from the Sales sheet.
Of the 1,682 stock lines, 1,443 were matched to sales. As there were only 1,097 lines in
the sales listing, there will be some duplicate part numbers in the stock listing which can
be identified using the separate Duplicates tool.
This report can now be used for Net Realisable Value testing of Stock, and a number of the
tests in the Inventory Section Module require both stock and sales information on the same
sheet, like this. The Join 2 Sheets tool can also do fuzzy matching
to allow you to find approximate matches. To enable this, click the "Use Levenshtein
Fuzzy Method" box. Minimum distance is the minimum number of character differences between
matched terms, maximum distance is the maximum number of character differences, and maximum
matches limits the number of matches that the tool will show for each item in Sheet
1. For more information on the Levenshtein method, please see the TopCAATs User Guide.