Matching between two excel files

Help
1

Hello,

I’m trying to match up data between two excel files. In Book 1 I have a list of Companies Names that i want to indicate if they appear in the Companies Name list in Book 2.

If there is a match then in Book 1 on column B a “Y” should be recorded. If there is no match a “N” should be recorded.

Further it should take account of variations between Ltd or Limited. So that if in Book 1 a Company is called “Company 2 Limited” the process should still match it up with “Company 2 Ltd” in Book 2. The same will also be true if in Book 1 a Company is called “Company 3 Ltd” the process should still match it up with “Company 3 Limited” in Book 2.

I can setup the data tables and the variables but get stuck when it comes to the actual comparison. I have started with a For Each Row Activity for dtBook1 and inside another For Each Row Activity for dtBook2. I’ve then added an If Activity to compare the two Company Name strings. I’m unsure where to go from here though.

Book1.xlsx (8.1 KB)
Book2.xlsx (8 KB)

2

I’ve done a similar thing recently using the following steps:

Read in both spreadsheets as separate data tables
add a new column to DT1 to handle the match status
use a for each row on DT1 and inside it filter DT2 by the company name of the current row.
Use an if statement to see if DT2.rows.count > 0. If it is then there is a match and update the new column with a Y. If no match then move on to the next row in DT1

For the actual name comparison in the filter, you could do a wildcard search for “Company 2 *” which will match both Limited, Ltd, LTD. and any other permutations. Just replace all instances of limited or ltd with the * wildcard

3

Thanks Andy, i’ve added in as suggested. The workflow runs but the original excel is not being updated. I think i am doing something wrong in the Filter Data Table activity.

CheckFAMEList.xaml (20.9 KB)

Invoice Data.xlsx (655.0 KB)
CosOver5Employees_UKEir.xlsx (8.3 KB)

4

Hi Jon

I can’t open most of your workflows as we are running different versions of the Studio. One thing I did notice was that you use a write cell activity looking at cell F1, is that the only place you are writing an output? If so, it will only ever update that specific cell in the spreadsheet

5

Hi Andy,

I have a counter setup and the write cell activity pointing to “F” + count.ToString. Current setup in images below including the data filter:

Image%201
Image%201662×710 40.3 KB
Image%202

6

Sorry Andy, looks like its actually working! :slight_smile: thanks for your help. I’m going to work on this further so will probably be back!

7

Ah ok, that looks fine to me.

It may be an issue with your filter then, pop some log messages in to display the number of rows in the DT or use a write range to output it to a new, blank spreadsheet to see what the filter is doing

8
  1. read two excel store in datatable -
    dt1 - datatable of 1st excel file
    dt2 - datatable of 2nd excel file
  2. pass the column name to from both sheets, pass all necessary parameter to this workflow

Component for Two Datatable Column Match and Get The Matched Records.xaml (6.3 KB)

  1. this will retrun you the match records between two excel

let me know if you need any help.

9

Thanks. Just logged back on so gonna give this all a try.

10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.