Lookup using two columns

[MANISHA_V_ARORA (Miss Arora)]

In continuation to below post:

Update column values of datatable based on the lookup result from another excel sheet

[columndata] For a column, we can have combination of different values as in here, it can be BLV, ABC,XYZ etc. We need to lookup into another excel file what ABC mean for example, get that result and paste here in c_prop_cd wherever we find ABC in this column in one go… How this can be done considering we have huge amount of data…

Here this is input sheet

Here this is lookup sheet

Here based on BLV & 532 fetch its desc (from lookup sheet) and update in input sheet under c_pos_rev_center_cd column

Can we perform a lookup using two columns as here, based on first & second column we need to retrieve 3rd column value. How do we achieve using this VB code shared earlier.

Thank You!

@prasath17

How do we modify this to have two columns based lookup:

dt1.AsEnumerable().ToList().ForEach(Sub(row) row(“c_prop_cd”)= If(dt2.AsEnumerable().Where(Function(r) r(“c_prop_cd”).ToString.Equals(row(“c_prop_cd”).ToString)).Count<>0, dt2.AsEnumerable().Where(Function(r) r(“c_prop_cd”).ToString.Equals(row(“c_prop_cd”).ToString))(0)(“c_prop_desc”).ToString,row(“c_prop_cd”).ToString))

[prasath17 (Guru)]

@MANISHA_V_ARORA - i guess @NIVED_NAMBIAR shared that query on that post…

[MANISHA_V_ARORA (Miss Arora)]

Yes @NIVED_NAMBIAR helped us earlier.

Your help is needed here and @Divyanshu_Divyanshu too

[NIVED_NAMBIAR (NIVED N)]

Hi @MANISHA_V_ARORA
just a small condition needed to be added here

dt1.AsEnumerable().ToList().ForEach(Sub(row) row(“c_prop_cd”)= If(dt2.AsEnumerable().Where(Function(r) r(“c_prop_cd”).ToString.Equals(row(“c_prop_cd”).ToString) And r(“c_pos_rev_center_cd”).ToString.Equals(row(“c_pos_rev_center_cd”).ToString)).Count<>0, dt2.AsEnumerable().Where(Function(r) r(“c_prop_cd”).ToString.Equals(row(“c_prop_cd”).ToString))(0)(“c_prop_desc”).ToString,row(“c_prop_cd”).ToString))

[MANISHA_V_ARORA (Miss Arora)]

Thank you for your quick help Nived.

This works if the columns names in input & lookup sheet are same.

What if the column names are different like below, will this not work?
I get Invoke code: Exception has been thrown by the target of an invocation.

This is input sheet

Lookup sheet

Here c_comp_prop_cd of input sheet is equal to c_rev_center_cd of lookup sheet
c_prop_cd of input sheet is equal to c_prop_cd of lookup sheet

c_lms_rev_center_desc need to be fetch & replace c_comp_prop_cd with desc value

Tried with below:
dt1.AsEnumerable().ToList().ForEach(Sub(row) row(“c_comp_prop_cd”)= If(dt2.AsEnumerable().Where(Function(r) r(“c_comp_prop_cd”).ToString.Equals(row(“c_rev_center_cd”).ToString) And r(“c_prop_cd”).ToString.Equals(row(“c_prop_cd”).ToString)).Count<>0, dt2.AsEnumerable().Where(Function(r) r(“c_rev_center_cd”).ToString.Equals(row(“c_comp_prop_cd”).ToString))(0)(“c_lms_rev_center_desc”).ToString,row(“c_comp_prop_cd”).ToString))

[NIVED_NAMBIAR (NIVED N)]

But since i had added the if condition, so seems it would too

[MANISHA_V_ARORA (Miss Arora)]

Hi Nived,

Could you review the code once again of my previous reply, also check whether it is possible or not

[MANISHA_V_ARORA (Miss Arora)]

@Yoichi Could you please help here?

Below code works if the columns names in input & lookup sheet are same.
dt1.AsEnumerable().ToList().ForEach(Sub(row) row(“c_prop_cd”)= If(dt2.AsEnumerable().Where(Function(r) r(“c_prop_cd”).ToString.Equals(row(“c_prop_cd”).ToString) And r(“c_pos_rev_center_cd”).ToString.Equals(row(“c_pos_rev_center_cd”).ToString)).Count<>0, dt2.AsEnumerable().Where(Function(r) r(“c_prop_cd”).ToString.Equals(row(“c_prop_cd”).ToString))(0)(“c_prop_desc”).ToString,row(“c_prop_cd”).ToString))

What if the column names are different like below, will this not work?

This is input sheet

Lookup sheet

Here c_comp_prop_cd of input sheet is equal to c_rev_center_cd of lookup sheet
c_prop_cd of input sheet is equal to c_prop_cd of lookup sheet

c_lms_rev_center_desc need to be fetch & replace c_comp_prop_cd with desc value

Tried with below:
dt1.AsEnumerable().ToList().ForEach(Sub(row) row(“c_comp_prop_cd”)= If(dt2.AsEnumerable().Where(Function(r) r(“c_comp_prop_cd”).ToString.Equals(row(“c_rev_center_cd”).ToString) And r(“c_prop_cd”).ToString.Equals(row(“c_prop_cd”).ToString)).Count<>0, dt2.AsEnumerable().Where(Function(r) r(“c_rev_center_cd”).ToString.Equals(row(“c_comp_prop_cd”).ToString))(0)(“c_lms_rev_center_desc”).ToString,row(“c_comp_prop_cd”).ToString))

I get Invoke code: Exception has been thrown by the target of an invocation for above code

[MANISHA_V_ARORA (Miss Arora)]

@prasath17 @NIVED_NAMBIAR @Divyanshu_Divyanshu

[NIVED_NAMBIAR (NIVED N)]

Hi @MANISHA_V_ARORA
can u share the sample file please?

[MANISHA_V_ARORA (Miss Arora)]

Book1.xlsx (10.2 KB)
Attached file

[NIVED_NAMBIAR (NIVED N)]

Hi @MANISHA_V_ARORA

after a long testing

got this code

dt1.AsEnumerable().ToList().ForEach(Sub(row) row(“c_comp_prop_cd”)= If(dt2.AsEnumerable().Where(Function(r) r(“c_rev_center_cd”).ToString.Trim.Equals(row(“c_comp_prop_cd”).ToString.Trim) And r(“c_prop_cd”).ToString.Trim.Equals(row(“c_prop_cd”).ToString.Trim)).Count<>0,dt2.AsEnumerable().Where(Function(r) r(“c_rev_center_cd”).ToString.Trim.Equals(row(“c_comp_prop_cd”).ToString.Trim) And r(“c_prop_cd”).ToString.Trim.Equals(row(“c_prop_cd”).ToString.Trim))(0)(“c_lms_rev_center_desc”).ToString, row(“c_comp_prop_cd”).ToString))

Result got

Regards,
Nived N
Happy Automation

[MANISHA_V_ARORA (Miss Arora)]

It worked Nived.

Thank you so much

[Yoichi]

Hi,

FYI, another solution:

dtResult = dtInput.AsEnumerable.Select(Function(r) dtInput.Clone.LoadDataRow({r("id"),r("c_prop_cd"), dtLookup.AsEnumerable.Where(Function(x) r("c_prop_cd").ToString.Trim=x("c_prop_cd").ToString.Trim AndAlso r("c_comp_prop_cd").ToString.Trim=x("c_rev_center_cd").ToString.Trim).Single().Item("c_lms_rev_center_desc") ,r("d_date")},False)).CopyToDataTable

Sample20210611-1.zip (11.1 KB)

Regards,