Dynamic VLookup between .xls file

Help
1

Hi,

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.

Example:

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!

2

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

3

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.

Thanks.

2 Likes