Find match for rows in excel

Hi team,

I have below excel data -

I have 2 values -
“currency” and “account”

First I have to check if [first 2 characters of " currency" ] is present in Remit_ID rows.If it has a unique row match , it should return the corresponding “Supplier_ID” for that Remit_ID. If its present in multiple rows or if no rows match, it should check for the [last 4 digits of " account"] in Remit_ID rows.If there is a unique match it should return the corresponding “Supplier_ID” for that Remit_ID.

For example -

  1. currency - USD
    account - 23421134
    it should return - SUPPLIER_CONNECTION-3-3628

  2. currency - BRA
    account - 23423456
    it should return - SUPPLIER_CONNECTION-3-2345

  3. currency - BRA
    account - 234234561
    it should return - “No record found”

  4. currency - USD
    account - 7891011
    it should return - SUPPLIER_CONNECTION-3-7654

  5. For multiple matches even after currency and account validation , it should return any of the

Kindly help here!


Duplicate : Excel help please - filter and fetch values

Hi ,

I have flagged the original post and asked the moderator to delete it since the requirement is not precise and I havent achieved the desired solution on that.The requirements for this is different from the original one.


Hi @gokul1904

Have a look on the XAML file

ExcelProcessing.xaml (10.6 KB)


1 Like

@gokul1904 I have got a chance to work on your process, but I was little bit confuse. Now you gave more examples, now I will be able to do something

1 Like

Hi ,

Thanks a lot for the prompt response. but this list is a dynamic one where switchcase cannot be used

“currency” and “account” are inputs to this excel datatable
it should perform a match with first 2 characters of currency and last 4 digits of account number in the order as mentioned in the main post.


@gokul1904 so input is Coming outside and just we need to match with excel file.

1 Like

Yes Input is coming from outside - currency and account are input variables here.

We have to match the input variables with each row of “Remit-ID” column and derive the corresponding “Supplier_ID” for that.


@gokul1904 ok got it.

1 Like

have a check at:

(From d In dtData.AsEnumerable
Let sc = strCUR.Trim.Substring(0,2).toUpper
Let sa = strACC.Trim.Substring(strACC.Trim.Length - 4).toUpper
Let rm = d("Remit_ID").toString.Trim.toUpper
Where rm.StartsWith(sc) Or rm.EndsWith(sa)
Let cm2 = Convert.ToInt32(rm.StartsWith(sc)).toString
Let cm1 = Convert.ToInt32( rm.EndsWith(sa)).toString
Let msk = CInt(cm1 & cm2)
Where msk > 0
Order By msk Descending
Select v = d("Supplier_ID").toString).DefaultIfEmpty("No record found").First()
1 Like

Thank you @ppr for your response.However it is not giving desired output for 1st and 4th scenario -
I will elaborate the first example → [first 2 characters of “Currency” i.e USD] has multiple matches in rows 1,2,4.So it should validate with [last 4 digits of " account"i.e 1134].It has only one match in row 4.Hence it should give SUPPLIER_CONNECTION-3-3628 as output.

If [first 2 characters of “Currency” i.e USD] had a unique match , it should have given respective Supplier_ID and it wont validate for [last 4 digits of " account"i.e 1134]

Only when currency validation results in multiple matches it should proceed with the account validation to filter the multiple matches.


can you check updated statement please?

1 Like

Thanks alot @ppr again!
But for values -
strCUR = EUR
strACC = 123456

it should return → SUPPLIER_CONNECTION-2-3627
whereas it is returning → SUPPLIER_CONNECTION-3-2345

Since strCUR i.e EUR is a unique match it should pick the corresponding Supplier_ID and not validate for strACC

Thank you once again for all the efforts.


Let us have a look at the rule definition

strCUR = EUR, strACC = 123456

EUR → has one Match so from your rule set it returns the 3627 Remit
3456 → has a match to the 2345 Remit

Currently the approach is assigning a rating mask base on cur / acc check

ACCCheck CurrCheck Mask Int
Y N 10 10
Y Y 11 11
N N 00 0
N Y 01 1

Where filtered out the cases of 0

We would suggest to advance with it, when you want take this approach and

  • swap the most important criteria
  • return all Remits with its rating mask and postprocess it
1 Like

Thank you for the explanation @ppr

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