Dynamic VLookup between .xls file


I have a work flow that contains a step that should extract specific data from Alpha.xlsx and update specif cells in Beta.xlsx. Both files contain the same unique ID in column A, to match with. I have created a data table of file Alpha. The output to be updated into file Beta.xlsx is located in column B in file Alpha.xlsx. The process should take the unique value of each row in column B in Alpha.xlsx and update column B in file Beta.xlsx based on the unique ID in column A. Since the list of unique IDs is long and will be updated frequently in both files I cannot work with specific cell references or named cells.


Source: Alpha.xlsx:

Unique ID Value
123456 1000
45872 2000

Values to be updated in Beta.xlsx:

Unique ID
123456 1000
45872 2000
4534 n/a

In this case Apha xlsx. did not contain the Unique ID: 4534 so this row was not updated as the rest in Beta.xlsx.

Thanks in advance for any input!

loop through your beta datatable.
in body section create array of datarow variable and assign value like this datarow=alpha.select("Unique = '"+row.Item(0).ToString() + "'")

if the datarow.length >0 you can get the data from datarow variable datarow(0).ItemArray(1)
you can write it in excel

in the else part set value as “N/A” and write it to excel

ddrdushy1’s solution sounds good. Basically use a .Select function to make an array of the rows to update, then loop through those rows and use a simple Assign activity to set the item for each row.

However, there has to be a faster way to simply replace the matched rows into Beta.xlsx

My thinking is you could run a query function to make a datatable of the matched rows and unmatched rows, then merge the unmatched with the Alpha.xlsx, therefore instantly updating Beta.xlsx

I have this one function saved that will create an unmatched datatable:
dtUnmatched = (From x In dt2.AsEnumerable() where Not (From row2 In dt2.AsEnumerable() Join row1 In dt1.AsEnumerable() On row2(col1).ToString() Equals row1(col1).ToString() select row2).Contains(x) select x).CopyToDataTable()

So dt2 would be Alpha, I think, with dt1 being Beta, and col1 is the column you are looking to match. I personally haven’t used this that much, but it’s an idea anyway.